Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Percentage of increase or decrease by year

    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?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That makes sense. Why didn't I think of that? Thanks for your help.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Well, sometimes dealing with queries can be overwhelming. Forest and trees situation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •