Calculating the Sum IF fields equal criteria

[serviceenvoy]

I have expense fields classified as "parts" or "labor". I need to
create a formula to look at ExpenseType fields 1-10, determine if they
are labor, and then calculate the Total fields sum of the labor field
expenses into a field called TotalLaborCost. Then I need to do the
same process to calculate the sum of expenses classified as Parts in
the TotalPartsCost field.

Fields in the form:
"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
"Quantity1", "Quantity2"..... (how many of each item or how many
hours)
"Cost1" ....(what is the per unit cost)
"Total1"....(the extended price)
"TotalLaborCost" (for the sum of all the labor "Totalx" fields
"TotalPartsCost" (for the sum of all the parts "Totalx" fields

 

[Allen Browne]

You will need to redesign this table to get the results you want.

Whenever you have repeating fields, such as:
ExpsnseType1, ExpenseType2, Cost1, Cost2, ...
it always means you need a related table where you can have many records
instead of using many fields in this one table.

One expense can have several line items (parts or labor) in it. There is
therefore a one-to-many relation between the expense and the line items in
it. So, you need to build this as 2 tables, like this:

Expense table:
ExpenseID AutoNumber primary key
ExpenseDate Date/Time when this expense was incurred.
...

ExpenseDetail table:
ExpenseDetailID AutoNumber primary key
ExpenseID Number Which expense record is line item is
for.
ExpenseTypeID Labor or Parts.
Quantity Number how many units
UnitCost Currency how much each.

If that doesn't make sense, open the Northwind sample database that installs
with Access. Open the Relationships window (Tools menu), and study the
Orders and OrderDetails table. You will see that the approach is similar to
what you need.

Ultimately you will end up with a form bound to the Expense table, with a
subform bound to the ExpenseDetail table (similar to the Orders form in
Northwind.) You will create a query to use as the source for the subform.

In the query, type this expression into the Field row:
LaborCosts: CCur(IIf([ExpenseTypeID] = 'Labor', Nz([Quantity] *
[UnitCost],0), 0))
In the next column, type this expression into the Field row:
PartsCost: CCur(IIf([ExpenseTypeID = 'Labor', 0, Nz([Quantity] *
[UnitCost],0)))

Now in your form, you will be able to sum these fields to display the
totals. (Note: It is really important that you do not store these totals in
either table.)

You now have a normalized data structure that avoids 2 of the big mistakes
newbies make, i.e.:
- repeating fields, and
- storing dependent data.
If you want to read more about this, the key word is normalization. Here's a
starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Marshall Barton]

I have expense fields classified as "parts" or "labor". I need to
create a formula to look at ExpenseType fields 1-10, determine if they
are labor, and then calculate the Total fields sum of the labor field
expenses into a field called TotalLaborCost. Then I need to do the
same process to calculate the sum of expenses classified as Parts in
the TotalPartsCost field.

Fields in the form:
"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
"Quantity1", "Quantity2"..... (how many of each item or how many
hours)
"Cost1" ....(what is the per unit cost)
"Total1"....(the extended price)
"TotalLaborCost" (for the sum of all the labor "Totalx" fields
"TotalPartsCost" (for the sum of all the parts "Totalx" fields

Sorry, but spreadsheet calculations like that are extremely
complex in a database. You really need to understand how to
design tables using the rules of Normalization (Google it).
One fundamental rule is that a table should not have
repeating fields like Quantity1, Quantity2, ...

Barring you ability to do that, you might have some hope of
getting a reasonable result by creating a subform based on a
query that calculates the desired totals, but it won't be
easy.

 

[serviceenvoy]

Sorry, but spreadsheet calculations like that are extremely
complex in a database. You really need to understand how to
design tables using the rules of Normalization (Google it).
One fundamental rule is that a table should not have
repeating fields like Quantity1, Quantity2, ...

Barring you ability to do that, you might have some hope of
getting a reasonable result by creating a subform based on a
query that calculates the desired totals, but it won't be
easy.

I am a novice so I guess I don't understand some of the dangers of
doing things the way I'm attempting. I need to track expenses of each
record so I don't understand why I can't store the total expenses in
that record. I'm also not sure how to setup a subform as you both
have suggested. Please explain to a beginner.

 

[Marshall Barton]

I am a novice so I guess I don't understand some of the dangers of
doing things the way I'm attempting. I need to track expenses of each
record so I don't understand why I can't store the total expenses in
that record. I'm also not sure how to setup a subform as you both
have suggested.

Another rule of relational databases is to never store data
that can be recalculated from other values. This kind of
total should be calculated in a query so it is is guaranteed
to be correct whenever the query runs.

Using a subform on this kind of table structure would just
be a hack at best and, as I said before, it will not be
easy. Very few people have extensive experience doing things
the wrong way so detailed help is not readily available.

Allen provided a nice explanation of how you might
profitably start a normalized design of your data. I am
sure your time, energy and learning curve would be much
better served by pursuing a better table structure than
figuring out a messy workaround that will do nothing to help
deal with the innumerable other problems your current
structure will cause.

 

[serviceenvoy]

Another rule of relational databases is to never store data
that can be recalculated from other values. This kind of
total should be calculated in a query so it is is guaranteed
to be correct whenever the query runs.

Using a subform on this kind of table structure would just
be a hack at best and, as I said before, it will not be
easy. Very few people have extensive experience doing things
the wrong way so detailed help is not readily available.

Allen provided a nice explanation of how you might
profitably start a normalized design of your data. I am
sure your time, energy and learning curve would be much
better served by pursuing a better table structure than
figuring out a messy workaround that will do nothing to help
deal with the innumerable other problems your current
structure will cause.

Well, now I am really confused. You recommended what Allen said but
then told me I shouldn't use a subform. Allens subform seemed like
overkill for what I want. Let's start from scratch. I'll explain
what I want to accomplish and you tell me the best way and how to
create it.

We have individual records for each service call we run. Each record
has multiple expenses broken down into labor or parts. There might be
3 different labor expenses and may be 5 different parts expenses and
each one must be listed seperately to ensure we reimburse various
involved parties. Since we reimburse others for parts & labor we have
no inventory and the price for the same part/labor varies every time
we pay for it. Because this varies so much I don't know that using a
subform is feasible. I need to be able to determine the total amount
of parts and the total amount of labor spent on every record. I
already have a field that calculates the extended price of each
expense line (lines 1-10)(all related fields are progressively called
QTY1, COST1, TOTAL1, then 2, 3, etc.). I just need to have a query or
something that looks at all the expense fields, and adds the extended
price for all the expenses marked parts and show me the total in a
field called TotalPartsCost. Then the same for all the labor
expenses. I am a novice so please help me understand the logic behind
your solution as well as the solution itself. I'm open to doing it
the best way for the long term health of my database assuming I
understand what the heck you're telling me.

 

[Marshall Barton]

Well, now I am really confused. You recommended what Allen said but
then told me I shouldn't use a subform. Allens subform seemed like
overkill for what I want. Let's start from scratch. I'll explain
what I want to accomplish and you tell me the best way and how to
create it.

We have individual records for each service call we run. Each record
has multiple expenses broken down into labor or parts. There might be
3 different labor expenses and may be 5 different parts expenses and
each one must be listed seperately to ensure we reimburse various
involved parties. Since we reimburse others for parts & labor we have
no inventory and the price for the same part/labor varies every time
we pay for it. Because this varies so much I don't know that using a
subform is feasible. I need to be able to determine the total amount
of parts and the total amount of labor spent on every record. I
already have a field that calculates the extended price of each
expense line (lines 1-10)(all related fields are progressively called
QTY1, COST1, TOTAL1, then 2, 3, etc.). I just need to have a query or
something that looks at all the expense fields, and adds the extended
price for all the expenses marked parts and show me the total in a
field called TotalPartsCost. Then the same for all the labor
expenses. I am a novice so please help me understand the logic behind
your solution as well as the solution itself. I'm open to doing it
the best way for the long term health of my database assuming I
understand what the heck you're telling me.

I guess my comments about using a subform with your current
table structure got confused with Allen's idea of using a
Normalized table structure and a subform. The core issus is
not the use of a subform, but the proper structure of your
data tables. If you don't get rid of the multiple cost and
total fields in a single table, you will have a terrible
time making a useful application.

If you follow Allen's guidlines about using a separate table
for each separate "entity" (ExpenseType, ExpenseDetail,
etc), then the use of subforms is a natural consequence that
parallels the tables.

It would be benificial for you to take some time out to do
some homework re Normalization. You can get a brief
overview at http://support.microsoft.com/kb/283878/en-us
For more extensive discussions use Google and concentrate on
just the first three normal forms of relational databases.
I just searched for: Relational Normal Forms
and quickly found this nice article (of more than a million)
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html