Aggregate function error

[Shannan Casteel via AccessMonster.com]

I have an invoice form with Parts and Labor as subforms. The parts section
is based on a query that pulls data from 2 tables (tblParts & tblPartPerClaim)
.. It has fields: ClaimID: tblPartPerClaim; NET PRICE: tblParts; Quantity:
tblPartPerClaim; and a calculated field Part Total: ([NET PRICE]*[Quantity]).
On my subform, I have a text box in the footer that displays the sum of the
Part Total field (=Sum([NET PRICE]*[Quantity])). This text box is called
PartsClaimed.

I need to be able to store this value. I tried using a query that has two
fields. ClaimID: tblPartPerClaim & Parts Claimed: Sum(tblParts.[NET PRICE]
*tblPartPerClaim.{Quantity}). The ClaimID is set to Group By. The Parts
Claimed field is set to expression. I also tried setting it to sum, but with
no change in the result.

Either it will tell me something wasn't included in an aggregate function or
it comes up asking me for the NET PRICE. I'm not any good with SQL, so
please just show me how to structure the query with the appropriate formulas.

Thanks for your help,

Shannan
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

Nevermind I finally got. I figured out you have to have the tables showing
in the upper part of the window with their relationships.

Shannan
Shannan Casteel wrote:

I have an invoice form with Parts and Labor as subforms. The parts section
is based on a query that pulls data from 2 tables (tblParts & tblPartPerClaim)
. It has fields: ClaimID: tblPartPerClaim; NET PRICE: tblParts; Quantity:
tblPartPerClaim; and a calculated field Part Total: ([NET PRICE]*[Quantity]).

On my subform, I have a text box in the footer that displays the sum of the
Part Total field (=Sum([NET PRICE]*[Quantity])). This text box is called
PartsClaimed.

I need to be able to store this value. I tried using a query that has two
fields. ClaimID: tblPartPerClaim & Parts Claimed: Sum(tblParts.[NET PRICE]
*tblPartPerClaim.{Quantity}). The ClaimID is set to Group By. The Parts
Claimed field is set to expression. I also tried setting it to sum, but with
no change in the result.

Either it will tell me something wasn't included in an aggregate function or
it comes up asking me for the NET PRICE. I'm not any good with SQL, so
please just show me how to structure the query with the appropriate formulas.

Thanks for your help,

Shannan

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

Now I have the same problem with the Labor Amount.

There is a difference. The labor amount is based on a Labor Rate entered on
the main form multiplied by the Total Hours. Total Hours is a calculated
field on the JobCodes subform. It sums the hours for each job in the list.
(Sum([Hours])) Labor Rate is entered by the user on the main form. I tried
this with the Labor Claimed field in the query I was trying to create....
Labor Claimed: ((Sum([Hours])*[LaborRate]) with total set to expression.
It tells me I have not included the formula in an aggregate function.

What's wrong?

Shannan

Shannan Casteel wrote:

Nevermind I finally got. I figured out you have to have the tables showing
in the upper part of the window with their relationships.

Shannan

I have an invoice form with Parts and Labor as subforms. The parts section
is based on a query that pulls data from 2 tables (tblParts & tblPartPerClaim)

[quoted text clipped - 18 lines]

Shannan

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1