Query to find top 6 parts sold per month...??

[Triona*]

Hi folks,
Im trying to make a chart to show my 5 best selling parts per month, but can't get the query right.

I started off with the following (Query1) to get the quantity of each part sold each day:

SELECT tlbSales.SaleDate, tblSales.[P/N], Sum(tblSales.Quantity) AS Quantity FROM tblSales

Then i did the following (Query2)to get the quantity of each part sold per month:

SELECT Query1.[P/N], Sum(Query1.Quantity) AS TotalPerMonth, Format([SaleDate],"mmm-yy") AS DateOfSale FROM Query1

So now i have totals of each part sold per month but i need to chart the top 6 sellers per month and i dont have a clue where to go from here...please help me...

Cheers

 

[ByteMyzer]

Try this:

SELECT Year(t1.SaleDate) AS SaleYear, Month(t1.SaleDate) AS SaleMonth, t1.[P/N], Sum(t1.Quantity) AS SumOfQuantity
FROM tblSales AS t1
WHERE t1.[P/N] IN (
SELECT TOP 6 t2.[P/N]
FROM tblSales AS t2
WHERE Year(t2.SaleDate)=Year(t1.SaleDate) AND Month(t2.SaleDate)=Month(t1.SaleDate)
GROUP BY t1.[P/N]
ORDER BY Sum(t2.Quantity) DESC
GROUP BY Year(t1.SaleDate) AS SaleYear, Month(t1.SaleDate) AS SaleMonth, t1.[P/N];


See if this works for you.

 

[Triona*]

you are a LEGEND! thanks.
One problem though....when i tried to use this query as the basis for my chart the following error popped up:

'Microsoft jet database engine does not recognise 't1.SaleDate' as a valid field name or expression'

Any ideas why this happens??

cheers

 

[woknick]

I usually get that error if I have mispelled or refrenced the a table incorrectly. Try checking the naming on 't1.SaleDate'.

Hope this helps