2

I have a SALES_RECEIPT table and a (sales) RETURNS table that both have REP_ID as a foreign key from the SALES_REP table. I want to sum total sales and total returns for each sales rep and calculate the commission from the sales and the lost commission from the returns.

The query runs without an error but the values in the columns are not correct. The values are much larger that what the actual values should be. I tried both INNER JOIN and LEFT JOIN to link the RETURNS table but that did not solve the problem. 

 SELECT Format(SALES_RECEIPT.SALE_DATE,'yyyy-mm') AS [Year-Month], 
 SALES_REP.rep_Name, 

 Sum(SALES_RECEIPT.SELLING_PRICE*SALES_RECEIPT.quantity) AS [Total Sales], 
 Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001)) AS [Gross Commission],
 Sum(Nz(returns.selling_price * returns.quantity)) AS [Returns Sales], 
 Sum(Nz(returns.selling_price*returns.quantity)) AS [Returns Sales], Sum((Nz(RETURNS.SELLING_PRICE,0)*Nz(RETURNS.quantity,0))*(Nz(RETURNS.commission_percent,100)*0.001)) AS [Lost Commission], 
 Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001))-Sum((Nz(RETURNS.SELLING_PRICE,0)*Nz(RETURNS.quantity,0))*(Nz(RETURNS.commission_percent,100)*0.001)) AS [Net Commission]

