summing fields

[stephendeloach via AccessMonster.com]

I have a form with the fields AR, LA, OK, TX, MS, Other... I enter miles in
each field. Somedays I wont enter anything in the field. How do I add all of
the entries and put them in the "Total Daily Mileage" field. I have it
working now by entering =[AR]+[LA]+[TX]+[MS]+[OK]+[Other] but when there is
nothing entered in one field nothing shows in Total Daily Mileage...

 

[KARL DEWEY]

The simple way is to set the field default to zero and then they will sum.
Update all fields that are now null.

 

[Jeff Boyce]

First, a caution...

If you have repeating fields (you appear to have multiple "state" fields) in
your table definition, you have ... a spreadsheet! You won't get the best
from Access' features and functions if you feed it 'sheet data. Is there a
reason you aren't just doing this in Excel?

Next, in Access, nulls propogate. As you've found out, having "nothing" in
at least one of those fields (spreadsheet columns!) means the sum of them
will be a nothing/null.

If you insist on keeping this decidedly un-normal data structure, you can
modify your formula/expression to use the Nz() function to convert nulls to
zeros (Nz([AR],0) + Nz([LA],0) + ...). Be aware, however, if you ever add
another state, you'll have to modify any/all formulas, your table structure,
any queries involving that that table, your forms based on that table, any
reports, any macros, any code ... -- what a maintenance headache!

Seriously consider looking into the topic of normalization -- Access is
designed to work with well-normalized data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[stephendeloach via AccessMonster.com]

First- When I change the default value to zero nothing shows (0) in the
fields?
Second- =(Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+([MS],0)+([OK],0)+([Other],0)) is
the expression i entered and it says #ERROR. THe reason I am doing this is
because my boss wants all of the data to be moved into a database.... What
would you consider to be "normalized" data? Thanks

First, a caution...

If you have repeating fields (you appear to have multiple "state" fields) in
your table definition, you have ... a spreadsheet! You won't get the best
from Access' features and functions if you feed it 'sheet data. Is there a
reason you aren't just doing this in Excel?

Next, in Access, nulls propogate. As you've found out, having "nothing" in
at least one of those fields (spreadsheet columns!) means the sum of them
will be a nothing/null.

If you insist on keeping this decidedly un-normal data structure, you can
modify your formula/expression to use the Nz() function to convert nulls to
zeros (Nz([AR],0) + Nz([LA],0) + ...). Be aware, however, if you ever add
another state, you'll have to modify any/all formulas, your table structure,
any queries involving that that table, your forms based on that table, any
reports, any macros, any code ... -- what a maintenance headache!

Seriously consider looking into the topic of normalization -- Access is
designed to work with well-normalized data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with the fields AR, LA, OK, TX, MS, Other... I enter miles in
each field. Somedays I wont enter anything in the field. How do I add all

[quoted text clipped - 3 lines]

is
nothing entered in one field nothing shows in Total Daily Mileage...

 

[KARL DEWEY]

First - I said "Update all fields that are now null."
Second - You tables
should look like this --
Trips ---
TripID - autonumber - primary key
TripDate - DateTime
Traveler - text - name os person traveling/driving

Travel --
TravelID - autonumber - primary key
TripID - number - long integer - foreign key related to Trips.TripID
State - text - state transversed
StartDate - DateTime
StartMile - number
EndDate - DateTime
EndMile - number
Etc -
--
KARL DEWEY
Build a little - Test a little

First- When I change the default value to zero nothing shows (0) in the
fields?
Second- =(Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+([MS],0)+([OK],0)+([Other],0)) is
the expression i entered and it says #ERROR. THe reason I am doing this is
because my boss wants all of the data to be moved into a database.... What
would you consider to be "normalized" data? Thanks

First, a caution...

If you have repeating fields (you appear to have multiple "state" fields) in
your table definition, you have ... a spreadsheet! You won't get the best
from Access' features and functions if you feed it 'sheet data. Is there a
reason you aren't just doing this in Excel?

Next, in Access, nulls propogate. As you've found out, having "nothing" in
at least one of those fields (spreadsheet columns!) means the sum of them
will be a nothing/null.

If you insist on keeping this decidedly un-normal data structure, you can
modify your formula/expression to use the Nz() function to convert nulls to
zeros (Nz([AR],0) + Nz([LA],0) + ...). Be aware, however, if you ever add
another state, you'll have to modify any/all formulas, your table structure,
any queries involving that that table, your forms based on that table, any
reports, any macros, any code ... -- what a maintenance headache!

Seriously consider looking into the topic of normalization -- Access is
designed to work with well-normalized data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with the fields AR, LA, OK, TX, MS, Other... I enter miles in
each field. Somedays I wont enter anything in the field. How do I add all

[quoted text clipped - 3 lines]

is
nothing entered in one field nothing shows in Total Daily Mileage...

 

[stephendeloach via AccessMonster.com]

This is what it looks like now...

Daily Log (Name of Table)

