Unanswered: Trying 2 Set Up an Inventory Control DB...
....and not sure how to execute something.
A friend of mine owns a business at which he constructs several types of trailers. Each trailer has a different parts list, but all of the parts used to construct all trailers are listed in the main 'Master Inventory' table.
What I want to be able to do is to tell Access to decrement the Master Inventory table by the number (and types) of parts required to build each trailer. For instance, if I know 'Trailer One' is constructed with 5 left handed widgets and 5 Snickers bars, I want the 'quantity' field in the "Left Handed Widgets" and "Snickers bars" lineitems in the "Master Inventory" table to be decremented by 5 when I tell Access to "Build Trailer One."
I'm just not sure how to proceed. I'm fairly sure I will have to build one table for each kind of trailer, and have it contain the parts list for each trailer - and then have the quantities for each line item decrement the quantities for the corresponding line items in the "Master Inventory" table. I just don't know how to go about it. Sorry for the bother, and thanks for any assistance provided.
Trailer Comments More info about the Trailer only
What Parts in a Trailer
What Parts in a TrailerID
TrailerID <= Trailer Table the link to the tailer table
more stuff about Building the Trailer
now join then
What Parts in a TrailerID
stock used is
parts used = [Order Trail].[qty]*[What Parts in a Trailer].[qty]
$$$ used is
Trailer cost $$ = [What Parts in a Trailer].[qty] * [Master InventoryID].[$$$]
this just off top of head need more thinking
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
Well, it would actually be easier to take the entire process out one notch and account for inventory RECEIVED as well.
You could make entries into your incoming invoices table, and never have to touch them again.
When an order is made, a quantity for each inventory item is entered along with the order. To return the current inventory level, you would then subtract the sum of a given part in the orders table from the sum of a given part in the received table.
That's just it. I don't think it's going to be that easy.
In other words, when stuff is 'added' to the inventory, it will be added as the parts. But the only time that things will ever be subtracted from the inventory (other than when people take stock of the inventory) is when a trailer is built. So, what we want to be able to do is to create a macro of some sort that will decrement the Master Inventory db by the correct number of EVERY part required to build that trailer.
For instance, if there's 20 different things required to build the trailer, and each of those 'things' are needed in a quantity of five, then I want to be able to have the database decrement the Master Inventory database by 5, for each of those 20 items.
Naturally, you SHOULD have a primary key associated with your parts, so you could create a query similar to the following:
SELECT partid, SUM(incoming.widgets) AS original_inventory, SUM(order_dtl.widgets) AS used_inventory, SUM(incoming.widgets) - SUM(order_dtl.widgets) AS current_inventory
FROM incoming LEFT JOIN order_dtl ON incoming.partid=order_dtl.partid
Alright, I think I understand what you're saying. (I'm NOT trying to be stupid, honest! *LOL* I seriously AM this new at it.)
It seems to me that I'm going to be creating a database that will be the 'original' inventory. Then, I'm going to add the number of incoming parts to that, and decrement the number of outgoing parts, and that'll give me my current inventory.
What I'm having trouble understanding (and you might have said it - like I said, I'm this new *LOL*) is how to make Access equate building a trailer with decrementing numerous parts. The people I will be building this for DO NOT want to go in and mark that 2 widgets and one wingnut has been used. They want to somehow press a button or notate that one trailer has been built, and then automate the task of decrementing all invididual parts.
That's not to big of a deal. Assuming you have a table that describes how much of each part is required for each type of trailer, you simply include that table in the query and use it to fetch the required information.