Very Computer

Does anybody know how to rewrite an Access SQL statement that can perform
the same function as follows?

I am having problems with the FIRST expression since SQL doesn't have an
equilavent....

SELECT OrderDetail.DocumentNumber, First(CustomerMaster.Name) AS Customer,
First(CustomerMaster.City) AS City, First(CustomerMaster.State) AS State,
Sum(DeliveryDetail.Cases) AS Cases, Sum(DeliveryDetail.Pounds) AS Weight
FROM CustomerMaster RIGHT JOIN (OrderDetail LEFT JOIN DeliveryDetail ON
(OrderDetail.LineItem = DeliveryDetail.LineItem) AND
(OrderDetail.DocumentNumber = DeliveryDetail.DocumentNumber)) ON
CustomerMaster.CustomerNumber = OrderDetail.SoldToCustomer
GROUP BY OrderDetail.DocumentNumber;

Not quite sure because my Access is a little rusty, but you may be able to get
by with using something like TOP 1 or max(...).  In fact, it looks as if
Max(column) will be your best bet unless you just need whatever SQL decides to
grab first.

HTH,

-Pete Schott

You could use MIN() or MAX(), but there is better way.
You should rearrange the query, so that you no longer need to apply
aggregate functions ( like First() ) to customer data:

SELECT OrderDetailSums.DocumentNumber, Cases, Weight, Name, City, State
FROM CustomerMaster RIGHT JOIN
    (
    SELECT OrderDetail.DocumentNumber, OrderDetail.SoldToCustomer,
    Sum(DeliveryDetail.Cases) AS Cases,
    Sum(DeliveryDetail.Pounds) AS Weight
    FROM OrderDetail LEFT JOIN DeliveryDetail
    ON (OrderDetail.LineItem = DeliveryDetail.LineItem)
    AND (OrderDetail.DocumentNumber = DeliveryDetail.DocumentNumber)
    GROUP BY OrderDetail.DocumentNumber, OrderDetail.SoldToCustomer
    ) AS OrderDetailSums
ON CustomerMaster.CustomerNumber = OrderDetailSums.SoldToCustomer

The inner query (derived table) could be expressed as view. You could do
something similar in Access, where the inner query can be saved as query.

HTH
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------