Summing accross multiple columns

[hgus393]

Hi all,
I am a noobie to access and access programming and I have a bit of a
conundrum that I cannot solve. I have a database with column headings date, unit and then I have 49 columns from Day one to day 49 with values. What I want to do is "simply" to get the total sum accross these days by unit and date ie.
Unit Date TotalSum
A 03/05/09 sum of all days (from day 1 to day 49)

Thankful for any help!

Cheers

Robert

 

[DCrake]

This sounds like you tables are not normalised, anyway, to acheive your objective you need to create a new query and add a column

Total:Field1+Field2+Field3+etc+Field49

You then need to group by date and sum this column.

Be careful that if some of the 49 fields have null values in them they could report errors.

David

 

[hgus393]

Thanks, however I have null values, how do I get around that?

Bob

 

[DCrake]

The easiest way is to wrap Nz(Fieldnn,0) around each field. You may also want to set 0 as your default value for new records in your table structure.

 

[hgus393]

Thanks NZ works like a charm



Cheers

Bob