Sow ALL entries.

I have a two tables like so:

Table1: CustInfo
    CustID - Primary Key
    Name

Table2: OrderInfo
    OrderNumber - Primary Key
    Date
    Amount
    CustID

I want to sum all the amounts from the orderinfo grouped by custID which I can do just fine. What I am having a problem with is showing ALL custID's even if there are no amounts or orders for them. I have the relationship set up to show all custid's and only those with matches in orderinfo but it doesn't work.

Please help.

J_K_M_A_N
LVL 3
J_K_M_A_NAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Select blah blah blah
FROM CustInfo
LEFT JOIN OrderInfo ON CustInfo.blah = OrderInfo.blah

The left join guarantees that all records in CustInfo are returned, even if there's no related OrderInfo records.

Hope this helps.
-Jim
0
josephwalshCommented:
This is an example similar to what you might do

SELECT tblARCustomer.strCustomerID, Sum(tblOEOrder.curOrderTotal) AS SumOfcurOrderTotal
FROM tblOEOrder RIGHT JOIN tblARCustomer ON tblOEOrder.strCustomerID = tblARCustomer.strCustomerID
GROUP BY tblARCustomer.strCustomerID

0
GRayLCommented:
I guess it depends on whether you are left or right handed;-)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ray - You kill me. :)
0
Joe_CoolCommented:
try this

SELECT [Cust info].[Cust name], [order info].*
FROM [Cust info] LEFT JOIN [order info] ON [Cust info].[Cust num] = [order info].[cust num];


It should give you all of the cust info data and all of the order data as well.
0
J_K_M_A_NAuthor Commented:
At the risk of being told that my table SUCKS (which I have already been told) here is my SQL statement.

