INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Show Zeros/NULLS

Show Zeros/NULLS

(OP)
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

RE: Show Zeros/NULLS

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?

RE: Show Zeros/NULLS

(OP)
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.

RE: Show Zeros/NULLS

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':

CODE -->

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?

RE: Show Zeros/NULLS


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Show Zeros/NULLS

(OP)
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.

RE: Show Zeros/NULLS


Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Show Zeros/NULLS

(OP)
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.

RE: Show Zeros/NULLS

Filter for "2014 or is null"

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

RE: Show Zeros/NULLS

(OP)
Got it. Thanks again!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close