stock in hand

J

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?
 
A

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."
 
J

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?

Al Campagna said:
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 said:
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?
 
B

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
Al Campagna said:
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 said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top