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
