CODEWITHSUNDEEP
ms-accesssubqueryaggregate-functions
Code Mechanik
Code Mechanik

Reputation: 83

ACCESS Sub-queries and aggregate calculations

I have a SALES RECEIPT table and a RETURNS table. The information in the two tables are independent of each other except that each table has the Rep_ID attribute from the SALES_REP table.

In the first sub-query, I calculate the sum of the “Total Sales” and the “Gross Commission” from the SALES RECEIPT table for each sales person on a monthly basis.

In the second sub-query, I calculate the sum of the “Return Sales” and the “Lost Commission” from the RETURNS table for each sales person on a monthly basis.

In the main query, I want to display for each sales person on a monthly basis the “Total Sales”, “Gross Commission”, “Return Sales”, “Lost Commission” and “Net Commission”. The “Net Commission” is the “Gross Commission” minus the “Lost Commission”.

With the code I have, I get the error message:

"The Microsoft Access database engine cannot find the input table or query ‘totSales’. Make sure it exists and that its name is spelled correctly.” 

Select 
    totSales.Year-Month as [Month/Year],
    SALES_REP.rep_name as [Sales Person],
    SUM(totSales.[Total Sales]) as [Total Sales],
    SUM(totSales.[Gross Commission]) as [Gross Commission],
    SUM(totReturns.[Return Sales],0) as [Sales Returns],
    SUM(totReturns.[Lost Commission],0) as [Lost Commission],
    Round([Gross Commission] - [Lost Commission],2) AS [Net Commission],


    (SELECT
        Format(SALES_RECEIPT.sale_date,'yyyy-mm') AS [Year-Month], 
        SALES_RECEIPT.rep_id,
        ( SALES_RECEIPT.selling_price *  SALES_RECEIPT.quantity) AS [Total Sales],
        ((Nz(SALES_RECEIPT.selling_price, 0) * Nz( SALES_RECEIPT.quantity, 0)) * (Nz(SALES_RECEIPT.commission_percent, 100) * 0.001)) AS [Gross Commission]
    FROM 
        SALES_RECEIPT
    WHERE 
        SALES_RECEIPT.sale_date Between #1/1/2000# And #12/31/2050#) AS totSales,


    (SELECT
        RETURNS.rep_id, 
        (Nz(RETURNS.selling_price * RETURNS.quantity)) AS [Sales Returns], 
        ((Nz(RETURNS.selling_price, 0) * Nz(RETURNS.quantity, 0)) * (Nz(RETURNS.commission_percent, 100) * 0.001)) AS [Lost Commission]
    FROM
        RETURNS
    WHERE
        RETURNS.return_date Between #1/1/2000# And #12/31/2050#) As totReturns

From (totSales
  LEFT JOIN totReturns on totReturns.rep_id = totSales.rep_id)
  INNER JOIN SALES_REP ON totSales.REP_ID = SALES_REP.rep_id

Group By totSales.Year-Month, totSales.rep_name;

The query below returns monthly sales commission data but only for one sales person. I want the above query to return the exact same results as the query below but for all sales persons not just one sales person.

SELECT Format(DatePart("m",months.month_start),"00") & "/" & Year(months.month_start) AS [Month/Year],

 (SELECT SALES_REP.rep_name  FROM SALES_REP WHERE SALES_REP.rep_id = 1) AS [Sales Person],

 (select Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) 
 FROM SALES_RECEIPT INNER JOIN SALES_REP ON SALES_REP.REP_ID = SALES_RECEIPT.REP_ID 
 WHERE SALES_RECEIPT.[SALE_DATE] between months.month_start and months.month_end and SALES_REP.rep_id = 1) AS [Total Sales], 

 (SELECT Round((Sum(((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(sales_receipt.quantity,0))*(Nz(sales_receipt.commission_percent,100)*0.001)))),2) 
 FROM SALES_RECEIPT INNER JOIN SALES_REP ON SALES_REP.REP_ID = SALES_RECEIPT.REP_ID
 WHERE SALES_RECEIPT.[SALE_DATE] between months.month_start and months.month_end and SALES_REP.rep_id = 1) AS [Gross Commission],

 (SELECT Round(Nz(Sum(returns.selling_price * returns.quantity), 0),2)
 FROM (returns inner JOIN inventory ON INVENTORY.INVENTORY_ID = returns.INVENTORY_ID)
 LEFT JOIN SALES_REP ON SALES_REP.REP_ID = returns.REP_ID
 WHERE returns.return_date between months.month_start and months.month_end AND SALES_REP.rep_id = 1) AS [Sales Returns], 

 (SELECT Round(Nz((Sum(((Nz(returns.SELLING_PRICE,0)*Nz(returns.quantity,0))*(Nz(returns.commission_percent,100)*0.001)))),0),2)
 FROM (returns inner JOIN inventory ON INVENTORY.INVENTORY_ID = returns.INVENTORY_ID)
 LEFT JOIN SALES_REP ON SALES_REP.REP_ID = returns.REP_ID
 WHERE returns.return_date between months.month_start and months.month_end AND SALES_REP.rep_id = 1) AS [Lost Commission], 

 (SELECT Round((Sum(((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(sales_receipt.quantity,0))*(Nz(sales_receipt.commission_percent,100)*0.001)))),2) 
 FROM SALES_RECEIPT INNER JOIN SALES_REP ON SALES_REP.REP_ID = SALES_RECEIPT.REP_ID WHERE SALES_RECEIPT.[SALE_DATE] between months.month_start and months.month_end and SALES_REP.rep_id = 1) -   (SELECT Round(Nz((Sum(((Nz(returns.SELLING_PRICE,0)*Nz(returns.quantity,0))*(Nz(returns.commission_percent,100)*0.001)))),0),2)   
 FROM(returns inner JOIN inventory ON INVENTORY.INVENTORY_ID = returns.INVENTORY_ID)
 LEFT JOIN SALES_REP ON SALES_REP.REP_ID = returns.REP_ID
 WHERE returns.return_date between months.month_start and months.month_end AND SALES_REP.rep_id = 1) AS [Net Commission]

FROM 
(SELECT DateSerial(Year(sale_date), Month(sale_date), 1) AS month_start,
 DateAdd("d", -1,  DateSerial(Year(sale_date), Month(sale_date) + 1, 1)) AS month_end
 FROM SALES_RECEIPT
 WHERE sale_date between #1/1/2000# And #12/31/2100#

 GROUP BY Year(sale_date), Month(sale_date))  AS months;

Upvotes: 0

Views: 419

Answers (1)

C. White
C. White

Reputation: 800

Move the two subqueries so that they are in the FROM...JOIN section of the query. As @Leviathan noted, the query is currently treating them as if they were fields (which they aren't, of course).

Also: be sure that you include all of the fields by which you are grouping in the GROUP BY clause (and do not alias them--you'll get an error).

Here's a stab at the first query. Access is screwy with how it likes parentheses when you're doing multiple joins, so apologies if it doesn't work the first time:

Select 
    totSales.Year-Month as [Month/Year],
    SALES_REP.rep_name as [Sales Person],
    SUM(totSales.[Total Sales]) as [Total Sales],
    SUM(totSales.[Gross Commission]) as [Gross Commission],
    SUM(totReturns.[Return Sales],0) as [Sales Returns],
    SUM(totReturns.[Lost Commission],0) as [Lost Commission],
    Round(TotCommissions - TotLostCommissions,2) AS [Net Commission]

FROM
  SALES_REP
INNER JOIN
  (
    (SELECT
        Format(SALES_RECEIPT.sale_date,'yyyy-mm') AS [Year-Month], 
        SALES_RECEIPT.rep_id,
        ( SALES_RECEIPT.selling_price *  SALES_RECEIPT.quantity) AS [Total Sales],
        ((Nz(SALES_RECEIPT.selling_price, 0) * Nz( SALES_RECEIPT.quantity, 0)) * (Nz(SALES_RECEIPT.commission_percent, 100) * 0.001)) AS [Gross Commission]
    FROM 
        SALES_RECEIPT
    WHERE 
        SALES_RECEIPT.sale_date Between #1/1/2000# And #12/31/2050#) AS totSales
LEFT JOIN
    (SELECT
        RETURNS.rep_id, 
        (Nz(RETURNS.selling_price * RETURNS.quantity)) AS [Returns Sales], 
        ((Nz(RETURNS.selling_price, 0) * Nz(RETURNS.quantity, 0)) * (Nz(RETURNS.commission_percent, 100) * 0.001)) AS [Lost Commission]
    FROM
        RETURNS
    WHERE
        RETURNS.return_date Between #1/1/2000# And #12/31/2050#) As totReturns
on totReturns.rep_id = totSales.rep_id
  )
ON totSales.REP_ID = SALES_REP.rep_id

Group By 
  totSales.Year-Month
  ,SALES_REP.rep_name
  ,Round(TotCommissions - TotLostCommissions,2)
;

Also: as a style thing: it's generally preferred to put the commas separating multiple fields on the line with the field that follows them, e.g.:

SELECT
  SomeField
  ,SomeOtherField

not:

SELECT
  SomeField,
  SomeOtherField

Some folks get really bent out of shape about it, so probably a good idea to try and get in the habit of leading with the comma to spare yourself unnecessary criticism.

Upvotes: 1

Related Questions