Query most frequent record

[napsterxp]

Hi there,

Our local radio station has a [relatively simple but complex for me!] database that records which user plays which track and when, manually entered.

I'm looking into making a query that can tell them the top 5 most played tracks this month. So basically I need a query that returns the 5 most frequent records within the current month.

Any help greatly appreciated, and remember baby steps please!

Adam

 

[Cronk]

You could have made it easier to answer by giving details of your table field names and the version of Access you are using.

You need to create a Totals query, with the Properties set to return the first 5 records with a count of the key field, and grouping on the record name and a filter something like the following to select only last month's entries.

format(PlayDate,"yyyymm") = format(dateserial(year(date()), month(date()-1,1),"yyyymm")

I don't know your level of experience using queries to use on-line help for further details and come back here for any guidance.

 

[napsterxp]

Hi,

Many apologies, we're on access 2010. However that sounds like exactly what I was looking for. I shall give it a shot and report back if I have any issues.

Many Thanks