list fields with 0 values for sum

[hamking01]

I need a list of all items with a sum of values, even when the items have a SumOfValue of 0.

Currently Have:

Item: SumOfValue:
A 5
B 6

Need to Have:

Item: SumOfValue:
A 5
B 6
C 0

The SumOfValue won't list item C which has a sum of 0. How can I get C to be listed.
thank you.

 

[lupins46]

Are you saying that you want to see the C total (0) when there are no C records in the data?
If so you have to create a table containing all the values you want to see and use this as the primary table in your query. Use a left join onto the existing table/query to ensure that you get at least one record for each item.

 

[hamking01]

I have a table of all the items and used the left join to the query. but it still doesn't list items that have no records. I then created a query returning all items and left joined it to the query. here it did return all items. This seems a little redundant. Why wouldn't it work when left joined to a table.

 

[lupins46]

> I then created a query returning all items and left >joined it to the query. here it did return all items

Does this mean that you have a where clause in your original query?
Perhaps you should post that here.

 

[hamking01]

argh, got it to work. did the exact same thing but just wouldn't work last time.