Sum certain records of a subform

[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

 

[Steve Schapel]

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]))

 

[Jen]

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

.