Simple Inventory of Ind. Parts but selling Kits -- please help!

[karatelung]

I'm not sure what the best way to set this up is, and I'm hoping someone can point me in the right direction.

I have a long list of different parts that I have acquired and taken inventory of. I am selling kits that contain different amounts of certain parts. For example, Kit 1 contains 2 of part A, 3 of part B, and 14 or part L. I will only be selling complete kits and not the individual parts.

Here's what I'm thinking. I need a table for the individual parts that lists the quantities of each. A table for the kits and a subtable for the kits that lists the quantities of each part. A transaction table.

Then I just need a form that has a combo box with the kits listed, date of transaction and quantity shipped, and a button that runs a query to subtract from the quantities of the individual parts based on the kits table and subtable.

Can someone please help? I'd really appreciate any tips.

Thanks.

Richie

 

[The_Doc_Man]

You need a parts table and a kits table. And a junction table I'll call KitParts.

The parts table has a unique identifier for the part number.

The kits table has a unique identifier for the kit number.

The KitParts table has three fields (minimum). The kits ID. The parts ID. The number of parts for the given part.

For any kit, you make entries in the KitParts table for the part number and quantity of every component part. So if you have six different parts in kit 101, you would have six entries in the KitParts table, all of which would have KitsID 101.

 

[karatelung]

thank you very much.

i guess the last table i have to create is a transactions table.

 

[The_Doc_Man]

Oh, I misunderstood the depth of your question.

To design something that relates to your business, you FIRST identify the entities of your business model. So far, we have identified Kits and Parts. But kits have "Assemblies" (groups of parts) which is therefore represented by the linking table that shows how many parts of each type make up one kit. With me so far? (I've been watching "Emeril Live" too much...)

OK, the next part of your business model is that you sell something. (We hope you will!) OK, that means you have a sales invoice. But a person can buy more than one thing at a time. (We hope he/she will!) So a sales invoice has sales line items. Two more tables - master sales invoice and child sales line item.

You have to get stock from somewhere. Which means purchase orders. But you will probably buy more than one part at a time and in quantity greater than one for each part. Which means line items for the purchase orders, too. Two more tables - master purchase order and child purchase line item.

This is where your question of transactions becomes significant.

The Sales Line Items are "Stock Remove" transactions at the individual level if you track by part number. (If you sell by kit, then you need to generate the part-level transactions from the kit line items since you stock by parts.) The Purchase Line Items are "Stock Add" transactions at the individual level if you track purchases by part number. The sum of all Add and Remove transactions becomes your running inventory.

Shrinkage and inventory recounts/resets are other types of transactions you might have to consider. Shrinkage is a stock remove. Recount/reset can be add or remove. And you can generate a UNION query of the various types to bring together the components.

When you are done, you should be able to verbally describe your business model and then point to each entity as a table in your Tables pane of your Access window. Data flow actions will be represented by queries that append transactions to specific tables. Reports will be managed by queries that sum your various transactions grouped by part number etc. Business rules (such as kits contain parts) will be expressed through relationships.

My advice is that when doing this, start with a dry-marker board and a big box of sticky notes. Populate your tables with individual notes representing individual records. Draw lines for relationships. Then do with paper and pen what you will eventually want Access to do. SPEND SOME TIME IN THIS MODE OF OPERATION! Because the more design facets you expose in this way, the better your understanding becomes when you convert the business model in your head into a DB for your business.

Or, stated another way, the "old professor's rule": You cannot effectively program anything that you yourself don't understand.

 

[karatelung]

Thanks a lot. It'll take me a little while to set up the way i need it.

I'm sure I'll be back needing some help with dealing with the calculations to keep inventory straight.

Richie

 

[The_Doc_Man]

Good luck, and remember that over 80% of all design flaws occur because of misunderstandings at the data/design level. Or at least that used to be the percentage. The more you catch at the design level, the less you have to do to retrofit it later. A PRIME case for "pay me now or pay me more later."

By the way, in your design, think about these kinds of transactions and look at a UNION query as the way to keep inventory straight, OR you have to do a double-dip in the way you track things.

1. Stock remove via sales detailed line item
2. Stock add via purchase detailed line item
3. Stock adjust (add OR remove) via result of inventory revealing discrepancy.

AND...

4. Stock level on-hand at last old-transaction-archive operation.

I'll explain that last one. At some point you will have too many transactions of a given type in your working tables. You will desire (trust me) to remove the oldest ones. BUT if your inventory is a running sum of all past transactions, how do you do that and still remove things?

ANSWER: Every so often, perhaps after a stock recount, you can add or remove stock via stock adjustment transactions. AT THE SAME TIME, you can run a date-restricted query of inventory on-hand at a certain date in the past. That date will be the day before the first day of transactions you will keep. Build a transaction for each part that has that date on it plus the inventory on hand as of that date. Archive everything selected by the query. Then add the on-hand transaction (with the historical date) to the parts inventory.

So what happens is that if you take part 12345 and find its inventory on-hand 91 days ago, you write a transaction dated 91 days ago with the inventory on-hand. THEN archive all other transactions 91 days old or older for that part number. (Of course, do this for every part number.)

Then you have TWO transaction table sets. One that is current, and for which all queries for inventory on-hand can be computed. Another that is archived, perhaps still accessible, but no long contributes to current inventory levels.

How do you decide how much to keep as "current" vs. how much to archive? Your business practices (rules) tell you that. When would you have archived your older paper files? (That's at least a starting point of an answer, if not a complete answer.)

OK, about transaction types: If you have separate sales, purchases, adjusts, and on-hand-at-given-date tables, you need a UNION query to keep them straight. If you merge them as stock transactions, then you need to understand that you have to pick a convention on adds vs. removes and then adjust the item that gets summed.

If you do the latter, you can add a "phantom" field in the table (one that exists but is never seen directly) or do it in the virtual recordset of your inventory query, either way. The way I did the latter was that I had one table with all transactions. But the transaction type implied a "direction" code (either +1 or -1) to go with the amount, which was ALWAYS written as a positive number. I just summed (Amount * Direction) rather than the raw Amount.