Eliminate duplicate query record by using most recent date

[timPM]

So I'm an Access beginner, and I'm taking a college class in Database Management. I've completed my assignment however I wanted to explore and take it a bit further.

I have a basic query that returns results for patients in a certain city. However because I want to include doctor information in the query as well, duplicate records are showing up because patients can have more than one visit.

In my query I just want to eliminate the duplicate visit records and show the most recent visit only. So for example I found this question posted on another forum at stackoverflow, however the answers were all using SQL code and I haven't been introduced to it yet so I was confused. I was wondering if there was a way to do this without using SQL code, if not can someone explain what the code would do?

Sorry it's a bit out of alignment ...



|--ID--|-Type-|-Code-|--Date--|
| 1 | A | 11 |11/07/13|
| 2 | A | 11 |11/07/13|
| 2 | B | 12 |10/07/13| <-- don't want this record from ID=2 as
| 3 | A | 11 |11/07/13| there is a more recent date for it
| 4 | A | 10 |11/07/13| than 10/07/2013
| 5 | A | 11 |11/07/13|

[pbaldy]

It sounds like you want something like this:

http://www.baldyweb.com/LastValue.htm

All queries are "SQL code"; the design grid you're probably familiar with is just a graphical user interface that allows you to create it. The first query in my link is a totals query; add the ID and date fields to a query, click on the "E" in the ribbon to make it a totals query, and change the Group By to Max for the date field.

[CJ_London]

what happens if you get two records with the same date? Or can't that happen?

|--ID--|-Type-|-Code-|--Date--|
|   1    |   A    |   11   |11/07/13|
|   1    |   B    |   12   |11/07/13|

[pbaldy]

what happens if you get two records with the same date? Or can't that happen?

Is that directed to me or the OP? If me and it was my db, the date field would contain both date and time to avoid the resulting problems.

[CJ_London]

Sorry - the OP

[timPM]

Yay! Doing those two queries worked from your link pbaldy. I created the first query to find the most recent visit date for each patient in a specific city. Then created a second with my first query as well as all the other tables I wanted fields from, joined all the matching fields together, however I did run into the problem that Ajax brought up! I have a patient who visited twice in the same day, so it shows a duplicate record for him. Is the only way around this to add another piece of information such as time?

[timPM]

Nevermind! Once I made the second query a totals query, the duplicate record merged into one because the records were identical! After getting some insight from you guys and walking myself through it it all is starting to make sense now, thank you guys again for the help!

[pbaldy]

Happy to help and welcome to the site by the way!