Sum certain records of a subform

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

Hello. I am designing an inventory database for a small
company.

I have a main form with the product number and warehouse
location on it, and a subform on the main form with the
inventory transactions on it.

I enter receipts, shipments, and orders on my subform (I
have a Transcation Type field and a Quantity field).
I want to total only the receipt (positive numbers) and
shipment (negative numbers) transactions so that I can get
a quantity on hand. (Orders don't affect the quantity on
hand until they're shipped).

Is there a way to calculate this?

Sincerely,
Jen
 
Jen,

If I understand you correctly, here are a couple of ideas that should
give the desired result. In the Form Footer section, put an unbound
textbox, and in its Control Source put...
=-Sum([Quantity]*([Transaction Type]<>"order"))
.... or...
=Sum(IIf([Transaction Type]="order",0,[Quantity]))
 
Steve,

Thanks for the formula help. They both worked!

Sincerely,
Jen
-----Original Message-----
Jen,

If I understand you correctly, here are a couple of ideas that should
give the desired result. In the Form Footer section, put an unbound
textbox, and in its Control Source put...
=-Sum([Quantity]*([Transaction Type]<>"order"))
.... or...
=Sum(IIf([Transaction Type]="order",0,[Quantity]))

--
Steve Schapel, Microsoft Access MVP

Hello. I am designing an inventory database for a small
company.

I have a main form with the product number and warehouse
location on it, and a subform on the main form with the
inventory transactions on it.

I enter receipts, shipments, and orders on my subform (I
have a Transcation Type field and a Quantity field).
I want to total only the receipt (positive numbers) and
shipment (negative numbers) transactions so that I can get
a quantity on hand. (Orders don't affect the quantity on
hand until they're shipped).

Is there a way to calculate this?

Sincerely,
Jen
Click to expand...
.
Click to expand...
 
Back
Top