How do I sum up two columns in a subform?

G

Guest

Hi. I have a subform with a product, unitPrice, costPrice, and quantity field
in it. Also I have two columns LineTotal and CostLineTotal where I have
price*quantity. When I try Sum([LineTotal]) and Sum([CostLineTotal]) together
in the form footer, errors occur. Both work fine when I cut the other one
out. Where am i going wrong?

Thanks in advance,
JohnnyF
 
A

Al Campagna

Johnny,
You can't use aggregate functions against "unbound" calculated fields.
Add 2 calculated columns to the query behind the subform.
LineTotal : [UnitPrice] * [Qty]
and
CostLineTotal : [CostPrice] * [Qty]
With those 2 "bound" fields on the form, Sum(LineTotal), and Sum(CostLineTotal) should
work.
 
G

Guest

Hi again,
Thanks for your help. That works fine for LineTotal but doesn't work for
CostLineTotal. The subform looks like this:

ProductName UnitPrice CostPrice Quantity LineTotal CostLineTotal

ProductName is a combo-box that takes its values from a products table. When
I select a Product the UnitPrice comes up in its field but CostPrice doesn't.
It can be typed manually but I don't want to do it like this. The query then
says something about ambiguous joins for CostLineTotal. I presume this is
because it's not reading the original CostPrice from the Products table but I
seem to be stuck. Any ideas?

Thanks again,
Johnny

Al Campagna said:
Johnny,
You can't use aggregate functions against "unbound" calculated fields.
Add 2 calculated columns to the query behind the subform.
LineTotal : [UnitPrice] * [Qty]
and
CostLineTotal : [CostPrice] * [Qty]
With those 2 "bound" fields on the form, Sum(LineTotal), and Sum(CostLineTotal) should
work.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

JohnnyF said:
Hi. I have a subform with a product, unitPrice, costPrice, and quantity field
in it. Also I have two columns LineTotal and CostLineTotal where I have
price*quantity. When I try Sum([LineTotal]) and Sum([CostLineTotal]) together
in the form footer, errors occur. Both work fine when I cut the other one
out. Where am i going wrong?

Thanks in advance,
JohnnyF
 
A

Al Campagna

Johnny,
Well that's really a different problem. You wrote...
When I try Sum([LineTotal]) and Sum([CostLineTotal]) together
in the form footer, errors occur. Both work fine when I cut the other one
Now you're saying you never did get the Cost value enetered on each record?

Also, you should name the individual Line Totals as PriceSubTotal and CostSubTotal, so
they don't get confused with the footer calculations PriceTotal and CostTotal.

Your Product table should contain both Price and Cost fields. Sounds like you're
relating a seperate Cost table to a Product/Price table.
Each Product should have a unique key value associated with it, so that that value
identifies only one Product
ProdID(key) ProdName Price Cost
14251 Ratchet 1.00 .35
761321 Pawl 1.25 .62 etc..

Your combo (ex. cboProdID) should consist of 4 columns laid out just like the
fieldnames above. It should be bound to your ProdID field in your table.
Combo Column widths (ex.) should be 0"; 1.5"; .75"; .75". (That hides the ProdID and
allows the user to select by ProdName)
When the user selects a ProdName, the ProdID is actually stored in the ProdID field,
but the combo will "display" the ProdName.
OK, we've got our ProdID stored, now... using the AfterUpdate event of the combo...
Price = cboProdID.Column(2)
Cost = cboProdID.Column(3)
The two "line" calculations should work now, and you should be able to add them in the
footer.

Actually, you should name the individual line calculations as PriceSubTotal and
CostSubTotal, so they don't get confused with the footer calculations PriceTotal and
CostTotal. Never name a calculation with a field name involved in that calculation.

ProdName(combo) Price Cost Qty PriceLineSubtotal QtyLineSubtotal
Ratchet 1.00 .35 10 10.00 3.50
Pawl 1.25 .62 4 5.00
2.48
ProdTotal = 15.00 CostTotal 5.98
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

JohnnyF said:
Hi again,
Thanks for your help. That works fine for LineTotal but doesn't work for
CostLineTotal. The subform looks like this:

ProductName UnitPrice CostPrice Quantity LineTotal CostLineTotal

