missing a concept

[RustyRick]

I can build a table and form for our "Parts Inventory". No problem.
I can build an "Order form" for orders that identify the parts.

How do I get the "Order form" to delete the parts or update (reduce) the "Parts Inventory" table with the reduced products?

Conversely I can build a "Form" to receive parts from vendors. How do I get that "Form" to add the newly purchased supplies into the "Parts Inventory"?

I'm probably making this harder than it really is.

 

[plog]

I got a bad feeling about your tables. Can you provide some sample data from each? Be sure to include table and field names. Use this format for posting your data:


Table1NameHere
Field1NameHere, Field2NameHere, Field3NameHere
David, 12, 4/3/2008
Steve, 8, 5/16/2010
Amy, 2, 6/6/2007

 

[RustyRick]

I'm just starting the process.
We have a parts supply warehouse whose contents changes daily. It's always "in flux".

Staff come daily and need parts and supplies for projects. So items are going out of inventory. That's one event.

Items are purchased daily and brought to the warehouse to replenish stock. That's another event.

So should I have 3 tables 1) Inventory, 2) Purchase Orders, 3) Parts acquisition É

And use delete or update queries to basically keep my incoming and out going tables empty buy frequently ``posting`` the daily activity open items.

Trying to figure the best approach.

 

[RustyRick]

"For this reason as well as the lack of auditability, I prefer to keep separate inventory transactions. That way you can sum the transaction to determine the current balance."

That's my thinking too. There is totally different required fields for the "Purchase Orders" and the "In house" consumption of supplies. And that way I can run reports off specific to different events.

So to reiterate.
I'll make one table for the incoming parts and a second table for the "in-house" consumption.

 

[RustyRick]

This is a quite Greek to me.

Never have heard of a "natural sign".

Each part has 3 stages Incoming, On hand, and Sold to a department.

So how does 1 or -1 handle that?

Are you saying to handle all these process's in one table?

We'll want to create reports of Vendors, dates of purchase, quantities through out the year, price comparisons from competitive vendors etc.

An Inventory report with Need to Order report, and from where, etc.

And with the plant having differing "departments", profit center type calculations.

Me.txtQty = Me.txtQty * Me.txtSign Is "txt" the part number, or the quantity of parts?

Sorry maybe this is over my head.

 

[gemma-the-husky]

generally the best way to manage inventory is to store a table of transactions, rather than to update the balance each time

so a purchase order (delivery) should generate a plus transaction (increasing the stock). a sale order (delivery) should generate a minus transaction (decreasing the stock).

what Pat is suggesting is storing the "selection" between plus and minus in the transaction type table, and applying it to the transaction quantity, so that the quantity value in the transaction table is stored correctly as plus or minus accordingly. (rather than storing different movement types all as pluses, and then having to determine whether to add or subtract at a later stage)


equally you could just hard-code the sign within your update process, rather than store the sign in the table. after all, it isn't ever going to change.


doing it this way, means you can just add the transaction totals to generate a movement total. if you had to use separate plus and minus queries, and then union them, you would get a non-updateable query , which is awkward in some instances, as well as being slower. (this is also better than storing each different transaction type in a separate table, which leads to the similar problems)

 

[nanscombe]

One further complication, that springs to mind is ...

Are the parts bought and consumed in the same quantities?

You may order 1 box of 100 parts - Transaction(In) 1 box
... but ...
You may use parts one at a time - Transaction(Out) 3 parts

What happens if you get a box of 100 parts marked 5% free, don't forget it would be a quantity of 105 not 100.

 

[RustyRick]

GRR - no kidding - good point to ponder

Another is changing prices results in changing values of inventory. I think this is going tooooo far

 

[gemma-the-husky]

GRR - no kidding - good point to ponder

Another is changing prices results in changing values of inventory. I think this is going tooooo far

OK - inventory - quantities is one thing, and is RELATIVELY easy to so.

prices and stock valuation is a whole new level of complexity. A serious developer would think twice before tackling an inventory system involving stock valuation., and it would depend on the circumstances - but generally you are talking a significant project.

 

[nanscombe]

It sounds mind boggling at first but, don't forget, someone is probably doing the job already. They'll just be using bits of paper and ledgers to do it.

When tackling a job like this don't treat it as something completely new. It would probably be a good idea to examine the existing system.

Paper forms, ledgers and typed reports will give you an insight into the information that you will need to store and some ideas about how to store it:

Paper forms can become Access forms / tables
Ledgers may give you an idea of the types of calculations used
Typed reports / inventory sheets can be recreated as Access reports

There will be a wealth on information about the existing process on the shop floor, as people already do the job, so why not take some time out and pick their brains before trying to re-invent the wheel all by yourself.