Access Query to Return the Newest Record for Multiple Records

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

This worked thank you, is there a way to do this so that the record set is updateable ?.

Sent from my SM-N950F using Tapatalk

[Micron]

Cross posting isn't bad per se. Doing it and not declaring it in both forums with links to each other is the problem. Here's another tip: if I see a thread is cross posted, I probably won't get involved because it can be too much trying to follow conversations and sort suggestions between forums. Plus I don't belong to any other Access forum anyway. I'm revealing my own bias here and maybe I'm alone in this thinking. The exceptions would be if a) immediately I think I know the answer, or b) I'm intrigued by the issue at hand.

Anyway, here's a good link for the many reasons why a query is not updatable http://allenbrowne.com/ser-61.html You've got at least 2 of them covered. I think it's time we saw a good example of table data, or zip a db copy with fake names and leave out phone numbers and such. Based on some of what you've posted, normalization might be an issue here, such that it's causing you to search for complicated solutions where the data is (possibly) the problem.

[cwager990]

Cross posting isn't bad per se. Doing it and not declaring it in both forums with links to each other is the problem. Here's another tip: if I see a thread is cross posted, I probably won't get involved because it can be too much trying to follow conversations and sort suggestions between forums. Plus I don't belong to any other Access forum anyway. I'm revealing my own bias here and maybe I'm alone in this thinking. The exceptions would be if a) immediately I think I know the answer, or b) I'm intrigued by the issue at hand.

Anyway, here's a good link for the many reasons why a query is not updatable http://allenbrowne.com/ser-61.html You've got at least 2 of them covered. I think it's time we saw a good example of table data, or zip a db copy with fake names and leave out phone numbers and such. Based on some of what you've posted, normalization might be an issue here, such that it's causing you to search for complicated solutions where the data is (possibly) the problem.

I will redact the table and upload the file.

Sent from my SM-N950F using Tapatalk

[CJ_London]

maybe I'm alone in this thinking

No, you are not. I don't expect newbies to understand - the argument 'I want as many possible answers' is perfectly valid but from a responder perspective there is 'I don't want to spend time working on providing a solution that has already been provided'. Time is given voluntarily and freely and most don't like to see time wasted. Couple this with the number of regular responders with an in depth knowledge of access/vba is relatively small - not counted but would suggest perhaps less than 100 worldwide - although I am sure there are many thousands of users with in depth knowledge but they don't use the forums (at least to help others). So it comes down to common courtesy which is what the link provided explains. The generally standard notification of 'cross posted here' with a link is to advise responders, potential or otherwise, and not an admonishment to the OP.

[cwager990]

No, you are not. I don't expect newbies to understand - the argument 'I want as many possible answers' is perfectly valid but from a responder perspective there is 'I don't want to spend time working on providing a solution that has already been provided'. Time is given voluntarily and freely and most don't like to see time wasted. Couple this with the number of regular responders with an in depth knowledge of access/vba is relatively small - not counted but would suggest perhaps less than 100 worldwide - although I am sure there are many thousands of users with in depth knowledge but they don't use the forums (at least to help others). So it comes down to common courtesy which is what the link provided explains. The generally standard notification of 'cross posted here' with a link is to advise responders, potential or otherwise, and not an admonishment to the OP.

I am new here, I don't usually use forums at all, I don't usually use access or databases, I am a server and desktop support analyst by trade.

Sent from my SM-N950F using Tapatalk

[CJ_London]

I am new here, I don't usually use forums at all, I don't usually use access or databases, I am a server and desktop support analyst by trade.

no need to apologise again, apology has been accepted. Understand and apply the etiquette and move on.

[cwager990]

no need to apologise again, apology has been accepted. Understand and apply the etiquette and move on.

Fair point, the suggestion made by JSR gets me the view I want, is there anyway to generate the same result but have the query editable ?

I will need to create some examples for you if you want files as I can not use the live customer data.

I will set up an example Database, now while I wait for any ideas.

Thanks All.

Sent from my SM-N950F using Tapatalk

[JSR]

Create a table "temp last purchase dates" with Customer ID and Last Purchase Date. Primary key is Customer ID and Last Purchase Date.

Create three queries:

1) delete all rows from temp last purchase dates
2) append query based on the purchase table, append to the temp table, group by Customer ID, Max([Date of Purchase]) as Last Purchase Date
3) select query based on three tables, customers and purchases joined by Customer ID, and the temp table joined by Customer ID and Date of Purchase = Last Purchase Date. Output all fields you want to see.

This should be editable (because primary key of temp table is customer ID and last purchase date) but slightly less convenient, because you need to run three queries. You can create a macro to run all three. I don't know of a way to have a select query do this type of thing and be editable.

[JSR]

dup; made corrections above

[cwager990]

Create a table "temp last purchase dates" with Customer ID and Last Purchase Date. Primary key is Customer ID and Last Purchase Date.

Create three queries:

1) delete all rows from temp last purchase dates
2) append query based on the purchase table, append to the temp table, group by Customer ID, Max([Date of Purchase]) as Last Purchase Date
3) select query based on three tables, customers and purchases joined by Customer ID, and the temp table joined by Customer ID and Date of Purchase = Last Purchase Date. Output all fields you want to see.

This should be editable (because primary key of temp table is customer ID and last purchase date) but slightly less convenient, because you need to run three queries. You can create a macro to run all three. I don't know of a way to have a select query do this type of thing and be editable.

I'll give this a go thank you

Sent from my SM-N950F using Tapatalk