Query Based on Last Date

[MooseMan]

I have a table called tblDefaults which contains monthly default reports for 50 different administrators. I have been trying to build a query based on three fields in that table. AdministratorID, DefaultAmount, and ReportingMonth.

My problem is that the query which will show the default amount by administrator has to be based on the most recent default report for that administrator. ReportingMonth is a date field indicating the reporting month, but when I use Last or Max in the query design I get multiple records for a number of AdministratorID's.

If the query were to work properly I should get one record for each AdministratorID and that record being the most recent entered into the Table.

Any help would be greatly appreciated.

 

[gol4]

I think this may be close to what you want

select AdministratorID, DefaultAmount, reportingmonth
from tbl where tbl.reportingmonth =(select Max(reportingmonth from tbl as a where a.AdministratorID = tbl.AdministratorID)as maxmonth

it uses a subquery and aliases the tbl

 

[Guest_imported]

How do you calculate the total number of orders by one customer and have the specific dates in which they purchased them on one database?