DMax Value in a Select Query

[sstump]

I am trying to find the max value of a non-standard field (ex. XAT-00000006719), however it doesn't seem to be cooperating. This is what I've got.

VRF APR
VRF-00000151740 XAT-00000006719
VRF-00000153895 XAT-00000006617
VRF-00000153895 XAT-00000007002
VRF-00000154380 XAT-00000006721
VRF-00000154380 XAT-00000007048
VRF-00000154554 XAT-00000000285

Row APR is what I'm trying to find the max value of, per VRF. So in this example I need to end with this.

VRF APR
VRF-00000151740 XAT-00000006719
VRF-00000153895 XAT-00000007002
VRF-00000154380 XAT-00000007048
VRF-00000154554 XAT-00000000285

Plus containing the additional information along the line. How can I do this in a Select Query. I've tried DMax([APR],Late App TEST, [VRF]) as described in Access help and it's not working.

Any suggestions on how this formula should work would be helpful.

 

[rac2]

Well you need quote marks around the paramenters of the DMax function.

DMax("APR]","Late App TEST", "[VRF]")

And the third parameter is a criterion condition, meaning that the maximum is obtained for a set of lines that meet the criterion. I dont believe that the name of a field is a criterion.

I dont think that DMax() is really what you need.

This will give the maximum APR for each different value of VRF.

SELECT VRF, MAX(APR)
FROM [Late App TEST]
GROUP BY VRF

Note that this uses the MAX() function, not DMAX().

Then there is the problem of the additional information along the line. That is actually a bit tricky to obtain. In general you must JOIN the above query to the table which has the additional information about each VRF. That might be the same table or it might be a different table.

Here is the whole thing assuming that the addtional information is from the same table named [Late App TEST].

SELECT a.VRF, a.color, a.weight, a.part_number, b.TopAPR
FROM [Late App TEST] a
JOIN (
       SELECT VRF, MAX(APR) AS "TopAPR"
       FROM [Late App TEST]
       GROUP BY VRF
) b ON a.VRF = b.VRF

You could save the query that gives the maximum value and refer to it as though it is a table. Suppose you save it and call it LatestApp. Then

SELECT a.VRF, a.color, a.weight, a.part_number, b.TopAPR
FROM [Late App TEST] a
JOIN [LatestApp] b ON a.VRF = b.VRF

By the way, terminology, VRF and APR are columns, the lines are the rows.

 

[sstump]

I actually meant to say "the other columns a long the line". There are approximately 8 columns, however I just need to carry that information over though.

Working on incorporating this into my query now. Will let you know.

Thanks!