Calculated field in form from another table

[weilerdo67]

Hello wonderful people. I am scratching my head trying to figure this one out. I have a form "Inventory" that is based on a table "Inventory" that stores Part Details (Part number, description, supplier, etc). I have a second table that stores transactions for a given part (IE addition, removal, etc) they are linked via Part Number. On my form I have a field that I want to show me the current inventory quantity on hand. Since my form is not based on a query I am trying have the field only display the data and not write it to a table. It seems like this should be simple. In my tbl_transaction I have a field called Trans_Type and a field Quantity. This is the expression I have tried in different forms but only get a #NAME?

=DSum([tbl_transaction].[Trans_Type]="Addition",[tbl_transaction]![Quantity],-([tbl_transaction]![Quantity]))
and this:
IIf([tbl_transaction].[Trans_Type]="Addition",[tbl_transaction]![Quantity],-([tbl_transaction]![Quantity]))

Not sure if this should be done in VB or what is the best coarse, but in my "tbl_transaction" table for example I have a Part Number and have 3 rows in there 1 row is Addition Qty 25 and 2 rows of Removal for 1 and 5 so I would expect the result to be Quantity on hand in my form to be 19.

I hope this makes since and I appreciate any help in advance.

 

[Gasman]

Do not guess the syntax of functions. Look it up.
Your Dsum() is nowhere near correct.
If you get the syntax correct, you should be able to calculate what you want.
I would expect however you would need to take into account the Deductions as well, plus filtering on the PartNumberID ?

 

[Eugene-LS]

any help in advance.

Try:

vba access DSum

 

[plog]

You need a query.

The stumbling block is that you are not storing +/- in the Quantity field. So you need logic on an individual record level (to determine + or -), then after that is done you need to aggregate all the records. There's no domain function that will do both those things for you. So, you need to build a query that does the [Trans_Type] logic, then you can use a DSum in the form to add all the rows together.

 

[arnelgp]

firstly use Query to your form.
create an Aggregate (Total) query agains your transaction table, Group by Part Number, to
total all Additions and Removal and the Balance of each Parts:

qryPartsTotalAddition:

SELECT tbl_Inventory.[Part Number], Sum(Nz(IIf([Trans_Type]="Addition",[Quantity],0),0)) AS TotalAdd
FROM tbl_Inventory LEFT JOIN tbl_Transaction ON tbl_Inventory.[Part Number] = tbl_Transaction.[Part Number]
GROUP BY tbl_Inventory.[Part Number];

qryPartsTotalRemoval:

SELECT tbl_Inventory.[Part Number], Sum(Nz(IIf([Trans_Type]="Removal",[Quantity],0),0)) AS TotalRemove
FROM tbl_Inventory LEFT JOIN tbl_Transaction ON tbl_Inventory.[Part Number] = tbl_Transaction.[Part Number]
GROUP BY tbl_Inventory.[Part Number];

qryPartBalance:

SELECT tbl_Inventory.[Part Number], [TotalAdd]-[TotalRemove] AS Balance
FROM (tbl_Inventory LEFT JOIN qryPartTotalAddition ON tbl_Inventory.[Part Number] = qryPartTotalAddition.[Part Number]) LEFT JOIN qryPartTotalRemoval ON tbl_Inventory.[Part Number] = qryPartTotalRemoval.[Part Number];

your query on your form:

SELECT tbl_Inventory.[Part Number],
    tbl_Inventory.Description,
    tbl_Inventory.Supplier,
    DLookUp("TotalAdd","qryPartTotalAddition","[Part Number] = '" & [Part Number] & "'") AS TotalAddition, 
    DLookUp("TotalRemove","qryPartTotalRemoval","[Part Number] = '" & [Part Number] & "'") AS TotalRemoval, DLookUp("Balance","qryPartBalance","[Part Number] = '" & [Part Number] & "'") AS Balance
FROM tbl_Inventory;

 

[Pat Hartman]

All transactions should be stored with their natural sign. + for adds, - for deletes. Then you can just sum the quantity and not have to examine the type to get the sign. Alternatively, you can store a multiplier field in the type table. Its value would be 1 or -1. Then your query joins to the tran type table and multiplies the amount by the multiplier field and sums it.

This gets a little complicated for the adjustments type since adjustments due to stock takes can be + or - so the multiplier for adjustments would be 1 and the quantity would have a natural sign.

 

[weilerdo67]

Arnelgp, thank you so much for the help. This makes total since when I see how you broke it out. Big thumbs up...

 

[Pat Hartman]

Using domain functions in queries is very inefficient. There are far better solutions.