Access to SQL Server 7 Query conversion with FIRST Keyword

Access to SQL Server 7 Query conversion with FIRST Keyword

Post by Daniel En » Thu, 20 Jul 2000 04:00:00



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;

 
 
 

Access to SQL Server 7 Query conversion with FIRST Keyword

Post by Peter A. Schot » Thu, 20 Jul 2000 04:00:00


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


> 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;


 
 
 

Access to SQL Server 7 Query conversion with FIRST Keyword

Post by Ivan Arjentinsk » Sun, 30 Jul 2000 04:00:00


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.
-----------------------------------------------


> 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;