Selecting Most Current 'Revision'

[Nola_Gal]

I am using MS Access 2003 Prof Edition
My question has to do with a query...I have a table which includes patient episode data...fields include
pt number
episode number
diagnosis


revision number
etc...
I would like to pull selected patient information, but only from the most current revision. For most patients, this would be revision # 1. For others that have gone through several revisions, it might be revision number 3 or 4.
How can I build this selection into my query. (Also, I am only comfortable using the visual design mode right now...)
Thanks so much for any help or direction you can give!

[ConneXionLost]

Hi Nola_Gal,

Is the table attribute "revision number" formatted as Number or Text?

Cheers,

[Nola_Gal]

As a number.

Thanks!

[pbaldy]

Does this help?

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

[Nola_Gal]

No, sorry but it's a little over my head...BUT I did find out that there is a row status field which distinguishes between current and superceded entries so I was able to work off that!

Thanks for trying! I need to take some lessons...

[AccessTheWayAhead]

Hi Nola_Gay

You need to create a group by query then use that as a sub query joining it to the original table.

1. create a new query and add your table
2. drop only the pt number and the revision number
3. make the query a group by
4. change the revision number from group by to Max
5. save and name as latest revision
6. open your current query and add the sub query
7. join the pt number in the table to the sub query
8. join the the revision number in the table to the maxofrevision number in the sub query


That should do it...

David