Calculated Field

[Guest]

I have three tables, Entries, Jobs and Parts. These three tables are related
with one-to-many relationships, from left to right.
i.e. one entry - many jobs
one job - many parts

Among the other fields, in the parts table there is quantity and price, and
in the jobs table labour. Additionally, I created a 3-level report where I
calculated, using a calculated field, the total of each job using the
expression:
=[Labour]+Sum([Quantity]*[Price Sold])

Now, I want to create another calculated field to sum up all the job totals
for each entry. I tried using the expression
=Sum([Labour])+Sum([Quantity]*[Price Sold]), but the outcome is wrong.
Specifically, there is a problem with the first part of the expression
(sum([labour]). Instead of summing up all the labour fields that exist in the
jobs table, it acts as if labour is a field of the parts table instead.
ie.
If I have 2 jobs with labours 20 and 10 and each job has two and three parts
respectively, the calculation performed with =sum([labour]) expression is:
(20+20)+(10+10+10) = 70 instead of 20+10=30
Any idea how to make the calculation I want (count each labour once and add
it to the total cost of the parts)?

 

[Allen Browne]

I think you have created a query that uses all 3 tables, as the source for
your report?

If so, when you view the query itself, you will see that the Labour field
from the jobs tables is repeated on every row for the parts. So if there are
4 parts in the job, when you sum the Labour field, it ends up being 4 times
the correct labour total.

There are several ways to avoid this. If you just want the totals by job in
your query, you can use First() instead of Sum() in the query. This will
yeild the Labour value from the first row for the job instead of all 4 rows
(assuming you are grouping by the JobID), hence giving the right total.

If you want to do it in the report, presumably you have used the Sorting And
Grouping box (View menu in report design) to create a Group Header and a
Group Footer on the Job ID. You show the parts in the Detail section. Then
in the JobID group footer you show the total parts as:
=Sum([Quantity]*[Price Sold])
Add another text box to the JobID group footer, and set its properties like
this:
Control Source =Labour
Running Sum Over All
Visible No
Name txtLabourRS

That causes Access to maintain a running total of the labour in the report.
You can now place another text box in the Report Footer section, and set its
Control Source to:
=[txtLabourRS] + Sum([Quantity]*[Price Sold])

Another completely different solution is to put the labour costs into the
final table as well, so you have the Jobs table as well as a JobDetail table
(line items in the job.) The JobDetail table has fields such as:
JobDetailID autoNumber
JobID foreign key to Jobs.JobID
PartID foreign key to Parts.PartID. Blank for labour
EmployeeID foreign key to Employee.EmployeeID. Blank for parts.
Quantity how many parts, or how many hours
PriceEach how much per part or per hour

This structure is more flexible:
- You track who worked on what.
- Copes with penalty rates (e.g. 6 hours @ normal rates, plus 1.5 hrs @
overtime)
- Copes with staff at different rates (e.g. foreman)
- Gives you productive hours per employee in a period.

It's also easier to get the totals, since all costs are in the one table.

And it's easy to interface: typically a main form for the job, and a tab
control with 2 pages for the parts and labour.

HTH.

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

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

I have three tables, Entries, Jobs and Parts. These three tables are
related
with one-to-many relationships, from left to right.
i.e. one entry - many jobs
one job - many parts

Among the other fields, in the parts table there is quantity and price,
and
in the jobs table labour. Additionally, I created a 3-level report where I
calculated, using a calculated field, the total of each job using the
expression:
=[Labour]+Sum([Quantity]*[Price Sold])

Now, I want to create another calculated field to sum up all the job
totals
for each entry. I tried using the expression
=Sum([Labour])+Sum([Quantity]*[Price Sold]), but the outcome is wrong.
Specifically, there is a problem with the first part of the expression
(sum([labour]). Instead of summing up all the labour fields that exist in
the
jobs table, it acts as if labour is a field of the parts table instead.
ie.
If I have 2 jobs with labours 20 and 10 and each job has two and three
parts
respectively, the calculation performed with =sum([labour]) expression is:
(20+20)+(10+10+10) = 70 instead of 20+10=30
Any idea how to make the calculation I want (count each labour once and
add
it to the total cost of the parts)?

 

[Guest]

That was very helpful, however in an entry there are several jobs. With the
method you proposed, it adds only the labor for the first job, ignoring all
the others. What I want is to sum up all the labors that correspond to the
specific entry.

I think you have created a query that uses all 3 tables, as the source for
your report?

If so, when you view the query itself, you will see that the Labour field
from the jobs tables is repeated on every row for the parts. So if there are
4 parts in the job, when you sum the Labour field, it ends up being 4 times
the correct labour total.

There are several ways to avoid this. If you just want the totals by job in
your query, you can use First() instead of Sum() in the query. This will
yeild the Labour value from the first row for the job instead of all 4 rows
(assuming you are grouping by the JobID), hence giving the right total.

If you want to do it in the report, presumably you have used the Sorting And
Grouping box (View menu in report design) to create a Group Header and a
Group Footer on the Job ID. You show the parts in the Detail section. Then
in the JobID group footer you show the total parts as:
=Sum([Quantity]*[Price Sold])
Add another text box to the JobID group footer, and set its properties like
this:
Control Source =Labour
Running Sum Over All
Visible No
Name txtLabourRS

That causes Access to maintain a running total of the labour in the report.
You can now place another text box in the Report Footer section, and set its
Control Source to:
=[txtLabourRS] + Sum([Quantity]*[Price Sold])

Another completely different solution is to put the labour costs into the
final table as well, so you have the Jobs table as well as a JobDetail table
(line items in the job.) The JobDetail table has fields such as:
JobDetailID autoNumber
JobID foreign key to Jobs.JobID
PartID foreign key to Parts.PartID. Blank for labour
EmployeeID foreign key to Employee.EmployeeID. Blank for parts.
Quantity how many parts, or how many hours
PriceEach how much per part or per hour

This structure is more flexible:
- You track who worked on what.
- Copes with penalty rates (e.g. 6 hours @ normal rates, plus 1.5 hrs @
overtime)
- Copes with staff at different rates (e.g. foreman)
- Gives you productive hours per employee in a period.

It's also easier to get the totals, since all costs are in the one table.

And it's easy to interface: typically a main form for the job, and a tab
control with 2 pages for the parts and labour.

HTH.

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

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

I have three tables, Entries, Jobs and Parts. These three tables are
related
with one-to-many relationships, from left to right.
i.e. one entry - many jobs
one job - many parts

Among the other fields, in the parts table there is quantity and price,
and
in the jobs table labour. Additionally, I created a 3-level report where I
calculated, using a calculated field, the total of each job using the
expression:
=[Labour]+Sum([Quantity]*[Price Sold])

Now, I want to create another calculated field to sum up all the job
totals
for each entry. I tried using the expression
=Sum([Labour])+Sum([Quantity]*[Price Sold]), but the outcome is wrong.
Specifically, there is a problem with the first part of the expression
(sum([labour]). Instead of summing up all the labour fields that exist in
the
jobs table, it acts as if labour is a field of the parts table instead.
ie.
If I have 2 jobs with labours 20 and 10 and each job has two and three
parts
respectively, the calculation performed with =sum([labour]) expression is:
(20+20)+(10+10+10) = 70 instead of 20+10=30
Any idea how to make the calculation I want (count each labour once and
add
it to the total cost of the parts)?

 

[Guest]

sorry, my mistake!
Problem's solved! Thank you very much!!!

I think you have created a query that uses all 3 tables, as the source for
your report?

If so, when you view the query itself, you will see that the Labour field
from the jobs tables is repeated on every row for the parts. So if there are
4 parts in the job, when you sum the Labour field, it ends up being 4 times
the correct labour total.

There are several ways to avoid this. If you just want the totals by job in
your query, you can use First() instead of Sum() in the query. This will
yeild the Labour value from the first row for the job instead of all 4 rows
(assuming you are grouping by the JobID), hence giving the right total.

If you want to do it in the report, presumably you have used the Sorting And
Grouping box (View menu in report design) to create a Group Header and a
Group Footer on the Job ID. You show the parts in the Detail section. Then
in the JobID group footer you show the total parts as:
=Sum([Quantity]*[Price Sold])
Add another text box to the JobID group footer, and set its properties like
this:
Control Source =Labour
Running Sum Over All
Visible No
Name txtLabourRS

That causes Access to maintain a running total of the labour in the report.
You can now place another text box in the Report Footer section, and set its
Control Source to:
=[txtLabourRS] + Sum([Quantity]*[Price Sold])

Another completely different solution is to put the labour costs into the
final table as well, so you have the Jobs table as well as a JobDetail table
(line items in the job.) The JobDetail table has fields such as:
JobDetailID autoNumber
JobID foreign key to Jobs.JobID
PartID foreign key to Parts.PartID. Blank for labour
EmployeeID foreign key to Employee.EmployeeID. Blank for parts.
Quantity how many parts, or how many hours
PriceEach how much per part or per hour

This structure is more flexible:
- You track who worked on what.
- Copes with penalty rates (e.g. 6 hours @ normal rates, plus 1.5 hrs @
overtime)
- Copes with staff at different rates (e.g. foreman)
- Gives you productive hours per employee in a period.

It's also easier to get the totals, since all costs are in the one table.

And it's easy to interface: typically a main form for the job, and a tab
control with 2 pages for the parts and labour.

HTH.

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

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

I have three tables, Entries, Jobs and Parts. These three tables are
related
with one-to-many relationships, from left to right.
i.e. one entry - many jobs
one job - many parts

Among the other fields, in the parts table there is quantity and price,
and
in the jobs table labour. Additionally, I created a 3-level report where I
calculated, using a calculated field, the total of each job using the
expression:
=[Labour]+Sum([Quantity]*[Price Sold])

Now, I want to create another calculated field to sum up all the job
totals
for each entry. I tried using the expression
=Sum([Labour])+Sum([Quantity]*[Price Sold]), but the outcome is wrong.
Specifically, there is a problem with the first part of the expression
(sum([labour]). Instead of summing up all the labour fields that exist in
the
jobs table, it acts as if labour is a field of the parts table instead.
ie.
If I have 2 jobs with labours 20 and 10 and each job has two and three
parts
respectively, the calculation performed with =sum([labour]) expression is:
(20+20)+(10+10+10) = 70 instead of 20+10=30
Any idea how to make the calculation I want (count each labour once and
add
it to the total cost of the parts)?