Solved Group salesproduct table by year and itemID? (1 Viewer)

A1ex037

Registered User.
Local time
Today, 20:13
Joined
Feb 10, 2018
Messages
50
Hello,
I am trying to figure out how to make a query that will have total sales (by quantity) of every product by year?
Table SalesProduct has SaleID, itemID, Quantity fields (and a few others not relevant right now).
Table Sales has SaleDate, SaleID (and a few others not relevant right now).

Is there any way to provide that information in a single query? I know how to extract for each year (separate query per year), but maybe there is an easier option?
Idea is to have years in rows, ItemIDs in columns.

Thanks.
 

plog

Banishment Pending
Local time
Today, 14:13
Joined
May 11, 2011
Messages
11,611
What you've described is achievable via a cross-tab query:


I suggest making an aggregate query first:


Make sure the data is correct, it will have a column for year and a column for data. Then when that looks good, make it a cross-tab.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:13
Joined
May 7, 2009
Messages
19,169
Code:
SELECT Year(Sales.SaleDate) As [Year],
    SalesProduct.ItemID,
    Sum(SalesProduct.Quantity) As [Total Sales]
FROM SalesProduct Inner Join Sales
ON SalesProduct.SalesID = Sales.SalesID
GROUP BY Year(Sales.SalesDate), SalesProduct.ItemID
ORDER BY Year(Sales.SalesDate), SalesProduct.ItemID;
 

Users who are viewing this thread

Top Bottom