SELECT Child.Name, (Sum([10A50]+[10A100]+[10A250]+[10A500]+[10A1000])) AS [3/8 Pipe], (Sum([15A50]+[15A100]+[15A250]+[15A500]+[15A1000])) AS [1/2 Pipe], (Sum([20A50]+[20A100]+[20A180]+[20A250]+[20A500])) AS [3/4 Pipe], (Sum([25A50]+[25A100]+[25A180]+[25A250])) AS [1 Pipe], (Sum([32A50]+[32A100]+[32A250])) AS [1-1/4 Pipe], (Sum([38A50]+[38A100]+[38A150])) AS [1-1/2 Pipe], (Sum([50A50]+[50A100]+[50A150])) AS [2 Pipe], Sum(Orders.Value) AS DollarValue
FROM Child LEFT JOIN Orders ON Child.ChildID = Orders.ChildID
WHERE (((Orders.Date) Between #3/1/2006# And #3/24/2006#))
GROUP BY Child.Name
ORDER BY Child.Name;

And it does NOT return all 57 names in the Child table. I am not sure why. Maybe because of the sums I am doing? I don't know. I am guessing it is beacuse of the WHERE with the dates. I am guessing it doesn't like that. Is there a way around it?

J_K_M_A_N
0
J_K_M_A_NAuthor Commented:
I took out the WHERE line and it returns all but for ALL of the data. Is there a way to get just the dates I want and still LEFT JOIN?

J_K_M_A_N
0
GRayLCommented:
Change:

WHERE (((Orders.Date) Between #3/1/2006# And #3/24/2006#))

To:

WHERE (Orders.Date Between #3/1/2006# And #3/24/2006#) OR IsNull(Orders.Date)
0
J_K_M_A_NAuthor Commented:
That only added 2 entries that have NO order data at all.

J_K_M_A_N
0
GRayLCommented:
Maybe I'm missing something, but if some of the Child.Names do not have any orders and you use date criteria in the WHERE clause, how can you expect to see them, unless you include the IsNull() criterion?  Are you saying there are more than 2 Names missing?  Can you give us an example of what is missing?  I just noticed you use the word Date as a field - that is a no-no as Date is an Access Reserved Word.  If you absolutely have to use it, it must be wrapped in square brackets.

Orders.[Date]
0
J_K_M_A_NAuthor Commented:
Well, the orders table holds all the data for the past year+. I want to show all customers and the totals of a certain month. I was hoping to do that with one SQL statement. It looks like I will not be able to do that. Some of the customers have data in the table but not that are between the dates I put in. I would like to see all of their totals (even if they are 0) for all customers. Does that explain it better?

J_K_M_A_N
0
GRayLCommented:
OK, the two customers that were added were the only two missing or were there more?  Try this query.  I have included a parameters criteria which includes the year as you said the table contains all the data for the past year+.  You should also qualify all the fields in the sum() functions with their table name:

SELECT Child.Name, (Sum([10A50]+[10A100]+[10A250]+[10A500]+[10A1000])) AS [3/8 Pipe], (Sum([15A50]+[15A100]+[15A250]+[15A500]+[15A1000])) AS [1/2 Pipe], (Sum([20A50]+[20A100]+[20A180]+[20A250]+[20A500])) AS [3/4 Pipe], (Sum([25A50]+[25A100]+[25A180]+[25A250])) AS [1 Pipe], (Sum([32A50]+[32A100]+[32A250])) AS [1-1/4 Pipe], (Sum([38A50]+[38A100]+[38A150])) AS [1-1/2 Pipe], (Sum([50A50]+[50A100]+[50A150])) AS [2 Pipe], Sum(Orders.Value) AS DollarValue
FROM Child LEFT JOIN Orders ON Child.ChildID = Orders.ChildID
WHERE Format(Orders.[Date],"yym") = [Enter Year and  Month Number (yym)] OR IsNull(Orders.[Date])
GROUP BY Child.Name
ORDER BY Child.Name;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J_K_M_A_NAuthor Commented:
Let me give you some sample data:

Table Orders:
Date              ChildID  10A50   10A100   10A250   10A500   10A1000   Value  (There is more but this will do for an example)
02/25/2006        2         100      200          0              0             0        $3400
02/18/2006        3          50       300         0              0              0        $1700
03/27/2006        2          50        100         0              0             0        $2300
03/15/2006        1          50         0           250          500           0       $2700
03/18/2006        2          50        100         0              0            1000   $3500


Now what I want is a list like the one below using the query where I can tell it what dates I want. In this case, I want all of March 2006 so I would like to see something like this:

ChildID    10A50   10A100   10A250   10A500   10A1000   Value
   1            50          0            250        500          0         $2700
   2           100        200           0            0          1000      $5800
   3             0           0              0          0            0          $0

ChildID 3 would be zero because that sale was in Feburary. ChildID 1 and 2 would sum the fields together but only for the month of march. Is there a way to do that?


J_K_M_A_N
0
J_K_M_A_NAuthor Commented:
I have inreased the value to 500 and I should also mention, I am actually using VB6. I am just trying to get it to work in Access first and then I am going to transfer it to VB6. Thanks everyone.

J_K_M_A_N
0
GRayLCommented:
If it is a case of seeing zeros instead of a blank, try:

SELECT Child.Name,
Nz(Sum([10A50]+[10A100]+[10A250]+[10A500]+[10A1000]),0) AS [3/8 Pipe],  Nz(Sum([15A50]+[15A100]+[15A250]+[15A500]+[15A1000]),0) AS [1/2 Pipe],  Nz(Sum([20A50]+[20A100]+[20A180]+[20A250]+[20A500]),0) AS [3/4 Pipe],
Nz(Sum([25A50]+[25A100]+[25A180]+[25A250]),0) AS [1 Pipe],0),
Nz(Sum([32A50]+[32A100]+[32A250]),0) AS [1-1/4 Pipe],
Nz(Sum([38A50]+[38A100]+[38A150]),0) AS [1-1/2 Pipe],  
Nz(Sum([50A50]+[50A100]+[50A150]),0) AS [2 Pipe],
Format(Nz(Sum(Orders.Value),0),"Currency") AS DollarValue
FROM Child LEFT JOIN Orders ON Child.ChildID = Orders.ChildID
WHERE Format(Orders.[Date],"yym") = [Enter Year and  Month Number (yym)] OR IsNull(Orders.[Date])
GROUP BY Child.Name
ORDER BY Child.Name;
0
J_K_M_A_NAuthor Commented:
The blank would be fine but I don't get that. Not for ALL of the customers. I am going to give out the points and give up. I really don't know what I am doing and I need to find a good book on the subject. I have the program written without using a database and it works ok. I will just stick with that and stop trying to learn database programming for now. It is driving me crazy. Thanks for all of your attempts.

J_K_M_A_N
0
GRayLCommented:
Thanks, but I sure hate to leave it like this.  
0
J_K_M_A_NAuthor Commented:
Well, I don't either but I really need a book and/or some examples to learn from. I think I am trying to jump in TOO deep to start. That makes it a little hard to keep your sanity. :)

Thanks anyway for the help.

J_K_M_A_N
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.