Trying to retrieve unique records

R

redstamp

Can someone please tell me how to force access to extract unique
records, based on the last record in a particular field.

Basically I have a customer table linked to a customer_order table. I
want to query through access fields within the customer and
customer_order table, but only extract their most recent order to the
results, therefore ending up with a unique list of customers with last
order details only.

I realise you can set unique records and values in a query, but this
still retrieves multiple lines if the data is different in the order
details. I want to force unique customer records, based on the order
date.

Any help is gratefully received.

Thx

Jon
 
G

Guest

Jon:

You do this by means of a correlated subquery like so:

SELECT CustomerName, OrderDate, OrderAmount
FROM Customer INNER JOIN Customer_Order AS CO1
ON CO1.CustomerID = Customer.CustomerID
WHERE OrderDate =
(SELECT MAX(OrderDate)
FROM Customer_Order AS CO2
WHERE CO2.CustomerID = CO1,CustomerID);

The two instances of the Customer_Order table are given aliases CO! and CO2
to distinguish them. This enables the subquery and outer query to be
correlated on the CustomerID column. For each row in the outer query the
subquery returns the latest date for the current customer, so only the rows
in the outer query where the OrderDate matches the date returned by the
subquery are returned. If a customer has two orders on the same date both
would be returned of course if the Order date is just the date (strictly
speaking it would be a date/time value at midnight at the start of the day as
there is no such thing in Access as a date value per se) as there is no way
from the date of knowing which was the latest. To distinguish them the
OrderDate would need to include the time of day of the order.

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top