Calculated Field Issue

[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 the following expression, but it displays a message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?

 

[Guest]

Add a new unbound text box in the footer of whichever form contains your
calculated field and put;

=Sum([NameOfYourCalculatedField])

HTH

 

[Guest]

I tried that, but it asks for a parameter value.

Add a new unbound text box in the footer of whichever form contains your
calculated field and put;

=Sum([NameOfYourCalculatedField])

HTH

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 the following expression, but it displays a message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?

 

[Guest]

Post your complete query SQL.
--
KARL DEWEY
Build a little - Test a little

I tried that, but it asks for a parameter value.

Add a new unbound text box in the footer of whichever form contains your
calculated field and put;

=Sum([NameOfYourCalculatedField])

HTH

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 the following expression, but it displays a message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?

 

[Ken Snell \(MVP\)]

Try this:

=Sum([Labour]+([Quantity]*[Price Sold]))

 

[Guest]

This makes calculation problem. It calculates the labour for each part
separately.
ie. if labour is 50 and there are 3 parts (a,b and c) that cost 10 20 and
30 respectively, it performs the following operation:
(50+10)+(50+20)+(50+30), when it should be 50+(10+20+30). Is there a
function to divide the labour with the number of records that correspond to
each job.

Christos Evripidou
kourkoutas

Try this:

=Sum([Labour]+([Quantity]*[Price Sold]))

--

Ken Snell
<MS ACCESS MVP>

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 the following expression, but it displays a
message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?

 

[Ken Snell \(MVP\)]

You'll need to post the SQL statement of the report's RecordSource query so
that we can see the structure of the data.

--

Ken Snell
<MS ACCESS MVP>

This makes calculation problem. It calculates the labour for each part
separately.
ie. if labour is 50 and there are 3 parts (a,b and c) that cost 10 20 and
30 respectively, it performs the following operation:
(50+10)+(50+20)+(50+30), when it should be 50+(10+20+30). Is there a
function to divide the labour with the number of records that correspond
to
each job.

Christos Evripidou
kourkoutas

Try this:

=Sum([Labour]+([Quantity]*[Price Sold]))

--

Ken Snell
<MS ACCESS MVP>

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 the following expression, but it displays a
message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?

 

[Guest]

I didn't use a query, I used the report wizard and selected data from the
three tables.

Post your complete query SQL.
--
KARL DEWEY
Build a little - Test a little

I tried that, but it asks for a parameter value.

Add a new unbound text box in the footer of whichever form contains your
calculated field and put;

=Sum([NameOfYourCalculatedField])

HTH



:

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 the following expression, but it displays a message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?

 

[Guest]

If you rolled the labor up, in this case totaled at 50, then dividing it by 3
for the number of parts will give false information. Fan belt, oil, and oil
filter do not take identical labor times to install.
And again - tire, valve stem, and balance weights.
- Brake pads, turn disc, and brake fluid.
--
KARL DEWEY
Build a little - Test a little

This makes calculation problem. It calculates the labour for each part
separately.
ie. if labour is 50 and there are 3 parts (a,b and c) that cost 10 20 and
30 respectively, it performs the following operation:
(50+10)+(50+20)+(50+30), when it should be 50+(10+20+30). Is there a
function to divide the labour with the number of records that correspond to
each job.

Christos Evripidou
kourkoutas

Try this:

=Sum([Labour]+([Quantity]*[Price Sold]))

--

Ken Snell
<MS ACCESS MVP>

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 the following expression, but it displays a
message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?

 

[Guest]

This differs with the way the system works. With labor, I mean the cost for
the working hands for each job. Many jobs can consist of many parts
(different types of valves, screws, hoses...) and it is not convinient to
have labor for each part. So, I think that labor should be in the jobs table.
Specifically:
Job A: labor=$10
Brake pads 8 x $10
Brake fluid 2x $5
Job B: Labor=$20
Tyre 4 x $100
Rims 4 x $50
Screws 20 x $1
the total should be: (10+(8*10)+(2*5))+(20+(4*100)+(4*50)+(20*1))
instead, it calculates (10+8*10)+(10+2*5)) +
(20+4*100)+(20+4*50)+(20+20*1)
The problem is how to have the sum calculated as explained.

If you rolled the labor up, in this case totaled at 50, then dividing it by 3
for the number of parts will give false information. Fan belt, oil, and oil
filter do not take identical labor times to install.
And again - tire, valve stem, and balance weights.
- Brake pads, turn disc, and brake fluid.
--
KARL DEWEY
Build a little - Test a little

This makes calculation problem. It calculates the labour for each part
separately.
ie. if labour is 50 and there are 3 parts (a,b and c) that cost 10 20 and
30 respectively, it performs the following operation:
(50+10)+(50+20)+(50+30), when it should be 50+(10+20+30). Is there a
function to divide the labour with the number of records that correspond to
each job.

Christos Evripidou
kourkoutas

Try this:

=Sum([Labour]+([Quantity]*[Price Sold]))

--

Ken Snell
<MS ACCESS MVP>



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 the following expression, but it displays a
message
that it could not have an aggregate function:
=Sum([Labour]+Sum([Quantity]*[Price Sold]))
Any idea how to make this calculated field?