Help with Query Total

[mohara]

I am very new at access and need to complete a project at work. I am not very good at sql but can understand some.

I have a table that consists of 500 product labels.

column headings are Label, description, # per roll, May 2010, June 2010 etc.

What I have is an inventory of all labels by month (starting at May 2010 to May 2011) As this is a new project I only have values for May 2010. For testing purposes I have entered a few values in June. When I make a new select query, I select the fields that I want to show and then enter the following in an empty field:

totals:[may 2010]+[june 2010]

The problem is that it only adds the values that exist in both may and june. It will not pull the values from may and enter it unless I have a 0 in the june field.

What am I doing wrong? Sorry if this is a dumb question but my books aren't giving me a clear answer.

Thanks for the help!

[pbaldy]

For starters, it's almost certainly a design mistake to have a field for each month (search on normalization). I suspect you'll be working around this in many areas. In any case:

totals:Nz([may 2010], 0) + Nz([june 2010], 0)

But I'd strongly suggest revisiting the design.

[mohara]

Thanks for the reply. I am working with pre-existing data however, I was thinking the design was the problem and of course, you just verified. Just trying to figure out a quick way around. This will get me the report I need and I can re-work the design

Thanks for the help! Michele

[pbaldy]

Happy to help Michele, and welcome to the site!

[weekend00]

Please mark it as Solved.