In a query, How to get the last row with it's own sum?

[KHerm]

I've been asked to "fix" someone else's query which I thought was going to be simple, and may yet be, but I'm stuck.

I don't have the code in front of me to work at this moment, but I know the problem.

The query selects columns grouped by File_number, dates, and sums the money column by date. The output could look like this:

AB101 01/01/2001 $1,005.51
AB101 02/21/2001 $3,145.99
AB101 03/05/2001 $5,492.38

But what the user wants is for the query to select the LAST row only. In this case, AB101 03/05/2001 $5,492.38

In design, I chose "Last" (instead of "Sum" or "Group by&quot in the date column, and I got AB101 03/05/2001 $9,643.88

I tried using "Last" in the money column, too, but that didn't work either.

Is this another time for a function? Or am I missing something in design mode?

Thanks for any help,

Ken

 

[gwinn7]

Well, if the result you want is based on a maximum value, as you have displayed in your example, you can use the MAX function to obtain the record with the maximum value in a column and nest it with a master statement. Like this...

"SELECT Myfield1, MyField2 FROM MyTable
WHERE (((MyField3) IN (SELECT Max(MyField3) AS MaxVal
FROM MyTable)));"

Does this help?

Gary
gwinn7

 

[KHerm]

Thanks for your response, and sorry for not getting back to this group faster to follow up.

It turned out that the last date could have the highest dollar amount, but not necessarily, so I tried something else which worked.

I made a query which pulled everything except the money field and using "Max" for the date field.

Then I used a new query that left joined with what I called the new Max-Query. It got the results I wanted. Somehow I thought I should have been able to use one query, but that's OK. It's now "not broke" so I won't "fix it"!

Thanks again,

Ken