sql - Query that will loop through a range of years

Question

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 of years results in an error. I could use UNION statements and repeat the code for each year but that isn't very efficient. Can it be done with just SQL code or does VB have to be used. I don't know VB so if the answer is that VB is needed I would appreciate detailed information.

The code for the ACCESS query is below:

SELECT DISTINCT
    DatePart("yyyy",sale_date) 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 #1/1/2009# And #12/31/2009#) 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 #1/1/2009# And #12/31/2009#) AS COGS,

    (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) - Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * sales_receipt.quantity),0),2) 
     FROM INVENTORY 
     INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009#) AS [Sales Margin],

    Round((((SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) - Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * sales_receipt.quantity),0),2) 
             FROM INVENTORY 
             INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
             WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009# )
             /
            (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 #1/1/2009# And #12/31/2009#))*100),0) & "%" AS [Profit Margin],

    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * deductions_inventory.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_INVENTORY ON INVENTORY.INVENTORY_ID = DEDUCTIONS_INVENTORY.INVENTORY_ID
     WHERE DEDUCTIONS_INVENTORY.ENTRY_DATE Between #1/1/2009# And #12/31/2009# ) AS [Inventory Deductions],

    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * DEDUCTIONS_EXPENSE.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_EXPENSE ON INVENTORY.INVENTORY_ID = DEDUCTIONS_EXPENSE.INVENTORY_ID
     WHERE DEDUCTIONS_EXPENSE.ENTRY_DATE Between #1/1/2009# And #12/31/2009# ) AS [Inventory Expenses], 

    (SELECT Round(Nz(Sum(DEDUCTIONS_FIXTURES_CHAINS.VENDOR_ACTUAL_PRICE*DEDUCTIONS_FIXTURES_CHAINS.quantity),0),2) 
     FROM DEDUCTIONS_FIXTURES_CHAINS
     WHERE DEDUCTIONS_FIXTURES_CHAINS.ENTRY_DATE Between #1/1/2009# And #12/31/2009# and REASON="Breakage-Theft") AS [Fixture & Chain Deductions], 

    (SELECT Round(Nz(Sum(DEDUCTIONS_FIXTURES_CHAINS.VENDOR_ACTUAL_PRICE*DEDUCTIONS_FIXTURES_CHAINS.quantity),0),2) 
     FROM DEDUCTIONS_FIXTURES_CHAINS
     WHERE DEDUCTIONS_FIXTURES_CHAINS.ENTRY_DATE Between #1/1/2009# And #12/31/2009# and REASON="Promotion") AS [Fixture & Chain Expenses], 

    (SELECT Round(Nz(Sum(returns.selling_price * returns.quantity), 0),2)
     FROM returns inner JOIN inventory ON INVENTORY.INVENTORY_ID = returns.INVENTORY_ID
     WHERE returns.return_date Between #1/1/2009# And #12/31/2009# ) AS [Sales Returns], 

    (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) - Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * sales_receipt.quantity),0),2)
     FROM INVENTORY 
     INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009#)
     -
    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * deductions_inventory.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_INVENTORY ON INVENTORY.INVENTORY_ID = DEDUCTIONS_INVENTORY.INVENTORY_ID
     WHERE DEDUCTIONS_INVENTORY.ENTRY_DATE Between #1/1/2009# And #12/31/2009#)
     -
    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * DEDUCTIONS_EXPENSE.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_EXPENSE ON INVENTORY.INVENTORY_ID = DEDUCTIONS_EXPENSE.INVENTORY_ID
     WHERE DEDUCTIONS_EXPENSE.ENTRY_DATE Between #1/1/2009# And #12/31/2009#)
     -
    (SELECT Round(Nz(Sum(DEDUCTIONS_FIXTURES_CHAINS.VENDOR_ACTUAL_PRICE*DEDUCTIONS_FIXTURES_CHAINS.quantity),0),2) 
     FROM DEDUCTIONS_FIXTURES_CHAINS
     WHERE DEDUCTIONS_FIXTURES_CHAINS.ENTRY_DATE Between #1/1/2009# And #12/31/2009#) 
     -
    (SELECT Nz(Sum(returns.selling_price * returns.quantity), 0)
     FROM returns inner JOIN inventory ON INVENTORY.INVENTORY_ID = returns.INVENTORY_ID
     WHERE returns.return_date Between #1/1/2009# And #12/31/2009#) AS [Gross Profit]

FROM
    INVENTORY
    INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
WHERE
    (((SALES_RECEIPT.SAle_date) Between #1/1/2009# And #12/31/2009#))
GROUP BY
    DatePart("yyyy",sale_date);

Answer 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