Struggling with an Inventory Tacking DB

[DeanFran]

Short description, the thing will hinge on two things, Batches (that we manufacture) and Purchase Orders (that we receive from customers).
The fields that really matter are these
Batch Table
ProductID
Quantity Manufactured
Ready Status
Date of Manufacture


Purchase Request Table
Product IDs
Product Quantities Requested


1. A Batch may stretch across multiple POs, but most often a batch will match a PO. That said, a given batch is sized to produce a certain quantity. Due to manufacturing reality, that value and the actual manufactured units almost never match, so a PO might call for 200k units, The batch size might be 220k in an attempt to match a PO after scrap rejects etc. but ultimately might produce 190K units, or even 210K units. Some clients, say I’m fine with 190k or Ill take all 220k, and that closes the PO, others might say, I need exactly 200k, so the negative/postive balance is carried over to the next run of that product. Rarely does manufacturing green light a 200k run to fill a 10k balance, so that 10k balance might hang around a while, and needs to be remembered.
2. POs will often have several products
3. Track balance of product on hand.
4. Track status of POs
5. Track which batch(es) were tied to which POs.


The manufacturing department (which I’m not part of by the way, has traditionally tracked these things with a couple really crappy spreadsheets, notebooks and post-it notes. I’m not kidding, we’re small and in many ways pretty old school). They’ve had a few recent **** ups, resulting from their state of the art tracking system, and have reached out to me for help, as I’m the resident Access guy (Doesn’t count for much). I’m in the tech services/quality department, and I have created a document management database for my group, that pulls in a bunch of the batch information, so manufacturing wants to piggyback onto that. Short story long, I thought this one would be relatively easy, but I’m really struggling to create something that does what they want. I’m not looking for someone to build it for me, but the proper table structure and has so far eluded me, and I’ll take any advice I can get.

 

[plog]

but the proper table structure and has so far eluded me, and I’ll take any advice I can get.

You did a great job of posting the context of your issue and the issue itself. I think the next step is that you have to build what you think is your structure, set up the relationship tool and post a screenshot of it back here.

Do that and we can help stress test it. We will be able to easily pick out the technical normalization errors and then we can ask questions of you and your data to find out if its going to accomodate the data you need to capture with it.

 

[DeanFran]

How about the whole thing?

This is the latest iteration. I think Im getting close, but...

 

[plog]

That was kind of helpful, I was really hoping for the Relationship tool. So, I made it and its a spider web (See attached).

There should only be one way to trace a path between tables. In the screenshot I posted you can trace 4 paths between tblPO and BatchT. That is incorrect. There should only be one path. I don't know which one that is, but I do know you have too many relationships.

Additionally, YesNoT is not necessary--there's a field type called Yes/No, just use that instead and omit the table. But that's minor compared to the spider web thing.

I think the best method forward is to mentally cleave this into its two parts--PO tracking and Batch tracking and then get one of those parts working. I just think its too complex to try and make everything work at once. So, work on the Relationship tool by adding either the Batch tables or the PO tables and omitting any table that attempts to link the two. Get the relationship tool correct for just one side. Post a screenshot of that and we can work through it.

 

[Mark_]

Three steps back.

You need a product table that is looked up (not directly related to) INVENTORY, BATCH and PO LINE ITEM
.
You need an INVENTORY. This has
Product – What is made
Quantity – Amount on hand
DateInventory – Date of the last physical inventory

You need a BATCH table. This has
Product – What is made
Quantity – Acceptable products ONLY, don’t worry about scrap at this point
Date – When the batch was made

You will need a PO
Customer – Who ordered
Date – When they ordered

You will need a PO LINE ITEM Child to PO
Product – What is made
Quantity – Amount they order
DateDue – When they want it delivered.
Fulfilled – Yes/No if customer considers this line item “Closed”.

You will need a PO LINE SHIP Child to PO LINE ITEM
Quantity – Amount of product in parent shipped
BATCHID – What batch it is from (if relevant, needed if recalls are involved OR if product expires)
DateShipped – When shipped

To find out how much of a product you have on hand, look at the LAST (by date) record in the INVENTORY file, add to it all quantities for the product in BATCH with dates AFTER your INVENTORY date, and subtract all PO LINE SHIP quantities with dates AFTER your INVENTORY date.

How this all works to reflect your real situation

