General approach? - Reducing Inventory

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I'm building a simple inventory tracking application that needs to decrease
item inventory as items are sold. The setup:

I have an Item table which includes a 'UnitsOnHand' field.

I have a Transaction table and a TransactionLineItem table, as each
Transaction can have 1 or more TransactionLineItems.

A transaction is entered via a Transaction/TransactionLineItem Form/SubForm.
When the user clicks on "Save Transaction", I'd like the 'UnitsOnHand' value
in the Item table to decrease by the 'UnitsSold' value in the
TransactionLineItem subform for every Item that is in the transaction.

Could someone please provide me with some general guidance on how this is
best achieved?

Thank you for any help,
Ken
 
Ken said:
I'm building a simple inventory tracking application that needs to
decrease item inventory as items are sold. The setup:

I have an Item table which includes a 'UnitsOnHand' field.

I have a Transaction table and a TransactionLineItem table, as each
Transaction can have 1 or more TransactionLineItems.

A transaction is entered via a Transaction/TransactionLineItem
Form/SubForm. When the user clicks on "Save Transaction", I'd like
the 'UnitsOnHand' value in the Item table to decrease by the
'UnitsSold' value in the TransactionLineItem subform for every Item
that is in the transaction.

Could someone please provide me with some general guidance on how
this is best achieved?

Thank you for any help,
Ken
Click to expand...

Run Update Query?
 
Okay, fair enough. But how do I pass the TransactionLineItem IDs (one or
more) from my current transaction one at a time to the Update Query?

Thanks!
Ken
 
Ken said:
I'm building a simple inventory tracking application that needs to
decrease item inventory as items are sold. The setup:

I have an Item table which includes a 'UnitsOnHand' field.

I have a Transaction table and a TransactionLineItem table, as each
Transaction can have 1 or more TransactionLineItems.

A transaction is entered via a Transaction/TransactionLineItem
Form/SubForm. When the user clicks on "Save Transaction", I'd like
the 'UnitsOnHand' value in the Item table to decrease by the
'UnitsSold' value in the TransactionLineItem subform for every Item
that is in the transaction.

Could someone please provide me with some general guidance on how
this is best achieved?

Thank you for any help,
Ken
Click to expand...

One alternatve approach is not to have an Items.UnitsOnHand field at
all, but calculate this value by query whenever you need it, by summing
the issue, receipt, and adjustment transactions for each item. This may
or may not be practical depending on your application, but it is the
most elegant, IMO.
 
Dirk,

Thanks for your thoughts. I had considered that approach and thought it to
be unpractical. (Though, to be honest, I'm not experienced enough with
Access to truly make that determination.)

My suspicion was that since a given item's inventory will change frequently,
the sheer number of transactions would eventually make that approach an
application bottleneck. At what point does this become true?

Thanks again,
Ken
 
Ken said:
Okay, fair enough. But how do I pass the TransactionLineItem IDs (one
or more) from my current transaction one at a time to the Update
Query?

Thanks!
Ken
Click to expand...

I was thinking of making a table populating it and clearing it after
each use.

The better way would be to set up a global variable.
 
Ken said:
Dirk,

Thanks for your thoughts. I had considered that approach and thought
it to be unpractical. (Though, to be honest, I'm not experienced
enough with Access to truly make that determination.)

My suspicion was that since a given item's inventory will change
frequently, the sheer number of transactions would eventually make
that approach an application bottleneck. At what point does this
become true?
Click to expand...

I couldn't say exactly, but a compromise approach is to periodically --
e.g., weekly, monthly, annually -- summarize the transactions to date
with a "balance forward" transaction. How large that period should be
would be based on the volume of transactions. An alternate form of this
is to trigger the "balance forward" process based on the volume of
transactions on file, on an item-by-item basis.
 
Thanks for the reply. A global variable is a reasonable idea, I'll look into
that and give it a go.

Thanks again,
Ken
 
Dirk,

Thanks again for your suggestions. Hopefully I'll have some time to work on
this project over the weekend and make some progress.

As a complete aside, I took a quick peek at your website and was surprised
to find that we live in the same town. (Such a small one at that, what are
the chances.)

Thanks again,
Ken
 
Ken said:
As a complete aside, I took a quick peek at your website and was
surprised to find that we live in the same town. (Such a small one at
that, what are the chances.)
Click to expand...

You're kidding! Feel free to e-mail me directly (remove NO SPAM from my
reply address) to talk about it.
 
Back
Top