Delete Query

  • Thread starter Thread starter nir020
  • Start date Start date
N

nir020

I have created a table in access which is has three columns, one is Name,
which refer to the name of person making the order (duplcates allowed), one
is order date (duplicates allowed) and the final column contains the an order
ID (which is the primary key). It show orders made by people and the date
they were made on.

What I want to do is create a query that produces a table that shows the
latest order date for each person and the order ID. Please note that some
people have made more than one order on a single day, and I would like to
show all these orders.
 
I'm not exactly sure what relationship the message subject has to the
question as you pose it, but a query that will SELECT the records you have
identified would look like:

SELECT * FROM yourTABLE
WHERE DateValue([OrderDate] =
DateValue(DMAX("OrderDate", "YourTable", "[Name] = '" & [Name] & "'"

Note: This one could be created in the query design grid
OR

SELECT T.*
FROM yourTable T
INNER JOIN
(SELECT [Name], DateValue(DMAX("OrderDate", "yourTable")) as MaxDate
FROM youTable
GROUP BY [Name]) as T2
ON T.[Name] = T2.[Name]
AND DateValue(T.[OrderDate]) = T2.MaxDate

Note: This one will probably be faster, but cannot be built or edited in the
query design grid. You will have to do any editing of this in the SQL
statement.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top