Query That Will Loop Through A Range Of Years

I'm looking for a way to have an ACCESS query return results for a range of years. Since there are aggregate calculations in the query, changing the BETWEEN statement to a range o

Solution 1:

In principle you need to replace all Between #1/1/2009# And #12/31/2009# conditions in the subqueries with a condition correlated with the outer query.
If you do it as is, you will have an utterly inefficient query that will calculate totals for each individual record in the outer query.

You do not seem to be using the outer query for anything other than giving you the single year number (2009) in this case.

Therefore change the outer query to simply return the year numbers of interest, and make the subqueries correlated, e.g.

SELECT
    Year(years.year_start) AS [YEAR],

    (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) 
     FROM SALES_RECEIPT
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] between years.year_start and years.year_end) AS [Gross Sales],

    (SELECT Round(Nz(Sum((Nz(inventory.VENDOR_ACTUAL_PRICE,0))*sales_receipt.quantity),0),2) 
     FROM SALES_RECEIPT
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] between years.year_start and years.year_end) AS COGS,

    etc
FROM
    (select
         DateSerial(Year(sale_date), 1, 1) as year_start,
         DateSerial(Year(sale_date), 12, 31) as year_end
     from SALES_RECEIPT
     where sale_date between #1/1/2009# And #12/31/2015#
     group by Year(sale_date)
    ) as years

Solution 2:

Consider removing the outer aggregate query's WHERE clause that filters for 2009 dates and have each subquery relate to distinct sale_date years. No need for the BETWEEN clauses but use DatePart("yyyy", ...) which will correspond to every distinct year in SALES_RECIEPT table. Below is the adjustment for first three subqueries:

SELECT DISTINCT
    DatePart("yyyy", SALES_RECEIPT.sale_date) AS [YEAR],

    (SELECT Round(Nz(Sum(sub1.SELLING_PRICE * sub1.quantity),0) ,2) 
     FROM SALES_RECEIPT AS sub1
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = sub1.INVENTORY_ID
     WHERE DatePart("yyyy", sub1.[SALE_DATE]) =  
           DatePart("yyyy", SALES_RECEIPT.sale_date)) As [Gross Sales],

    (SELECT Round(Nz(Sum((Nz(inventory.VENDOR_ACTUAL_PRICE,0)) * sub2.quantity),0),2) 
     FROM SALES_RECEIPT As sub2
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = sub2.INVENTORY_ID
     WHERE DatePart("yyyy", sub2.SALES_RECEIPT.[SALE_DATE]) = 
           DatePart("yyyy", SALES_RECEIPT.sale_date)) AS COGS,

    (SELECT Round(Nz(Sum(sub3.SELLING_PRICE * sub3.quantity),0) - 
            Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * sub3.quantity),0),2) 
     FROM SALES_RECEIPT As sub3
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = sub3.INVENTORY_ID
     WHERE DatePart("yyyy", sub3.sale_date) =  
           DatePart("yyyy", SALES_RECEIPT.[SALE_DATE])) AS [Sales Margin],

    ...

FROM
    INVENTORY
    INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
GROUP BY
    DatePart("yyyy", SALES_RECEIPT.sale_date);

Solution 3:

You could use the DATEPART function to get the year of the date, like this WHere datepart ("y, fielddaye) <1999 and datepart (y, fieldate ) > 1990. Once it works you can even patamaterise the years. Please note : SUM aggregate NULL to 0, so you don't need NZ function on top of it HTH.