Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Top 2 Values or Last 2 Order dates

6 views
Skip to first unread message

Gail

unread,
Jun 9, 2003, 5:31:29 PM6/9/03
to
Please help...My Query gives me the last 2 Order dates for
1 company and for 1 product they order only. I do not know
how to create a query that does it all at once for each
company and product they order within one query.

Here's my Query:

SELECT TOP 2 [Companies].PRODUCT, [Companies].CUSTOMER,
[Companies].DATE,[Companies].[ID Number]
FROM [Companies]
WHERE ((([Companies].PRODUCT)="A") AND
(([Companies].CUSTOMER)="Company N.B."))
ORDER BY [Companies].DATE DESC

QUESTION:
How do I create one query that gets the last 2 Order dates
for each separate company and product that the particular
company orders??

Example of Part of Table:

Companies Product Order Date
Company NB A April 1, 2003
Company J A April 15, 2003
Company NB A May 20, 2003
Company NB B May 21, 2003
Company J A May 13, 2003
Company J C June 3, 2003
Company J A June 9, 2003
Company NB A June 9, 2003

Example: I would like the query to give me the following
Data:
Companies Product Order Date
Company NB A June 9, 2003
Company NB A May 20, 2003
Company NB B May 21, 2003
Company J A June 9, 2003
Company J A May 13, 2003
Company J C June 3, 2003


John Spencer (MVP)

unread,
Jun 9, 2003, 7:25:00 PM6/9/03
to
SELECT [Companies].PRODUCT, [Companies].CUSTOMER,
[Companies].[DATE],[Companies].[ID Number]
FROM [Companies]
WHERE Companies.[Date] IN
(SELECT TOP 2 [A].[DATE]
FROM [Companies] As A
WHERE A.Product =[Companies].PRODUCT AND
A.CUSTOMER = [Companies].CUSTOMER
ORDER BY [Companies].DATE DESC)
0 new messages