FROM
 (SALES_RECEIPT INNER JOIN SALES_REP ON SALES_RECEIPT.REP_ID = SALES_REP.REP_ID)
 LEFT JOIN RETURNS ON SALES_RECEIPT.REP_ID = RETURNS.REP_ID
 WHERE (((SALES_RECEIPT.SALE_DATE) Between #1/1/2000# And #12/31/2050#))
 GROUP BY Format(SALES_RECEIPT.SALE_DATE,'yyyy-mm'), SALES_REP.rep_Name;
Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
Code Mechanik
  • 83
  • 7
  • Debug it by summing the RETURNS for some SALES only: `SELEcT Sum(Nz(returns.selling_price*returns.quantity)) AS [Returns Sales] FROM RETURNS WHERE REP_ID IN (put some sales IDs here) GROUP BY REP_ID` and check if the numbers returned are correct – Thomas G Apr 23 '16 at 06:16
  • The following code has correct values for "Return Sales" but the how to do everything in the same query is the difficult part. SELECT Format(returns.return_DATE,'yyyy-mm') AS [Year-Month], SALES_REP.rep_Name AS [Sales Person], Sum(Nz(returns.selling_price*returns.quantity)) AS [Returns Sales] FROM RETURNS INNER JOIN SALES_REP ON RETURNS.REP_ID = SALES_REP.REP_ID WHERE RETURNS.RETURN_DATE Between #1/1/2000# And #12/31/2050# GROUP BY Format(RETURNS.RETURN_DATE,'yyyy-mm'), SALES_REP.rep_Name; – Code Mechanik Apr 23 '16 at 14:16
  • Sorry ... not an Access guru ... looks like you also need semicolon at the end of the select. I have edited it one last time. Let me know how it works. – J Greene Apr 27 '16 at 13:00
  • The latest edited code still causes an error in Access. It looks like you have 2 sub-queries within a query to do the aggregate sums from the 2 tables. Then in the main part of the query you select the results of the sub-queries. The syntax for sub-queries in Access must be a little different than the syntax for SQL Server so that's why it causes an error. – Code Mechanik Apr 27 '16 at 15:41

1 Answers1

0

Edited for Access ( trying to fix syntax )

Assuming that the RETURNS table has a Key ID to the SALES_RECEIPT table ?

First, Pull all the SalesReceipt Records based on Date Range Then, Pull all RETURNS based on the same Date Range

WITH totSales as
(
    SELECT
        sr.SalesReceiptID
        ,sr.REP_ID
        ,Format(sr.SALE_DATE,'yyyy-mm') AS [Year-Month] 
        ,(sr.SELLING_PRICE * sr.quantity) AS [Total Sales] 
        ,((Nz(sr.SELLING_PRICE, 0) * Nz(sr.quantity, 0)) * (Nz(sr.commission_percent, 100) * 0.001)) AS [Gross Commission]
    FROM 
        SALES_RECEIPT sr
    WHERE 
        sr.SALE_DATE Between #1/1/2000# And #12/31/2050#
)

,totReturns as 
(
    SELECT
        r.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 r
    WHERE
        r.RETURN_DATE Between #1/1/2000# And #12/31/2050#
)

Select
    ts.Year-Month
    ,sr.REP_Name
    ,SUM(ts.[Total Sales]) as 'TotSales'
    ,SUM(ts.[Gross Commission]) as 'TotCommissions'
    ,SUM(ISNULL(tr.[Return Sales],0) as 'TotReturnSales'
    ,SUM(ISNULL(tr.[Lost Commission],0) as 'TotLostCommissions'
From totSales ts
LEFT JOIN totReturns tr on tr.SalesReceiptID = ts.SalesReceiptID
INNER JOIN 
    SALES_REP sr ON ts.REP_ID = sr.REP_ID)
Group By ts.Year-Month, ts.REP_Name;

From your last comment, I modified the 2nd CTE to get all RETURNS in the same date range used in the SALES_RECEIPT CTE. This should provide you with the proper sum of sales, commissions, returns, and lost commissions over the same date range grouped by specific Sales Rep.

J Greene
  • 261
  • 1
  • 7
  • OP did not mention DBMS which perusing his/her very related last 5 questions is MS Access which does not support window functions. – Parfait Apr 25 '16 at 01:45
  • I get the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', SELECT', or 'UPDATE'. I added a "Net Commission" column which is "Gross Commission" - "Lost Commission". I would like the date column to be in the format Month/Year (with a leading zero for months 1-9) but grouped by year like it is now. Thank you for your help. – Code Mechanik Apr 25 '16 at 21:11
  • What is your dbms ? You should be able to run the CTE code separately to debug that they return what you expect. If you can provide a bit more detail. – J Greene Apr 26 '16 at 00:29
  • My dbms is Microsoft Access 2010 on my Windows 7 home computer. – Code Mechanik Apr 26 '16 at 01:01
  • Sorry ... made a syntax error ... I have updated the answer on the first line. Can you try it again ? Hope this helps. – J Greene Apr 26 '16 at 01:22
  • Same error message and stops on the first line. It appears that comments beginning with 2 dashes are not allowed in Access SQL like they are in SQL Server so I removed the first comment. With the first comment removed, it stops on the first line of ;WITH totSales as I'm only a beginner at SQL but isn't a semicolon a terminator in which no code is executed after a semicolon? I tried removing the semicolon and I still get the same error message that I wrote in my previous comment. Is ;WITH totSales as the start of the code for the query or am I missing something? – Code Mechanik Apr 26 '16 at 04:32
  • The RETURNS table does not have a foreign key from the SALES_RECEIPT table. It has a REP_ID foreign key from the SALES_REP table and a INVENTORY_ID (item number) foreign key from the INVENTORY table. The SALES_RECEIPT table has the same 2 foreign keys as the RETURNS table (REP_ID and INVENTORY_ID foreign keys). There is no direct link between the SALES_RECEIPT and RETURNS tables. – Code Mechanik Apr 26 '16 at 04:40
  • Is there a return date on the RETURNS table ? In your current approach, you are reading back all records per REP_ID in the RETURNS table as opposed to having it linked to items sold in SALES_RECEIPT table. This will be a problem. You really need to have knowledge in the RETURNS table which SALES_RECEIPT someone is returning. – J Greene Apr 26 '16 at 13:14
  • The RETURNS table has the following attributes: Return_ID, Quantity, Selling_Price, Return_Date, Commission_Percent, Comment, Inventory_ID (foreign key), Rep_ID (foreign key). Since the RETURNS table has all the necessary information to do the commission calculations, it shouldn’t be necessary to link the return record to the actual sale record in the SALES_RECEIPT table. Sales and returns are done with paper receipts and are entered later into the database. In a computerized sales system, I understand that it would be necessary to have a direct link between SALES_RECEIPT and RETURNS table. – Code Mechanik Apr 26 '16 at 15:11
  • OK ... I have edited the answer to give the Sales and Returns for the same date range. Hope this is more what you were looking for. Good Luck – J Greene Apr 26 '16 at 23:18
  • ;WITH totSales as doesn't work with ACCESS SQL. The error message is: Invalid SQL statement, expected 'DELETE', 'INSERT', 'PROCEDURE', SELECT', or 'UPDATE' – Code Mechanik Apr 27 '16 at 01:14
  • Try removing the semicolon – J Greene Apr 27 '16 at 01:30
  • I tried removing the semicolon but it still didn't work. In Access SQL you must start the code with DELETE', 'INSERT', 'PROCEDURE', SELECT', or 'UPDATE' and nothing else. – Code Mechanik Apr 27 '16 at 06:03