Hello all,
I am very new to Access, am doing an online course and learning as I go.  
I am stuck with one query and I'm hoping someone can help me.
I have a sales table for which I am required to produce a query which tells 
the most popular artists and the number of sales they have made.  
The fields in the table are:-
Invoice number, product code, artist code, date, payment type.
The query is on the Artist Code field, but for the life of me I cannot get 
it to show just the top 5 Artists and their sales count.
Hoping for help - written slowly 'cos I'm very new at this.
Thanks in advance. 

"Lorri" <Lorri@discussions.microsoft.com> wrote in message 
news:7733272A-0BF7-4226-824D-0AAC25F7B2C7@microsoft.com...
> Hello all,
> I am very new to Access, am doing an online course and learning as I go.
> I am stuck with one query and I'm hoping someone can help me.
> I have a sales table for which I am required to produce a query which 
> tells
> the most popular artists and the number of sales they have made.
> The fields in the table are:-
> Invoice number, product code, artist code, date, payment type.
> The query is on the Artist Code field, but for the life of me I cannot get
> it to show just the top 5 Artists and their sales count.
> Hoping for help - written slowly 'cos I'm very new at this.
> Thanks in advance.


Assuming that each record in your Sales table represents a sale for a given 
artist, then first you need a totals query that returns the artist code and 
the count of records for each artist code.  That will be the sales count. 
You can make such a query in the query designer by creating a new query 
based on the Sales table.  Click the "Totals" button on the toolbar (the one 
with the caption that is the Greek sigma -- looks like a big, stylized "E") 
to make it into a totals query.  Drag the [Artist Code] to the field grid 
twice.  On the "Total:" row under the first column, choose "Group By", and 
under the second column for the field, choose "Count".  Give that field an 
alias of SalesCount by changing the field name so that it looks like this:

    SalesCount: [Artist Code]

At this point, if you switch into SQL view, the query's SQL looks something 
like this:

    SELECT [Artist Code], Count([Artist Code] As SalesCount
    FROM Sales
    GROUP BY [Artist Code];

If you flip into datasheet view, you'll see the raw results, ordered by 
artist code.

Now, to get the top 5, we need to rank these results in descending order by 
the calculated field SalesCount.  Switch back to design view and on the 
"Sort: row" of the field grid, under SalesData, choose "Descending".  The 
SQL view of the query would now look like this:

    SELECT [Artist Code], Count([Artist Code] As SalesCount
    FROM Sales
    GROUP BY [Artist Code]
    ORDER BY Count([Artist Code]) DESC;

Finally, we need just the top 5.  So in design view, go to the "Top Values" 
dropdown box on the toolbar and enter or choose 5.  Flip to SQL view and it 
should now look like this:

    SELECT TOP 5 [Artist Code], Count([Artist Code] As SalesCount
    FROM Sales
    GROUP BY [Artist Code]
    ORDER BY Count([Artist Code]) DESC;

If you switch to datasheet view, you should see that it gives you the 
desired result.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)