Select the first 3 (or any number) # of records from a table by date

[ammalo]

I create lists of songs for a band. I have a table showing what songs were played at each gig. I would like to create a query to identify what songs were included in the last 3 (or any number) of set lists. How do I create a query to do this?
The table looks like this:

When	Location	1	2
6/10/2016 12:00:00 AM	Dummy	I Can See Clearly Now	It's Not Unusual
5/17/2016 2:00:00 PM	Bickford Cottage	Candy Man, The	Crazy
4/27/2016 3:30:00 PM	Independence Village	9 to 5	Beyond the Sea
4/21/2016 1:30:00 PM	Bickford Cottage	Bushel and a Peck	Copacabana
4/18/2016 2:30:00 PM	Hopedale	Dancing In The Moonlight	Desperado
3/29/2016 1:30:00 PM	Hillside Village	9 to 5	Amazing Grace
3/17/2016 1:30:00 PM	Bickford Cottage	Crazy	Hit the Road Jack
1/27/2016 3:30:00 PM	Independence Village	9 to 5	Bring It On Home To Me
1/21/2016 2:00:00 PM	Bickford Cottage	Beyond the Sea	Daddy's Little Girl

[CJ_London]

I can't make sense of your data - don't understand what columns 1 and 2 are (looks like unnormalised data), but you would use TOP and ORDER BY e.g.

SELECT TOP 3 *
FROM tblSongs
ORDER BY WHEN Desc

[ammalo]

The first column is the date and time. To present it here, I cut and pasted through Excel to make it readable. The "TOP" function is the part I was missing.

[ammalo]

Thanks, Ajax. This gives me what I was looking for. Before this tip, I was setting a design criteria prompt to ask how many days past to include. Is there a way to do something similar for the TOP value, so that the program would ask how many TOP records to include?

[CJ_London]

Is there a way to do something similar for the TOP value, so that the program would ask how many TOP records to include?

in terms of an existing query to run, no. If on the other hand you have vba code to create and run your query, then yes. something like


sqlstr="SELECT TOP " & me.txtTopNo & " * FROM......"


where me.txtTopNo is the name of the control the user has entered the number of records required.

then assign the string to a query, a form or report recordsource, a combo or listbox rowsource

Not that TOP 3 can produce more or less records - if there are only 2 in the recordset then only 2 records are returned. if the values are say

1
1
2
2
2
3

then 4 records will be returned - 1,1,2,2,2

to get round this, you may need an additional column to order by