How to limit to the 6 most recent dates?

[fat controller]

I have a form with a combo that allows users to select from dates that are found in a table; as there are numerous records within the table that have the same date, I have used the min function to ensure that each date only shows once. However, the list of dates is growing, and I was wondering if there was a way to limit the list of dates to the six most recent ones?

The SQL for the combo is as follows:

SELECT Min([4WeeklyPayControllersHistory].ID) AS ID, [4WeeklyPayControllersHistory].[4WeeksCommencing]
FROM 4WeeklyPayControllersHistory
GROUP BY [4WeeklyPayControllersHistory].[4WeeksCommencing]
ORDER BY [4WeeklyPayControllersHistory].[4WeeksCommencing];

 

[Alc]

Perhaps order by date and use 'Top 6' in the query?

 SELECT Top 6 Min([4WeeklyPayControllersHistory].ID) AS ID, [4WeeklyPayControllersHistory].[4WeeksCommencing]
FROM 4WeeklyPayControllersHistory
GROUP BY [4WeeklyPayControllersHistory].[4WeeksCommencing]
ORDER BY [4WeeklyPayControllersHistory].[4WeeksCommencing];

 

[fat controller]

I didn't know you could put something like that in a query, thank you

It works, sort of, but it is returning the 6 oldest dates as opposed to the 6 most recent ones - - I have tried swapping the Top for Bottom or Last and that produces an error; what is the opposite of Top in Access?

EDIT - cracked it! I sorted in descending order and the top 6 of course is now those that were the bottom 6.

Thank you

 

[Alc]

Good to hear, glad it worked out.

 

[marstev]

Can this be done in a regular query, or does it have to be done using SQL? I am starting to learn basic query functions, but am not quite proficient with SQL.

 

[Frothingslosh]

Regular queries ARE SQL.

That said....

One of the options on the ribbon on the query design toolbar is 'Return'. It normally says 'All' next to it - you can change that to set a hard limit on the number of records returned instead.

 

[mary]

I'm having the same issue where it is returning the bottom 3 dates rather than the top. I tried changing the sort from ascending to descending as suggested, but no luck. Any thoughts?

thanks,
Mary

 

[jdraw]

Show us your query SQL and a brief description of your data and what you're trying to accomplish.