sum fields in a form

[Russ]

I have a database of golf socres.

field 1 is hole1, field 2 is hole2 etc.

the 10th field is total. how do i add fields 1 through 9 to calculate the
total score for a side?

 

[Douglas J. Steele]

Are you saying that you have 9 fields in a single row in your table, and you
want the score stored as a 10th field in that same row?

If you don't mind me saying so, your database design is flawed. Rather than
have it as 9 fields in a single row, the score for each hole should be a
separate row in a second table, linked to the first one. You'd score details
about when the game was played, whose score it is and so on in the first
table, and the second table would contain the primary key for the first
table, the hole number and the score for that hole.

Secondly, you should never store calculated fields. Rather, you should
create a query that calculates the total and use the query wherever you
would otherwise have used the table. With an arrangement like I described
above, you'd need a Totals query (View | Total when the query is in Design
view)

If you don't change your design, you should still be using a query, rather
than storing the total. In that case, though, your calculated field would
have to be something like Front9: Nz([Hole1], 0) + Nz([Hole2], 0) + ... +
Nz([Hole9], 0)

 

[Rick Brandt]

I have a database of golf socres.

field 1 is hole1, field 2 is hole2 etc.

the 10th field is total. how do i add fields 1 through 9 to
calculate the total score for a side?

You have a couple of design problems. Keeping your current design of a field
per hole (questionable), then you should not have a field for the total since
storing values that can be calculated from other values is a no-no.

Eliminate the field from your table. Then create a query based on your table
that includes all fields and then adds an additional calculated field for the
total using...

TotalScore: [Hole1] + [Hole2] + [Hole3] etc...

Then just use the query instead of the table for your forms/reports.

If you want to see the total even when all holes have not yet been entered
either have the hole fields default to zero with "Required = Yes" or wrap each
field name in the expression in the Nz() function thusly...

TotalScore: Nz([Hole1]) + Nz([Hole2]) + Nz([Hole3]) etc...

The reason for that is that any expression that includes a Null will result in a
Null for the entire expression (in some cases that is what you want).

A better table design might be...

PlayerName GameID HoleNumber Score
John 1 1 4
Bill 1 1 5
John 1 2 3
Bill 1 2 7

With a design like this you can easily aggregate all holes for one player or
multiple players. Also a lot easier to aggregate across multiple rounds played
and easier to handle rounds where the number of holes varies.

The "field per hole" does make data entry easier to set up, but it will make
many types of aggregations more complicated to perform.

 

[Russ]

Doug and Rick,

Thank you for the suggestions i will change the desing. But i am unusre of
how to add a calcuated field to a query. i thought you could only add a
field that was stored in a table. could you please describe this to me?
your help is greatly appreciated.

russ

Are you saying that you have 9 fields in a single row in your table, and you
want the score stored as a 10th field in that same row?

If you don't mind me saying so, your database design is flawed. Rather than
have it as 9 fields in a single row, the score for each hole should be a
separate row in a second table, linked to the first one. You'd score details
about when the game was played, whose score it is and so on in the first
table, and the second table would contain the primary key for the first
table, the hole number and the score for that hole.

Secondly, you should never store calculated fields. Rather, you should
create a query that calculates the total and use the query wherever you
would otherwise have used the table. With an arrangement like I described
above, you'd need a Totals query (View | Total when the query is in Design
view)

If you don't change your design, you should still be using a query, rather
than storing the total. In that case, though, your calculated field would
have to be something like Front9: Nz([Hole1], 0) + Nz([Hole2], 0) + ... +
Nz([Hole9], 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a database of golf socres.

field 1 is hole1, field 2 is hole2 etc.

the 10th field is total. how do i add fields 1 through 9 to calculate the
total score for a side?

 

[Russ]

The formula worked, thank you. But now that i have implemented the design
suggested I have a few questions. Since the score for each of the 9 holes is
in a different form, how do i enter the data? i do not want to open up nine
different forms just to record one round. i tried to create a form that put
all nine holes in one form even though the data is in different tables, but
this only allowed me to view the data entered on the table, not enter new
data. how do i arrange it so i can enter a new round using that form with
all nine holes in it? also, can i put the field on the query that calculates
teh cummulative score in teh form? thanks for you helpl

russ

Doug and Rick,

Thank you for the suggestions i will change the desing. But i am
unusre of how to add a calcuated field to a query. i thought you
could only add a field that was stored in a table. could you please
describe this to me? your help is greatly appreciated.

russ

In the query after you get the fields from the table in you create a new
calculated "field" in the query by picking the first empty column (on the
right) and then type in the formula Douglas gave you.

"Front9: Nz([Hole1], 0) + Nz([Hole2], 0) + ... + Nz([Hole9], 0)"

Note the Colon after the name of the new "field" is required as it
identifies everything before it as the name. His formula is well thought
out and takes care of the zero problem nicely.

Are you saying that you have 9 fields in a single row in your table,
and you want the score stored as a 10th field in that same row?

If you don't mind me saying so, your database design is flawed.
Rather than have it as 9 fields in a single row, the score for each
hole should be a separate row in a second table, linked to the first
one. You'd score details about when the game was played, whose score
it is and so on in the first table, and the second table would
contain the primary key for the first table, the hole number and the
score for that hole.

Secondly, you should never store calculated fields. Rather, you
should create a query that calculates the total and use the query
wherever you would otherwise have used the table. With an
arrangement like I described above, you'd need a Totals query (View
| Total when the query is in Design view)

If you don't change your design, you should still be using a query,
rather than storing the total. In that case, though, your calculated
field would have to be something like Front9: Nz([Hole1], 0) +
Nz([Hole2], 0) + ... + Nz([Hole9], 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a database of golf socres.

field 1 is hole1, field 2 is hole2 etc.

the 10th field is total. how do i add fields 1 through 9 to
calculate the total score for a side?