Find most recent dates in a query 1

[redaccess]

I have a table with the following fields; Date, ID, Salary.

I need to find all the records that have the ten most recent dates, then take those records and join them if any records have the same ID number while totaling each ID's salary.

If you have any ideas, or need more info let me know.

 

[Bry12345]

In the query, use "TOP 10" and a Descending sort to get the 10 lastest dates:

SELECT TOP 10 tblTableName.MyDate, tblTableName.AnotherField
FROM tblTableName
ORDER BY tblTableName.MyDate DESC;

This should give you 10 records with the latest dates.

Write a report based on this query which groups the records by ID. You can then use SUM to add the values Salary fields as they are aggregated by the grouping.

I'm also thinking that "Date" may be bad for a field name . . . (Someone help me here -- reserved word, maybe??) . . might be better to use MyDate or something like that.

 

[redaccess]

Hey, I'd never used that Top 10 thing before! It worked great, thanks! Now I want to sum those results by ID number and that's easy enough using the Sum expression in a query but how can I get the dates to show for each record without them being part of an aggregate function?

 

[redaccess]

Oops-
Actually the Top 10 statement only selects the 10 RECORDS with the most recent date. I need ALL the records with DATES that are the ten most recent.
Anyway, to do this?

 

[WildHare]

First you need to determine what the 10 most recent dates are/were ( which is different than the most recent 10 dates, as you've found).

Select TOP 10 Distinct MyDate Order By MyDate DESC ;

Then use the Min and MAX of that set to set Parameters in your other query...

Gotta run..

Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com