Query Return: most recent records only

[Jabiru]

I have a query set up that does a simple percentage calculation from 3 other queries based on 2 fields from a table. No problem with this at all and everything works fine. However, I was wondering if it is possible to make the query continuously return only the 20 (or whatever) most recent records for each name, instead of all records. I have looked at various ways of using the date field, but no luck so far. Any ideas? I am using Access 2000.

thanks.

 

[Ally]

In design view of your query, on the toolbar, there's a dropdown box that by default says "All". If you click that choose, (or overwrite it) with 20. Providing your query is sorted in the correct order, this will then only display the top 20 records!

HTH

 

[Jabiru]

Query Return : most recent records only

Hi, Ally and thanks for your help. However, Top Values is not what I'm after...it only returns the Top 20 names overall. What I need is the 20 most recent records for each individual name - counting back 20 from the most recent date.
I assume DateDiff will not work because it requires actual date entry.

thanks,
Kev.

 

[Pat Hartman]

Look up this article in the kb:
132056 - ACC How to Create a Top Values Per Group Report

I believe that there are samples in the solutions.mdb or the microsoft queries sample db.

 

[Mike375]

You could tgry this. might b de a bit messy for you but if nothing else comes along it should get you over the line.

Make a form (any rough old form will do) on your query. If the data does not have a date like Now() then it will need a autonumber.

I am a macro person and so the macro would do

1) Opens the form
2) GoToControl (autonumber or Now() field
3) Sort in descending order.
4) GoToRecord....First
5) A RunMacro action and the macro being run is GoToRecord...Next
6) The runmacro action has a repeat count of 20 or 100 or whatever
7) OpenQuery action. The query has as its criteria
>=[Forms]![YourFormName]![Auto or Now() field]
8) Macro closes the form

With Echo at No you should just see the query open

I have not tried it but I think it should be OK.

If you wanted to refine it you could an unbound text box on your form (your main form, not the new rough form) and then enter a number like 20 or a 100 and then the runmacro action could be ceased on an expression based on the number you entered.

Mike