Sum Total from Calc Fields

[Joshua McLemore]

Hello,

I know the initial answer to "how do you sum a total from fields on a form"
is use the fields equation to get a sum total.

But what about when you have 33 fields that you need to sum and so the
expression is too long? For example:

Me.Audio_Sum_Cost = SUM([Forms]![frmEvent_Part1_Details]![Audio Cost 1] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 2] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 3] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 4])

The [Audio Cost 1] ... fields are all calculated fields as well. So I need
to add up 33 of those bad boys for a grand total in the footer of my form.

Thanks for help in advance.

 

[Allen Browne]

To do this correctly, Joshua, you need to set up your tables differently.
You need many related *records*, instead of many repeating fields.

Your main form is probably bound to a table that contains the details for an
event? It seems this table has 33 fields to handle all the possible audio
costs. This table needs to be broken down into 2 tables, like this:

tblEvent (one record for each event you look after):
EventID AutoNumber primary key
EventName Text what this booking is.
EventDate Date/Time when this booking is

tblEventCost (a record for every cost in every event):
EventCostID AutoNum primary key
EventID Number which event this cost record is for
CostDescrip Text what this cost is for
Amount Currency how much

With this structure, you can have as many (or as few) costs associated with
an event as you need. And since they are all in the one column, it's dead
easy to sum them.

If your existing 33 columns are actually for different types of cost, then
create another table with a record for each one:
tblCostType:
CostTypeID Text primary key
Now in tblEventCost, replace the CostDescrip field with a CostTypeID field.
This lets to correctly track and summarize the different costs.

You can even use a crosstab query to produce a matrix like the table you
currently have.

This idea (one-to-many relationships) is the core concept to grasp for
relational database. Everything depends on it, so it is absolutely crucial
to get this. Here's a couple more examples of setting up relational
structures:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Joshua McLemore]

Thanks for your reply, Allen.

Without getting into many details, I cannot redesign the table structures
for this. The fields [Audio Cost 1] are simply calculated for FYI on the
form. The only value I need to store in the table is the grand total.

So ... [Audio Cost 1] is calculated with:

=[Audio 1 Rate]*(DateDiff("h",[Audio 1 Start Time Text],[Audio 1 End Time
Text]))

And so I need to be able to sum that exp 33 times which is just too long of
a string.

So is there anyway that I could still do this?

Thanks again.

--
Joshua

To do this correctly, Joshua, you need to set up your tables differently.
You need many related *records*, instead of many repeating fields.

Your main form is probably bound to a table that contains the details for an
event? It seems this table has 33 fields to handle all the possible audio
costs. This table needs to be broken down into 2 tables, like this:

tblEvent (one record for each event you look after):
EventID AutoNumber primary key
EventName Text what this booking is.
EventDate Date/Time when this booking is

tblEventCost (a record for every cost in every event):
EventCostID AutoNum primary key
EventID Number which event this cost record is for
CostDescrip Text what this cost is for
Amount Currency how much

With this structure, you can have as many (or as few) costs associated with
an event as you need. And since they are all in the one column, it's dead
easy to sum them.

If your existing 33 columns are actually for different types of cost, then
create another table with a record for each one:
tblCostType:
CostTypeID Text primary key
Now in tblEventCost, replace the CostDescrip field with a CostTypeID field.
This lets to correctly track and summarize the different costs.

You can even use a crosstab query to produce a matrix like the table you
currently have.

This idea (one-to-many relationships) is the core concept to grasp for
relational database. Everything depends on it, so it is absolutely crucial
to get this. Here's a couple more examples of setting up relational
structures:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hello,

I know the initial answer to "how do you sum a total from fields on a
form"
is use the fields equation to get a sum total.

But what about when you have 33 fields that you need to sum and so the
expression is too long? For example:

Me.Audio_Sum_Cost = SUM([Forms]![frmEvent_Part1_Details]![Audio Cost 1] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 2] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 3] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 4])

The [Audio Cost 1] ... fields are all calculated fields as well. So I need
to add up 33 of those bad boys for a grand total in the footer of my form.

Thanks for help in advance.

 

[Allen Browne]

Perhaps you could put 33 text boxes in the Form Footer section.

Control Source like this:
=Sum([Audio 1 Rate] * (DateDiff("h", [Audio 1 Start Time Text],
[Audio 1 End Time Text]))
Name it (say) A1.
The next one will be A2, and so on.

Then in the 34th box in the form footer:
=[A1]+[A2]+...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your reply, Allen.

Without getting into many details, I cannot redesign the table structures
for this. The fields [Audio Cost 1] are simply calculated for FYI on the
form. The only value I need to store in the table is the grand total.

So ... [Audio Cost 1] is calculated with:

=[Audio 1 Rate]*(DateDiff("h",[Audio 1 Start Time Text],[Audio 1 End Time
Text]))

And so I need to be able to sum that exp 33 times which is just too long
of
a string.

So is there anyway that I could still do this?

Thanks again.

--
Joshua

To do this correctly, Joshua, you need to set up your tables differently.
You need many related *records*, instead of many repeating fields.

Your main form is probably bound to a table that contains the details for
an
event? It seems this table has 33 fields to handle all the possible audio
costs. This table needs to be broken down into 2 tables, like this:

tblEvent (one record for each event you look after):
EventID AutoNumber primary key
EventName Text what this booking is.
EventDate Date/Time when this booking is

tblEventCost (a record for every cost in every event):
EventCostID AutoNum primary key
EventID Number which event this cost record is
for
CostDescrip Text what this cost is for
Amount Currency how much

With this structure, you can have as many (or as few) costs associated
with
an event as you need. And since they are all in the one column, it's dead
easy to sum them.

If your existing 33 columns are actually for different types of cost,
then
create another table with a record for each one:
tblCostType:
CostTypeID Text primary key
Now in tblEventCost, replace the CostDescrip field with a CostTypeID
field.
This lets to correctly track and summarize the different costs.

You can even use a crosstab query to produce a matrix like the table you
currently have.

This idea (one-to-many relationships) is the core concept to grasp for
relational database. Everything depends on it, so it is absolutely
crucial
to get this. Here's a couple more examples of setting up relational
structures:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html

message

Hello,

I know the initial answer to "how do you sum a total from fields on a
form"
is use the fields equation to get a sum total.

But what about when you have 33 fields that you need to sum and so the
expression is too long? For example:

Me.Audio_Sum_Cost = SUM([Forms]![frmEvent_Part1_Details]![Audio Cost 1]
+
[Forms]![frmEvent_Part1_Details]![Audio Cost 2] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 3] +
[Forms]![frmEvent_Part1_Details]![Audio Cost 4])

The [Audio Cost 1] ... fields are all calculated fields as well. So I
need
to add up 33 of those bad boys for a grand total in the footer of my
form.