Adding up numbers in coloums

[JohnG]

Hi All,
I have a simple database for packs of timber.
These packs have there own number and size and have many different lengths
from .900m thru to 7.2m in .300mm increments.
The table I created has a seperate coloum for each length and each pack may
or maynot have peices across all these lengths. In most cases alot of the
coloums are blank (no entry at all)
i.e

Pack one: [email protected], 12@1200, blank@1500

I have a simple quesry based on this table and I want to include a colum
called "total peices" that adds up the total ammount of peices in each pack.
(result for the above pack should be 112)
I have tried [.900]+[1200]+[1500] etc based on the main tables entries but
for some reason it only works if there are entries in all the cloums. If a
length colum is blank like the 1500 one in the above example then the formula
returns nothing even though there are lengths in other colums.
Any ideas why my formula does not work if colums are left blank?
Thanks
JohnG from New Zealand

 

[Jeff Boyce]

John

Given how you are processing the information, what you've created sounds
very much like a spreadsheet!

While using a column "for each length" might be necessary if you were using
Excel, you're finding that Access cannot do its best when you feed it 'sheet
data.

Is there a reason you can't just export the data to Excel and do your
calculations there?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John W. Vinson]

Hi All,
I have a simple database for packs of timber.
These packs have there own number and size and have many different lengths
from .900m thru to 7.2m in .300mm increments.
The table I created has a seperate coloum for each length

Then your table is not a table: it is a spreadsheet. It may be just fine for
use in Excel, but this is an INCORRECT DESIGN for a relational database.

and each pack may
or maynot have peices across all these lengths. In most cases alot of the
coloums are blank (no entry at all)
i.e

Pack one: [email protected], 12@1200, blank@1500

I have a simple quesry based on this table and I want to include a colum
called "total peices" that adds up the total ammount of peices in each pack.
(result for the above pack should be 112)
I have tried [.900]+[1200]+[1500] etc based on the main tables entries but
for some reason it only works if there are entries in all the cloums. If a
length colum is blank like the 1500 one in the above example then the formula
returns nothing even though there are lengths in other colums.
Any ideas why my formula does not work if colums are left blank?
Thanks
JohnG from New Zealand

Any mathematical expression involving NULL returns NULL as a result. An empty
field is *not* the same as a zero.

You can use NZ([.900]) + NZ([1200]) + NZ([1500])

However... you would be VERY strongly advised to normalize your data. "Fields
are expensive, records are cheap" - and the lengths *ARE DATA*, and data
should be stored *in* fields, not in fieldnames. Consider using *three* tables
not one:

StandardLengths
Length <Number, Double>

(just to serve as the rowsource for a combo box so you don't need to type the
length)

Packs
PackID
<information about the pack as an entity, nothing about the boards in it>

Boards
PackID <link to Packs, what pack are we talking about>
Length <link to StandardLengths, how long>
Quantity <how many boards of this length are in the pack>

If you have boards of six different lengths in the pack, you would have six
RECORDS in the Boards table for that pack; you can easily display and enter
the data using a Subform, and easily tot up the total number of boards (and
for that matter the total board-meters) using a Totals query.

John W. Vinson [MVP]