Prev: Not sure how to do this part
Next: I can't agree with you more about the necessity of
From: Lorri on 22 Jan 2010 13:47
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.
From: Dirk Goldgar on 22 Jan 2010 14:15
"Lorri" <Lorri(a)discussions.microsoft.com> wrote in message
news:7733272A-0BF7-4226-824D-0AAC25F7B2C7(a)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)

 | 
Pages: 1
Prev: Not sure how to do this part
Next: I can't agree with you more about the necessity of