Combining inventory transactions

[Amin]

Hello Experts,

In an inventory database if you wanted to perform an item transfer between
two locations you'd typically create two records in the transactions table,
one for the source and the qty is a negative number and another one for the
destination warhouse in a positive number.

Any ideas on how to handle this using only one line (choose the source,
destination and enter quantity once)?

Thank you all,

 

[Fred]

Beyond just saying to use a form coded to execute the transactions, the final
answer will take somebody better at coding that I. But I do know
that they will need to know more about your table structure. A lot of even
mid-range inventory DB structures would have one main record for the part
number, a primary storage location, and total inventory values.

Your structure is tracking multiple inventories of the same part number at
multiple locations. So it has a fancier table structures in the area
relevant to your question.....you'll have to tell us what that is.

 

[Fred]

Beyond just saying to use a form coded to execute the transactions, the final
answer will take somebody better at coding that I. But I do know
that they will need to know more about your table structure. A lot of even
mid-range inventory DB structures would have one main record for the part
number, a primary storage location, and total inventory values.

Your structure is tracking multiple inventories of the same part number at
multiple locations. So it has a fancier table structures in the area
relevant to your question.....you'll have to tell us what that is.

 

[Tony Toews [MVP]]

In an inventory database if you wanted to perform an item transfer between
two locations you'd typically create two records in the transactions table,
one for the source and the qty is a negative number and another one for the
destination warhouse in a positive number.

Any ideas on how to handle this using only one line (choose the source,
destination and enter quantity once)?

No. Well maybe.

I always use a transaction table that tracks all inventory ins and
outs as well as purchase orders for QOO. Thus it's quite easy to
create queries against this table to view the data.

Now what you could do is create an unbound form which has the various
fields such as part number, date, qty and the two locations. Then
write two records to the transaction table.

Tony

 

[Tony Toews [MVP]]

In an inventory database if you wanted to perform an item transfer between
two locations you'd typically create two records in the transactions table,
one for the source and the qty is a negative number and another one for the
destination warhouse in a positive number.

Any ideas on how to handle this using only one line (choose the source,
destination and enter quantity once)?

No. Well maybe.

I always use a transaction table that tracks all inventory ins and
outs as well as purchase orders for QOO. Thus it's quite easy to
create queries against this table to view the data.

Now what you could do is create an unbound form which has the various
fields such as part number, date, qty and the two locations. Then
write two records to the transaction table.

Tony

 

[Tony Toews [MVP]]

A lot of even
mid-range inventory DB structures would have one main record for the part
number, a primary storage location, and total inventory values.

But if the app has to handle multiple inventory locations then you
have to have at least two tables if not 5 or 10 or 30. One for the
part number, description, manufacturer, vendor and usually cost and
price. The other parts transactions table would have a PartID foreign
key, a location foreign key and various qtys such as QOO and QOH.

Tony

 

[Tony Toews [MVP]]

A lot of even
mid-range inventory DB structures would have one main record for the part
number, a primary storage location, and total inventory values.

But if the app has to handle multiple inventory locations then you
have to have at least two tables if not 5 or 10 or 30. One for the
part number, description, manufacturer, vendor and usually cost and
price. The other parts transactions table would have a PartID foreign
key, a location foreign key and various qtys such as QOO and QOH.

Tony