Code Mechanik Code Mechanik - 6 months ago 7
SQL Question

Access SQL, Aggregate Sums with multiple WHERE clauses and GROUP BY sales person

The following query correctly sums the daily "Sales Total", "Commission" and the "Cost of Goods Sold (COGS)" for each sales person.

The problem is with the "Taxable Sales", "Tax Free Sales", and "Sales Tax" sums because a WHERE clause is needed. The sub-queries with the WHERE clauses return the sum of all sales people combined for each sales person.

I need to modify the query so it returns an individual value for each sales person.

SELECT Format(DatePart("m",sale_date),"00") & "/" & Format(DatePart("d",sale_date),"00") & "/" & DatePart("yyyy",sale_date) AS [Date],

SALES_REP.rep_Name AS [Sales Person],

Round(Nz(Sum(sales_receipt.SELLING_PRICE*sales_receipt.quantity),0),2) AS [Sales Total],

Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001)) AS [Commission],

(SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2)
FROM SALES_RECEIPT
WHERE SALES_RECEIPT.SALE_DATE = Date() and SALES_RECEIPT.SALES_TAX_EXEMPT="No") AS [Taxable Sales],

(SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2)
FROM SALES_RECEIPT
WHERE SALES_RECEIPT.SALE_DATE = Date() and SALES_RECEIPT.SALES_TAX_EXEMPT="Yes") AS [Tax Free Sales],

(SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) * .05
FROM SALES_RECEIPT
WHERE SALES_RECEIPT.SALE_DATE = Date() and SALES_RECEIPT.SALES_TAX_EXEMPT="No") AS [Sales Tax],

Round(Nz(Sum((Nz(inventory.VENDOR_ACTUAL_PRICE,0))*sales_receipt.quantity),0),2) AS COGS

FROM (SALES_RECEIPT
INNER JOIN SALES_REP ON SALES_RECEIPT.REP_ID = SALES_REP.REP_ID)
LEFT JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID

WHERE SALES_RECEIPT.SALE_DATE=Date()
GROUP BY Format(DatePart("m",sale_date),"00") & "/" & Format(DatePart("d",sale_date),"00") & "/" & DatePart("yyyy",sale_date), SALES_REP.rep_Name;


The following code is the updated and corrected version based on the answers I received.

SELECT Format(DatePart("m",sale_date),"00") & "/" & Format(DatePart("d",sale_date),"00") & "/" & DatePart("yyyy",sale_date) AS [Date],

SALES_REP.rep_Name AS [Sales Person],

Round(Nz(Sum(sales_receipt.SELLING_PRICE*sales_receipt.quantity),0),2) AS [Sales Total],

Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001)) AS [Commission],

tsales.sum_taxable_sales AS [Taxable Sales],

tfsales.sum_tax_free_sales AS [Tax Free Sales],

tsales_tax.sum_sales_tax AS [Sales Tax],

Round(Nz(Sum((Nz(inventory.VENDOR_ACTUAL_PRICE,0))*sales_receipt.quantity),0),2) AS COGS

FROM ((((SALES_RECEIPT
INNER JOIN SALES_REP ON SALES_RECEIPT.REP_ID = SALES_REP.REP_ID)
LEFT JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID)

LEFT JOIN

(SELECT SALES_RECEIPT.rep_ID, Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) as sum_taxable_sales

FROM SALES_RECEIPT
WHERE SALES_RECEIPT.SALE_DATE = Date() and SALES_RECEIPT.SALES_TAX_EXEMPT="No"
GROUP BY SALES_RECEIPT.rep_ID)
tsales ON tsales.rep_ID = SALES_REP.rep_ID)

LEFT JOIN

(SELECT SALES_RECEIPT.rep_ID, Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) as sum_tax_free_sales
FROM SALES_RECEIPT
WHERE SALES_RECEIPT.SALE_DATE = Date() and SALES_RECEIPT.SALES_TAX_EXEMPT="Yes"
GROUP BY SALES_RECEIPT.rep_ID) tfsales
ON tfsales.rep_ID = SALES_REP.rep_ID)

LEFT JOIN

(SELECT SALES_RECEIPT.rep_ID, Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2)* .05 as sum_sales_tax

FROM SALES_RECEIPT
WHERE SALES_RECEIPT.SALE_DATE = Date() and SALES_RECEIPT.SALES_TAX_EXEMPT="No"
GROUP BY SALES_RECEIPT.rep_ID)
tsales_tax ON tsales_tax.rep_ID = SALES_REP.rep_ID

WHERE SALES_RECEIPT.SALE_DATE=Date()

GROUP BY Format(DatePart("m",sale_date),"00") & "/" & Format(DatePart("d",sale_date),"00") & "/" & DatePart("yyyy",sale_date)
, SALES_REP.rep_Name
, tsales.sum_taxable_sales
, tfsales.sum_tax_free_sales
, tsales_tax.sum_sales_tax
;

Answer

You can use a sub-query to properly group the records, and join on the sales person. I am making an assumption in the query that rep_name is the key, otherwise replace this in the JOIN ON.

SELECT Format(DatePart("m",sale_date),"00") & "/" & Format(DatePart("d",sale_date),"00") & "/" & DatePart("yyyy",sale_date) AS [Date],

SALES_REP.rep_Name AS [Sales Person], 

Round(Nz(Sum(sales_receipt.SELLING_PRICE*sales_receipt.quantity),0),2)  AS [Sales Total], 

Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001)) AS [Commission],

tsales.sum_taxable_sales AS [Taxable Sales],

Round(Nz(Sum((Nz(inventory.VENDOR_ACTUAL_PRICE,0))*sales_receipt.quantity),0),2) AS COGS

FROM ((SALES_RECEIPT
INNER JOIN SALES_REP ON SALES_RECEIPT.REP_ID = SALES_REP.REP_ID)
LEFT JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID)

LEFT JOIN (SELECT SALES_RECEIPT.rep_ID, Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) as sum_taxable_sales

FROM SALES_RECEIPT
WHERE SALES_RECEIPT.SALE_DATE = Date() and SALES_RECEIPT.SALES_TAX_EXEMPT="No"
GROUP BY SALES_RECEIPT.rep_ID) tsales ON tsales.rep_ID = SALES_REP.rep_ID

WHERE SALES_RECEIPT.SALE_DATE=Date()


GROUP BY Format(DatePart("m",sale_date),"00") & "/" & Format(DatePart("d",sale_date),"00") & "/" & DatePart("yyyy",sale_date), SALES_REP.rep_Name;

Simplified syntax would be:

SELECT a.id, b.value
FROM a
JOIN (SELECT b.id, SUM(b.value) as value FROM b) b ON a.id = b.id