General approach? - Reducing Inventory

message from Ken on 27 May 2004

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

Dirk Goldgar replied to Ken on 27 May 2004

"Ken" <kcoakley@macomberreports.com> wrote in message
news:dbScnYSmbJgPuSvdRWPC-w@speakeasy.net

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.

Ken replied to Dirk Goldgar on 27 May 2004

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

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:OO5fWZBREHA.4016@TK2MSFTNGP12.phx.gbl...

Dirk Goldgar replied to Ken on 27 May 2004

"Ken" <kcoakley@macomberreports.com> wrote in message
news:ArGcnTziiYpnrivdRWPC-w@speakeasy.net

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.

Ken replied to Dirk Goldgar on 28 May 2004

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

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:enj6yaCREHA.1656@TK2MSFTNGP10.phx.gbl...

Dirk Goldgar replied to Ken on 28 May 2004

"Ken" <kcoakley@macomberreports.com> wrote in message
news:ryydnXomz5R0zSrdRWPC-g@speakeasy.net

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

Ken replied to Dirk Goldgar on 28 May 2004

Well that's it then... that's everybody in town. :)

Ken

"bong" <abc@abc.com> wrote in message
news:%23b1pJxMREHA.3580@TK2MSFTNGP11.phx.gbl...

Joseph Meehan replied to Ken on 27 May 2004

Run Update Query?

Ken replied to Joseph Meehan on 27 May 2004

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

"Joseph Meehan" <sligojoeS_PAM_2@hotmail.com> wrote in message
news:G0qtc.391$bd3.379@fe2.columbus.rr.com...

Joseph Meehan replied to Ken on 27 May 2004

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 replied to Joseph Meehan on 28 May 2004

Thanks for the reply. A global variable is a reasonable idea, I'll look into
that and give it a go.

Thanks again,
Ken

"Joseph Meehan" <sligojoeS_PAM_2@hotmail.com> wrote in message
news:iCrtc.9843$dn1.3192@fe2.columbus.rr.com...

Archived message: General approach? - Reducing Inventory (MS Access Forms)