MAX question using 2 fields to select records

[molly]

I need a way to choose records based on the last monthID and the associated FormulaID. Maybe like a 2 field MAX problem ??

Attached is a small file to try. I need to records to show up in the query 8510Qry-P05 Total Fixed Cost

The 2 records would be for:
692030 for month 200908
692744 for month 200909

The rest do not need to be seen. I even tried making an extra query level but it did not return my 2 records.

What do I need to do? I am looking for a formula's last month number.

Thanks, Molly

 

[molly]

As a check, I should get the following 2 records back.

formulaID monthID Total Fixed Cost
692030 200908 935.14
692744 200909 2200.40

If I put MAX on a TOTAL, the dollar fields are incorrectly summed up. I just want the 2 records above to show up.

I tried concatenating the monthID and FormulaID but that did not seem to work either.

Molly

 

[molly]

Never mind. Figured it out after reading deep in the Tek Tips. They gave me an idea.

I made a query of just the formulaID and the MonthID. I put on totals, and put MAX on the monthID total row.

Then i went to the main query and added this new query.
I put a newer version 2 file if you need to follow me better.

thanks for the tips
Molly