Access Query to Return the Newest Record for Multiple Records

[cwager990]

Okay guys,

I have two tables one for Customers and One For the Items they have purchased.

I have a query that currently returns the records for customers with every item they have purchased so I recieve multiple records per customer in the query as it lists every item they have purchased.

I now want to know how to modify this query to only display the last item they purchases.

If anyone can help that would be greeat.

Thank You.

[CJ_London]

not enough information to provide a suggestion. But assuming you are talking about one customer and you are defining 'last' as being the latest date, and they purchase no more than one item in any one day, sort your query in descending order by date and select TOP 1

[cwager990]

not enough information to provide a suggestion. But assuming you are talking about one customer and you are defining 'last' as being the latest date, and they purchase no more than one item in any one day, sort your query in descending order by date and select TOP 1

The existing query I have is:

SELECT Customers.CustomerID, Caravan.[Stock No], Caravan.Year, Caravan.Model, Caravan.[VIN Number], Customers.[No Mail], Customers.[Svc Plan], Customers.Expires, Customers.[Mr/Mrs], Customers.Surname, Customers.Address1, Customers.Address2, Customers.Town, Customers.PostCode, Customers.HomePhone, Customers.WorkPhone, Customers.MobilePhone1, Customers.MobilePhone2, Caravan.[Date of Purchase], Caravan.[Original Date of Purchase], Customers.[Electronic Mail No]
FROM Caravan INNER JOIN Customers ON Caravan.[Customer ID] = Customers.CustomerID;

This query returns multiple results when a customer has purchased more than on caravan what I want to do us only return there most recent caravan purchased using Original Date of Purchase,

[pbaldy]

Try

SELECT TOP 1 Customers...

and add a sort on the date field.

[cwager990]

I changed the query to this:

SELECT TOP 1 Customers.CustomerID, Caravan.[Stock No], Caravan.Year, Caravan.Model, Caravan.[VIN Number], Customers.[No Mail], Customers.[Svc Plan], Customers.Expires, Customers.[Mr/Mrs], Customers.Surname, Customers.Address1, Customers.Address2, Customers.Town, Customers.PostCode, Customers.HomePhone, Customers.WorkPhone, Customers.MobilePhone1, Customers.MobilePhone2, Caravan.[Date of Purchase], Caravan.[Original Date of Purchase], Customers.[Email No]
FROM Caravan INNER JOIN Customers ON Caravan.[Customer ID] = Customers.CustomerID
ORDER BY Caravan.[Date of Purchase] DESC;

It returned the 3 records with the newest date, what I want to do is return the newest record from the caravan table for each customerid using the newest date of purchase for each user to achieve this.

[pbaldy]

How about:

http://allenbrowne.com/subquery-01.html#TopN

[cwager990]

Okay so it looks like that would work the only issue being I am new to Access and have no idea how to go about modifying what I have to do that etc.

[cwager990]

Okay so it looks like that would work the only issue being I am new to Access and have no idea how to go about modifying what I have to do that etc.

This is what I think I need to do:

SELECT Customers.CustomerID, Caravan.[Stock No], Caravan.Year, Caravan.Model, Caravan.[VIN Number], Customers.[No Mail], Customers.[Svc Plan], Customers.Expires, Customers.[Mr/Mrs], Customers.Surname, Customers.Address1, Customers.Address2, Customers.Town, Customers.PostCode, Customers.HomePhone, Customers.WorkPhone, Customers.MobilePhone1, Customers.MobilePhone2, Caravan.[Date of Purchase], Caravan.[Original Date of Purchase], Customers.[Email No]
FROM Caravan INNER JOIN Customers ON Caravan.[Customer ID] = Customers.CustomerID
WHERE Caravan.[Customer ID] IN
(SELECT TOP 1 [Customer ID]
FROM Caravan as Dupe
WHERE Dupe.[Customer ID] = Caravan.[Customer ID]
ORDER BY Dupe.[Date of Purchase] DESC, Dupe.[Customer ID] DESC)
ORDER BY Caravan.[Date of Purchase] DESC;

Does this look right to you ?

[cwager990]

That is not working quite right its sorting the dates etc but its still showing me there older caravans too, what did I get wrong ?

[JSR]

My personal preference is to do this type of thing differently. Make a query on the two tables joined by customer ID, turn on totals, Group by customer ID, Max([Date Of Purchase]) as LastPurchaseDate. Save this query.

Now, to get all the data associated with just the last purchase for each customer, create another query based on the two tables and the first query. Join the two tables by customer ID, and join the purchase table to the query by customer ID and Date Of Purchase to the query's LastPurchaseDate. No totals, just output every field you want to see.

[cwager990]

My personal preference is to do this type of thing differently. Make a query on the two tables joined by customer ID, turn on totals, Group by customer ID, Max([Date Of Purchase]) as LastPurchaseDate. Save this query.

Now, to get all the data associated with just the last purchase for each customer, create another query based on the two tables and the first query. Join the two tables by customer ID, and join the purchase table to the query by customer ID and Date Of Purchase to the query's LastPurchaseDate. No totals, just output every field you want to see.

I am not sure exactly how to do that I am a begginer with Access, and am struggling to get my head around these things.

[JSR]

Do the first one. Create a query (design view), select the two tables, join by customer ID, turn on totals, group by customer ID, click on Date Of Purchase, select "max" and save. Run that query and you will see that you get just one row for each customer ID and the latest purchase date.

[cwager990]

Do the first one. Create a query (design view), select the two tables, join by customer ID, turn on totals, group by customer ID, click on Date Of Purchase, select "max" and save. Run that query and you will see that you get just one row for each customer ID and the latest purchase date.

Yes I have that, outputs two colums on is the CustomerID the other is MaxOfDate.

What do I do Next ?

[cwager990]

This works, as I expected but results in a recordset that is not updateable, my previous query that return all the vans per customerid is updateable is there a way to do this and maintain updateability.

[cwager990]

Sorry all, Apparently I have caused offense by cross-posting this post on another fourum, I did not realise that was an issue. I had thought it would open me up to more ideas from different people, I had thought there may be people here that are not on the other forum and vice a versa, anyway I have delete the post on the other forum as this one has been more helpful. @JSR, your solution has generated the exact output I want, but results in a record set that is non updateable is there anyway to do this witth an updateable record set ?.

Once again sorry for any upset or offense caused by the cross-post.