How to pull lowest value?

[TommyRex]

Hello, All,

Thank you for your help.

I'm working with Access 2007 and need to learn how to pull the lowest value for a particular ID.

For Example:

Patient Procedure Procedure Rank
John Smith 12450 3
John Smith 42258 8


John Smith 30114 2
Mike Jones 44454 5
Mike Jones 13534 9


How do I I set up a query so that the only records that pull are John Smith's with Procedure Rank 2 (his lowest) and Mike Jones' with Procedure Rank 5 (his lowest)? I am not interest in the other...only the lowest for each Patient.

Any ideas,

Thank you, Tom

[pbaldy]

This technique, with Min instead of Max:

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

[TommyRex]

I not sure I'm following this:

First, I constructed a make table query where I joined my patient records table and code rank table pulling Patient ID and Code Rank (both put in the new table).

I've now taken this table and joined it with my original patient ID table, but rather than just getting the primary code (lowest rank) for John Smith, etc. I'm again getting all the codes for both patients.

What am I doing wrong?

Thanks,
Tom

[pbaldy]

Not sure; you're using some other method. I wouldn't use a make table query, but are you joining on both fields?

[TommyRex]

Yeah, I'm not following the directions from your link. I thought it asked me to make a table in step one.

Do you know if any other way to explain it?

Thank you, Tom

[pbaldy]

My method? The first step is just a query, which the second query uses like a table.