You do an inventory of a given product to see how many you have on hand. This should but will not always match what you calculate with the above. This is because some products get damage / lost / stolen / what have you between physical inventories.

As customers order the product, you make more. From an inventory perspective you don’t worry about quantity manufactured, just quantity that is saleable. As you ship, you record when what was sent for which line item. When the customer says “OK, that’s good” you mark that PO LINE ITEM as fulfilled.

This lets you know, once all line items are fulfilled, that the PO is fulfilled so you can close that PO.

This covers when one batch covers one order, one batch covers more than one order, and where you need more than one batch to cover an order.

Does this give you a basic frame to start working off of?

 

[DeanFran]

Plog,
Thank you for taking the time to do that. There are a bunch of redundant tables/relationships in there as I was trying different things. I apologize. I should have cleaned it up first, and it's still a mess even after cleanup. The whole thing is going on the scrap heap.



Mark,
That's a relationship model I never even considered. I'll get to work on your design criteria, and return when I have something to show. Thank you for taking the time to explain.

 

[Pat Hartman]

My view is a little simpler. You need a product table. A batch is a child of the product table. This is how you will manage inventory. You need a PO table to hold the order. If the PO can be for multiple products (and perhaps even if it currently can't), a child of PO would be the order detail - how much of each item the customer wants.

Now we get to the fulfillment part. This is a junction table that sits between a batch and a PO detail and specifies how much is assigned to the order. Based on your description, the amount may be =, >, or < the amount that was ordered. In most applications = and < would be allowed but not > so that makes this a little different than most although not unheard of because I actually worked for a clothing manufacturer who also needed to over or under produce depending on how the pattern pieces fit on the material as the garment was cut and we also used to ship all the garments since the discrepancy was usually small.

Random thought. You might want a complete flag on the PO and another on the Batch to make your searches more efficient so you don't have to consider filled orders or exhausted batches. If you were to use such a flag, the flag would also be used to prevent changes to the child records so they couldn't be changed later.

 

[Mark_]

Pat,

Same page as me, but I learned to include a separate table for the physical inventory. Avoids a lot of internal issues when the person taking inventory has no system for updating a production batch and vice versa. Keep the operations people from trying to hide missing product and often requires different fields than the "batch".

 

[Pat Hartman]

This sounds more like an on demand manufacturing system. It didn't sound like they keep inventory. They make it when a customer orders it but they have to account for underages/overages.

 

[Mark_]

This sounds more like an on demand manufacturing system. It didn't sound like they keep inventory. They make it when a customer orders it but they have to account for underages/overages.

For the most part, I agree. Post #1 Item 1 though says he's got to track inventory though. I am guessing that a monthly inventory would normally be "0 on hand", until they get a customer who starts asking for "exactly 200k each time" and orders often. Then they decide to keep 10k to 20k on hand for when they do batch runs for them, but need to track an actual on hand inventory. Easier to set up this to begin with, even if they keep putting in "no inventory this month" for most products.

 

[DeanFran]

Pat,


Yes, with a few exceptions, we are on demand. We have a few part numbers that aren't that we manufacture a batch of, and essentially manage the shipping and inventory for our customer. These part numbers are manufactured in large batches like all of our products, but are shipped/sold in small bits, a few shippers at a time. In fact, I did build an Access app for these particular part numbers that is used every day. Its pretty slick if I do say so myself. It captures each unique order fulfillment, keeps the inventory balance, generates an order report, sales summaries for the financial person, user defined history searches, etc. I thought I would be able to basically recreate something similar for all the rest, but as I'm learning, the business rules, and user requirements are entirely different.

 

[Pat Hartman]

The difference is that you are tying a batch to an order so you are not dealing with a single bucket of inventory quantity. You need to take quantity from one or more batches.

 

[Mark_]

DeanFran,

For your business, is it required that you track "Batch" through to delivery?
I know that with food products, which "Batch" can be very important when there is a recall, but I'm not sure if this applies for your requirements.

 

[Pat Hartman]

I designed a QA database for Pratt & Whitney (jet engines) and we had to track the batch, lot, or heat that everything came from. If rivets failed, we had to track back to the heat and then find all engines with any metal parts from the same heat.

 

[DeanFran]

Mark,


Yes, we do have to maintain information on all batches manufactured, but that is beyond the scope of this project. As I mentioned before, I built an Access application that manages QA and Technical Services documents, that is a small part of that, and why some batch information is pulled into that system.