Help Needed With DSUM???

[Guest]

Hi

Please can anyone help me with the following query:-

I am trying to create an inventory application in Access 2007.
I have created a Stock table (info stored on each shoe), a Warehouse table
(where stock is kept). A Transactions table (info stored on each transaction).
I also have a sales table that I want it to record items that go in and out
of the warehouse and an orders table that records items coming in.

How would I get both of these tables to add records to the Transaction table?

I have also created a DSUM function:- (which I think is correct)
=DSUM("[Quantity]","tblTransactions","[Stock ID] = " & [tblStock].[Stock ID]
& " AND [Transaction Type] = 'Incoming'") -
DSUM("[Quantity]","tblTransactions","[Stock ID] = " & [tblStock].[Stock ID] &
" AND [Transaction Type] = 'Outgoing'")

But where in a query should I put this and what other fields should I
include, so it will work?

I hope my explanation is clear to you.
Please do not hesitate to ask me any questions if you need to clarify before
being able to answer my question.

Thanks for all your help,
Louisa.

 

[Guest]

Louisa,

It looks like your DSUM function is supposed to calculate the current number
of each item ([Stock_ID]) in your transactions table, but if you don't know
where you would put this in a query, I don't know what good it is going to do
you. You might put it as the control source of a textbox on a form, and then
replace the references to [tblStock].[Stock_ID] with a reference to a combo
box on a form.

If I wanted to write a query to get the quantity of each item in stock, I
would probably not use DSUM. I would write my write my query something like:

SELECT Stock_ID,
SUM(IIF([Transaction_Type] = "Incoming", 1, _
IIF([Transaction_Type] = "Outgoing", -1, 0)) *
[Quantity]) as InStock
FROM tblTransactions
GROUP BY Stock_ID

By nesting the IIF statements, I can determine whether to add a positive or
negative "Quantity".

HTH
Dale