ProductName is a combo-box that takes its values from a products table. When
I select a Product the UnitPrice comes up in its field but CostPrice doesn't.
It can be typed manually but I don't want to do it like this. The query then
says something about ambiguous joins for CostLineTotal. I presume this is
because it's not reading the original CostPrice from the Products table but I
seem to be stuck. Any ideas?

Thanks again,
Johnny

Al Campagna said:
Johnny,
You can't use aggregate functions against "unbound" calculated fields.
Add 2 calculated columns to the query behind the subform.
LineTotal : [UnitPrice] * [Qty]
and
CostLineTotal : [CostPrice] * [Qty]
With those 2 "bound" fields on the form, Sum(LineTotal), and Sum(CostLineTotal)
should
work.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

JohnnyF said:
Hi. I have a subform with a product, unitPrice, costPrice, and quantity field
in it. Also I have two columns LineTotal and CostLineTotal where I have
price*quantity. When I try Sum([LineTotal]) and Sum([CostLineTotal]) together
in the form footer, errors occur. Both work fine when I cut the other one
out. Where am i going wrong?

Thanks in advance,
JohnnyF
 
G

Guest

Hi Al,
Thanks a million for yout rime and efforts. That works a treat now.

Johnny

Al Campagna said:
Johnny,
Well that's really a different problem. You wrote...
When I try Sum([LineTotal]) and Sum([CostLineTotal]) together
in the form footer, errors occur. Both work fine when I cut the other one
Now you're saying you never did get the Cost value enetered on each record?

Also, you should name the individual Line Totals as PriceSubTotal and CostSubTotal, so
they don't get confused with the footer calculations PriceTotal and CostTotal.

Your Product table should contain both Price and Cost fields. Sounds like you're
relating a seperate Cost table to a Product/Price table.
Each Product should have a unique key value associated with it, so that that value
identifies only one Product
ProdID(key) ProdName Price Cost
14251 Ratchet 1.00 .35
761321 Pawl 1.25 .62 etc..

Your combo (ex. cboProdID) should consist of 4 columns laid out just like the
fieldnames above. It should be bound to your ProdID field in your table.
Combo Column widths (ex.) should be 0"; 1.5"; .75"; .75". (That hides the ProdID and
allows the user to select by ProdName)
When the user selects a ProdName, the ProdID is actually stored in the ProdID field,
but the combo will "display" the ProdName.
OK, we've got our ProdID stored, now... using the AfterUpdate event of the combo...
Price = cboProdID.Column(2)
Cost = cboProdID.Column(3)
The two "line" calculations should work now, and you should be able to add them in the
footer.

Actually, you should name the individual line calculations as PriceSubTotal and
CostSubTotal, so they don't get confused with the footer calculations PriceTotal and
CostTotal. Never name a calculation with a field name involved in that calculation.

ProdName(combo) Price Cost Qty PriceLineSubtotal QtyLineSubtotal
Ratchet 1.00 .35 10 10.00 3.50
Pawl 1.25 .62 4 5.00
2.48
ProdTotal = 15.00 CostTotal 5.98
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

JohnnyF said:
Hi again,
Thanks for your help. That works fine for LineTotal but doesn't work for
CostLineTotal. The subform looks like this:

ProductName UnitPrice CostPrice Quantity LineTotal CostLineTotal

ProductName is a combo-box that takes its values from a products table. When
I select a Product the UnitPrice comes up in its field but CostPrice doesn't.
It can be typed manually but I don't want to do it like this. The query then
says something about ambiguous joins for CostLineTotal. I presume this is
because it's not reading the original CostPrice from the Products table but I
seem to be stuck. Any ideas?

Thanks again,
Johnny

Al Campagna said:
Johnny,
You can't use aggregate functions against "unbound" calculated fields.
Add 2 calculated columns to the query behind the subform.
LineTotal : [UnitPrice] * [Qty]
and
CostLineTotal : [CostPrice] * [Qty]
With those 2 "bound" fields on the form, Sum(LineTotal), and Sum(CostLineTotal)
should
work.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi. I have a subform with a product, unitPrice, costPrice, and quantity field
in it. Also I have two columns LineTotal and CostLineTotal where I have
price*quantity. When I try Sum([LineTotal]) and Sum([CostLineTotal]) together
in the form footer, errors occur. Both work fine when I cut the other one
out. Where am i going wrong?

Thanks in advance,
JohnnyF
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top