Sow ALL entries.

I guess it depends on whether you are left or right handed;-)

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

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

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)

That only added 2 entries that have NO order data at all.

J_K_M_A_N

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]

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

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

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

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;

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

Thanks, but I sure hate to leave it like this.