stock in hand

[Jon]

Greeting,

I have a table called inventory transactions which has the following fields:
ID>>>autonumber>>>Primary Key
Transaction Item>>>number>>>1 for “Penâ€, 2 forâ€Watchâ€â€¦..
Transaction Type>>>number>>>1 forâ€additionâ€, 2 for “Shrinkageâ€
Quantity>>>number

What I want to do is summing the quantity transaction type “Addition†for
the item e.g. Pen, after that, summing the quantity transaction type
“Shrinkage†for the item e.g. Watch
There will be more than 1 transaction (Addition or Shrinkage) on the
transaction item.
The main purpose of the above need is to compare the two values and get the
stock in hand. After that, if in stock is less than 5 , then warning msg
appears and user cannot do any Shrinkage transaction in the item. Please
advice?

 

[Al Campagna]

Jon,
I think you might find it easier to try a different setup.

Given a table (ex. tblInventoryTransactions) setup...
TransactionID (key)
ItemNo
Credit
Debit
A "Receiving" form would allow additions to the [Credit] field.
A "Disbursment" form would use the [Debit] field.

The current inventory level at any time would be...
Sum(Credit) - Sum(Debit)

In the table (ex. Items)
ItemNo (key)
ItemDecription
ReorderPoint
would allow for a simple calculation to determine if reordering is required.
Is (Sum(Credit) - Sum(Debit)) > or < than ReorderPoint?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Jon]

Ok. but if there is more than one record for X item, or more than One P.O
for the item, how sum all these record?

Jon,
I think you might find it easier to try a different setup.

Given a table (ex. tblInventoryTransactions) setup...
TransactionID (key)
ItemNo
Credit
Debit
A "Receiving" form would allow additions to the [Credit] field.
A "Disbursment" form would use the [Debit] field.

The current inventory level at any time would be...
Sum(Credit) - Sum(Debit)

In the table (ex. Items)
ItemNo (key)
ItemDecription
ReorderPoint
would allow for a simple calculation to determine if reordering is required.
Is (Sum(Credit) - Sum(Debit)) > or < than ReorderPoint?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Greeting,

I have a table called inventory transactions which has the following
fields:
ID>>>autonumber>>>Primary Key
Transaction Item>>>number>>>1 for "Pen", 2 for"Watch"...
Transaction Type>>>number>>>1 for"addition", 2 for "Shrinkage"
Quantity>>>number

What I want to do is summing the quantity transaction type "Addition" for
the item e.g. Pen, after that, summing the quantity transaction type
"Shrinkage" for the item e.g. Watch
There will be more than 1 transaction (Addition or Shrinkage) on the
transaction item.
The main purpose of the above need is to compare the two values and get
the
stock in hand. After that, if in stock is less than 5 , then warning msg
appears and user cannot do any Shrinkage transaction in the item. Please
advice?

 

[Bob Quintal]

Ok. but if there is more than one record for X item, or more than
One P.O for the item, how sum all these record?

Exactly the same way you would sum your original transactions
table,... using a totals query.

Q

Jon,
I think you might find it easier to try a different setup.

Given a table (ex. tblInventoryTransactions) setup...
TransactionID (key)
ItemNo
Credit
Debit
A "Receiving" form would allow additions to the [Credit]
field. A "Disbursment" form would use the [Debit] field.

The current inventory level at any time would be...
Sum(Credit) - Sum(Debit)

In the table (ex. Items)
ItemNo (key)
ItemDecription
ReorderPoint
would allow for a simple calculation to determine if reordering
is required.
Is (Sum(Credit) - Sum(Debit)) > or < than ReorderPoint?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your life."

Greeting,

I have a table called inventory transactions which has the
following fields:
ID>>>autonumber>>>Primary Key
Transaction Item>>>number>>>1 for "Pen", 2 for"Watch"...
Transaction Type>>>number>>>1 for"addition", 2 for "Shrinkage"
Quantity>>>number

What I want to do is summing the quantity transaction type
"Addition" for the item e.g. Pen, after that, summing the
quantity transaction type "Shrinkage" for the item e.g. Watch
There will be more than 1 transaction (Addition or Shrinkage)
on the transaction item.
The main purpose of the above need is to compare the two values
and get the
stock in hand. After that, if in stock is less than 5 , then
warning msg appears and user cannot do any Shrinkage
transaction in the item. Please advice?