Listing wrong number of items sold and paid - need help

[Ken]

Hi,

Trying find number of item sold and paid. There are 3 tables and taking value from each table for the result.

[pre]Tables:
1) tProduct_Sold
2) tSub_Product_Details
3) tStore_Location

e.g.
tProduct_Sold
PK
Item_id date
1 1/1/2015
2 1/2/2015
3 1/3/2015
4 1/4/2015
5 1/5/2015


tSub_Product_Detail
FK FK
date Item_id Sub_item_id Sold_Status Payment_Status Location_id
1/1/2015 1 1A Sold Paid 1
1/1/2015 1 1B Sold Paid 1
1/2/2015 2 2A Sold UnPaid 5
1/2/2015 2 2B Sold UnPaid 5
1/3/2015 3 3A Sold In-Process 25
1/4/2015 4 4A Sold Paid 12
1/4/2015 4 4B Sold Paid 12
1/5/2015 5 5A Sold In-Process 12

tStore_Location
PK
Location_Id LocationName
1 Denton
2 Wichita Falls
3 Valley Brook
4 Cape Coral
5 Richmond
10 Charlotte
12 Jackson
25 Edmond

Output required
LocationName CountOfItem_id Paid
Denton 1 1
Edmond 1 0
Jackson 1 1
Richmond 1 1[/pre]

SELECT SL.LocationName, 
       Count(PS.Item_id) AS CountOfItem_id,
       Sum(IIf(SPD.[Sold_Status]="Paid",1,0)) AS Sold

FROM  (tProduct_Sold PS

LEFT JOIN tSub_Product_Detail SPD ON PS.Item_id = SPD.Item_id) 
LEFT JOIN tStore_Location SL      ON SPD.Location_id = SL.Location_Id

GROUP BY SL.LocationName;

Getting different result, not sure what is missing in the SQL ?
[pre]LocationName CountOfItem_id Sold
Denton 2 0
Edmond 1 0
Jackson 3 0
Richmond 2 0[/pre]

Thanks,

Tech

 

[dhookom]

Only Denton and Jackson have items with a Payment_Status of "Paid". How do you get Richmond?

Duane
Hook'D on Access
MS Access MVP

 

[Ken]

Hi Duane,

Typo error, Richmond paid number is 0.

Thanks,
TechIt

 

[dhookom]

First I would change
Sum(IIf(SPD.[Sold_Status]="Paid",1,0)) AS Sold
to
Sum(IIf([SPD].[Payment_Status]="Paid",1,0)) AS Sold

You might need to create a totals query first and then build you final query off the totals query.

Duane
Hook'D on Access
MS Access MVP

 

[Ken]

Hi Duane,

For even getting total the numbers are increased twice due to tSub_Product_Detail has 2 rows for each item. After running below query the counting is twice instead of 1.

SELECT  SL.LocationName, SPD.Sold_Status, SPD.Payment_Status, 
        Count(SPD.Payment_Status) AS CountOfPayment_Status
FROM    tSub_Product_Detail SPD
INNER JOIN tStore_Location SL ON SPD.Location_id = SL.Location_Id
GROUP BY SL.LocationName, SPD.Sold_Status, SPD.Payment_Status;

Result from above sql
[pre]LocationName Sold_Status Payment_Status CountOfPayment_Status
Denton Sold Paid 2 (this should be 1)
Edmond Sold In-Process 1
Jackson Sold In-Process 1
Jackson Sold Paid 2 (this should be 1)
Richmond Sold UnPaid 2 this should be 1 [/pre]

Needs a condition like if count> 2 then it result be 1 for item sold and if paid item sold then 1 for paid too.

Thanks

Techit

 

[dhookom]

As I stated, I think you need to do this with more than one query.

Duane
Hook'D on Access
MS Access MVP