Percentage of increase or decrease by year

Topic

New Reply

I am building a query that tracts the amount of product shipped per year (I am only using 2 years for now). I would like to see the percentage of increase or decrease. How can I build that in to the query?

Reply | Quote

Replies

The simplest way would be to start with a couple of queries, one for each year. So your qryShipmentsIn2013 might look like this:

SELECT ProductID, Sum(Quantity) AS QuantityShipped2013 FROM tblShipments WHERE ShipDate Between #1/1/2013# AND #12/31/2013# GROUP BY ProductID

Similarly, you’d have a qryShipmentsIn2014. Then create a 3rd query that joins the first 2 queries on ProductID. In this query, (QuantityShipped2014-QuantityShipped2013)/QuantityShipped2013 gives you the percentage increase/decrease from 2013.

Reply | Quote

That makes sense. Why didn’t I think of that? Thanks for your help.

Reply | Quote

Well, sometimes dealing with queries can be overwhelming. Forest and trees situation.

Reply | Quote