MIN() MAX() and subquery

[simon03]

Hi,

I have a table similar to this sample:

ID, Name, Date, P
0, Tom, 01/01/2010, 100
1, John, 01/02/2010, 80
2, Tom, 01/03/2010, 70
3, John, 01/05/2010, 10
4, Tom, 01/06/2010, 90
5, Tom, 01/07/2010, 60
6, John, 01/08/2010, 30

What I would like to do is to run a query which returns all fields and the calculated field CalcValue which is the ratio between field P and the max value of field P for each Name. So something like:

ID, Name, Date, P,CalcValue
0, Tom, 01/01/2010, 100, 1
1, John, 01/02/2010, 20, 0.25
2, Tom, 01/03/2010, 70, 0.7
3, John, 01/05/2010, 80, 1
4, Tom, 01/06/2010, 90, 0.9
5, Tom, 01/07/2010, 60, 0.6
6, John, 01/08/2010, 30, 0.375

I suspect I need a subquery but I can't figure out how to use it (if a subquery is needed!)

Thanks for your help!

 

[vbaInet]

You can use a subquery but it's not needed.

1. Create a query to get the Max of each Name (only have the NameID and the Max value in this query). Save and close the query.
2. Join the query in step 1 to your original query (via NameID) and perform your calculation.

By the way when I say NameID I'm not talking about the ID field you've shown in your example, but the unique field for each person that isn't shown in your example. You should have one.