Simple Query

[VANWITTEN]

Hi there

Hoping someone can help. I have 3 tables, 1 table for product information, another for artist information and the last one for sales information. I have made a simple query and am trying to find which 5 artists are the most popular based on the number of products sold.

At the moment each sale is listed seperately. I can't figure out how to total those sales by artist. Any help would be greatly appreciated!

I then need to know how to select just the 5 top artists.

 

[Alansidman]

Provide us with the table names and the fields for each table. I believe that this is a case of joining the tables on key fields and running an aggregate query.

 

[Pat Hartman]

Use the sigma button on the ribbon to change the select query to a totals query. Leave group by for the artistID but change it to Sum for the sales amount. Add a sort to sort descending on the sales amount field. And finally, edit the query properties to select the top 5.

 

[VANWITTEN]

Thanks for your responses

I did omit to say that I am using Access 2003 and am a bit of a novice when it comes to this software.

The tables I have are as follows:
Product Table - ProductCode, ProductName, ProductCost, ProductPrice, ProductDesign
Artist Table - ArtistCode, ArtistName, ArtistLocation
Sales Table - InvoiceNumber, ProductCode, ArtistCode, Date, PaymentType

The question I have "which 5 artists are the most popular based on the number of products sold? also asks me to show the Artists name and their counts only.

I have run the relationships between the 3 tables and have created a simple query. The query comes up with every sale made by every artist and I just can't figure out how to total the artists sales so it just shows the total sales made per artist.

 

[Alansidman]

Not sure if the top 5 consisted of $ or count of units. Based upon the information you have provided, here is a SQL for both $ and count. BTW: The term DATE is a reserved term and should not be used. It would be better to use something like sDate or Date1 or some variation.

SELECT TOP 5 Artist.ArtistName, Count(Product.ProductCode) AS CountOfProductCode, Sum(Product.ProductPrice) AS SumOfProductPrice
FROM (Product INNER JOIN Sales ON Product.ProductCode = Sales.ProductCode) INNER JOIN Artist ON Sales.ArtistCode = Artist.ArtistCode
GROUP BY Artist.ArtistName
ORDER BY Count(Product.ProductCode) DESC , Sum(Product.ProductPrice) DESC;

 

[VANWITTEN]

Thanks heaps for that Alan!