Show Zeros/NULLS 1

[mtownbound]

I have a table for shoes and a table for sales. I need to report the number of shoes sold and even the ones that didn't sell. For example:

Brand Sales Net
Nike 540 $32,934.00
Adidas 0 $0.00
Reebok 23 $11,432.00


Thanks

 

[trevil620]

Since it looks like you are totaling values, something like:
SELECT ShoeName, Sum(SalesAmt) as ttlSales, Sum(SalesQty) as ttlQty
FROM tblShoes LEFT JOIN tblShoeSales ON tblShoes.ID = tblShoeSales.ID
GROUP BY tblShoes.ShoeName


The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[mtownbound]

Thanks trevil620! To throw wrench in the mix, I also need to pull in the credit card type from tblCreditCard and tblShoesSales.CreditCardType=tblCreditCard.CreditCardType.

 

[trevil620]

You just need two left joins; I have no clue what else you are trying to do, but I'm guessing you may need to alter some of the 'Group By':

SELECT tblShoe.ShoeMgf, Sum(tblShoeSale.SaleQty) AS SumOfSaleQty, Sum(tblShoeSale.SalePrice) AS SumOfSalePrice, tblShoeSale.SaleType, tblShoeCreditCard.CCType, tblShoeCreditCard.CCNumber, tblShoeCreditCard.CCTotal
FROM (tblShoe LEFT JOIN tblShoeSale ON tblShoe.SID = tblShoeSale.ShoeID) LEFT JOIN tblShoeCreditCard ON tblShoeSale.CreditCardLink = tblShoeCreditCard.CCLink
GROUP BY tblShoe.ShoeMgf, tblShoeSale.SaleType, tblShoeCreditCard.CCType, tblShoeCreditCard.CCNumber, tblShoeCreditCard.CCTotal
ORDER BY tblShoe.ShoeMgf;

The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[SkipVought]

BTW, all this that trevil620 has coded, can be done in the GUI graphically, by double-clicking the LINK between tables on appropraite joins to select the outer join as requierd.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mtownbound]

Thanks to both of you!! Actually, I have to report the number of sales per month showing the shoes not sold, as well. In addition to the join, one of my challenges is pulling the shoes that don't have a transaction date to group on.

 

[SkipVought]

pulling the shoes that don't have a transaction date to group on

You'll need to join a calendar similarly. Your company may have a business calendar table that indicates work days, accounting week/month groupings etc. Otherwise you may have to build one.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mtownbound]

Almost got it. I get all the shoes listed, even the ones that haven't sold in the last 2 years show up with NULL values. They're even grouped by month & year.

The only issue now is that when I try to filter for 2014 (Year([SaleDate])="2014"), the shoes that haven't sold this year disappear. It's like the LEFT join isn't working.

 

[trevil620]

Filter for "2014 or is null"

The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[mtownbound]

Got it. Thanks again!!