+/- calculations

[lathander]

Hi, I'm not sure where the correct subboard for this question would be, but I'm trying to add a calculation for an inventory tracking database and I'm wondering if this sort of thing is possible with the access functions.

So my fields are as follows:

Date
Item Name
QTY1
In/Out
QTY2


QTY3

And o I want QTY 3 to calculate QTY 1 + QTY 2 if the 4th field is "in", but have it calculate QTY 1 - QTY 2 if the 4th field is "out".

Thanks in advance.

[ArviLaanemets]

You need tables like
tblStorage: StorageID, EntryDate, EntryType, ItemID, Qty;
tblEntryTypes: EntryType, Description, Direction (where direction has values 1 for entry types marking incoming, and -1 for entry types marking outgoing movements);
tblItems: ItemID, ItemName, ...;

To calculate the quantity of item "MyItem" in stock at date "MyDate":

SELECT  it.MyItem, SUM(et.Direction*st.Qty) AS InStock
FROM (tblStorage st LEFT JOIN tblEntryTypes et ON et.EntryType = st.EntryType) LEFT JOIN tblItems it ON it.ItemID = st.ItemID
WHERE EntryDate <= MyDate
GROUP BY it.MyItem

NB! You have to get the syntax for date limit in WHERE clause right!

[CJ_London]

you can have something like this in a query

QTY3:QTY1 + iif([in/out]="in",QTY2,-QTY2)

[lathander]

Thanks for the answers. I will test both out.

A follow-up question, though, can this be linked to a form?

[ArviLaanemets]

A follow-up question, though, can this be linked to a form?

With my solution, probably the easiest way is to write an UDF, which constructs a similar querystring for active row in form, ant then runs it and returns the result value.

[Micron]

I don't know why no one commented on your un-normalized design. What if you need another QTY, you add a field? Also, you are storing a calculated result. The qty of a product should be 1 when added, -1 when removed. qty on hand is the sum of all +'s and -'s for a product.

[lathander]

Hm, I just realized that there might be simultaneous incoming/outgoing in one day, so perhaps it's better set the fields like follows:

- QTY on hand (1)
- Incoming QTY (2)
- Outgoing QTY (3)
- Final QTY : (1) + (2) - (3)

I don't want the final QTY to be on the form though.

[CJ_London]

as has been suggested by avril and micron, if this is supposed to calculate a closing balance it should be calculated as and when required