Select newest Record

[jbailey4545]

I need some help selecting the newest patient record in a table. The problem is that each patient could have multiple entries in the table. The way to identify the new record is by the highest program_seq number. Example below.





I want the third row to be the only one that shows in the query.

[pbaldy]

Did you forget about this thread?

https://www.accessforums.net/queries...elp-25854.html

[jbailey4545]

I tried that. Still ended up with duplicate patient records.

[jbailey4545]

You might know if this is possible. I have did code a while back in a program that would look through the table and kick out duplicates. For example if you sort the query by client_id then program_seq. It would start at program_seq : 1 then look at the next record. If the client_id was = to the next record client_id it would then compare the 2 program_seq numbers and take the larger of the two. Once the next record client_id changed it kept the last record for the last client with the highest program_seq number. Anything like that possible in Access?

[pbaldy]

I tried that. Still ended up with duplicate patient records.

With my method? Post the SQL you used, as you shouldn't get duplicates with mine.

[jbailey4545]

I just re created the new query and you are right. I must have missed something when i did it before. Thank you for your help.

[pbaldy]

Happy to help.