Date - Date/Time
Unit # - Text
Vin # - Text
Driver - Text
Ending Mileage - Number
Beginning Mileage - Number
Total Daily Mileage - Number
AR - Number
LA - Numb
TX - Numb
MS - Numb
OK - Numb
Other - Numb
Truck Type - Text

Do i need to redo before i get to far into this...?

First - I said "Update all fields that are now null."
Second - You tables
should look like this --
Trips ---
TripID - autonumber - primary key
TripDate - DateTime
Traveler - text - name os person traveling/driving

Travel --
TravelID - autonumber - primary key
TripID - number - long integer - foreign key related to Trips.TripID
State - text - state transversed
StartDate - DateTime
StartMile - number
EndDate - DateTime
EndMile - number
Etc -

First- When I change the default value to zero nothing shows (0) in the
fields?

[quoted text clipped - 34 lines]

 

[Jeff Boyce]

"Normalized" is a term describing a series of steps that condition data for
use in a relational database (e.g., Access). You can learn more about
normalizing via Access HELP and by searching on-line.

A more normal design for your data might be something like:

tblMileage
MileageID
State
Mileage
MileageDate

With a design like this, adding a new state doesn't require any changes
(you'd just add the new state to your tblState table). If you have a state
and a date without any miles, you DON'T have a record for that combination.
No Nulls, no worries!

To add up all the miles for a date range, a simple Totals query. For a
given state, a simple Totals query.

Changing the default value on a table's field doesn't go back and "fix"
existing records. It only applies to new records.

Why are you using Nz() on only the first three?

Regards

Jeff Boyce
Microsoft Office/Access MVP

First- When I change the default value to zero nothing shows (0) in the
fields?
Second- =(Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+([MS],0)+([OK],0)+([Other],0))
is
the expression i entered and it says #ERROR. THe reason I am doing this
is
because my boss wants all of the data to be moved into a database.... What
would you consider to be "normalized" data? Thanks

First, a caution...

If you have repeating fields (you appear to have multiple "state" fields)
in
your table definition, you have ... a spreadsheet! You won't get the best
from Access' features and functions if you feed it 'sheet data. Is there
a
reason you aren't just doing this in Excel?

Next, in Access, nulls propogate. As you've found out, having "nothing"
in
at least one of those fields (spreadsheet columns!) means the sum of them
will be a nothing/null.

If you insist on keeping this decidedly un-normal data structure, you can
modify your formula/expression to use the Nz() function to convert nulls
to
zeros (Nz([AR],0) + Nz([LA],0) + ...). Be aware, however, if you ever add
another state, you'll have to modify any/all formulas, your table
structure,
any queries involving that that table, your forms based on that table, any
reports, any macros, any code ... -- what a maintenance headache!

Seriously consider looking into the topic of normalization -- Access is
designed to work with well-normalized data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with the fields AR, LA, OK, TX, MS, Other... I enter miles
in
each field. Somedays I wont enter anything in the field. How do I add
all

[quoted text clipped - 3 lines]

is
nothing entered in one field nothing shows in Total Daily Mileage...

 

[stephendeloach via AccessMonster.com]

I figured that out while looking it over.. I have everything working like it
should be now. BUT is this the best way to do it?

"Normalized" is a term describing a series of steps that condition data for
use in a relational database (e.g., Access). You can learn more about
normalizing via Access HELP and by searching on-line.

A more normal design for your data might be something like:

tblMileage
MileageID
State
Mileage
MileageDate

With a design like this, adding a new state doesn't require any changes
(you'd just add the new state to your tblState table). If you have a state
and a date without any miles, you DON'T have a record for that combination.
No Nulls, no worries!

To add up all the miles for a date range, a simple Totals query. For a
given state, a simple Totals query.

Changing the default value on a table's field doesn't go back and "fix"
existing records. It only applies to new records.

Why are you using Nz() on only the first three?

Regards

Jeff Boyce
Microsoft Office/Access MVP

First- When I change the default value to zero nothing shows (0) in the
fields?

[quoted text clipped - 43 lines]

 

[Jeff Boyce]

Define "best". Tell me what criteria you are using to evaluate, and I'll
describe how I think this approach measures up.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I figured that out while looking it over.. I have everything working like
it
should be now. BUT is this the best way to do it?

"Normalized" is a term describing a series of steps that condition data
for
use in a relational database (e.g., Access). You can learn more about
normalizing via Access HELP and by searching on-line.

A more normal design for your data might be something like:

tblMileage
MileageID
State
Mileage
MileageDate

With a design like this, adding a new state doesn't require any changes
(you'd just add the new state to your tblState table). If you have a
state
and a date without any miles, you DON'T have a record for that
combination.
No Nulls, no worries!

To add up all the miles for a date range, a simple Totals query. For a
given state, a simple Totals query.

Changing the default value on a table's field doesn't go back and "fix"
existing records. It only applies to new records.

Why are you using Nz() on only the first three?

Regards

Jeff Boyce
Microsoft Office/Access MVP

First- When I change the default value to zero nothing shows (0) in the
fields?

[quoted text clipped - 43 lines]

is
nothing entered in one field nothing shows in Total Daily Mileage...