Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2019, 07:37 AM   #1
DeanFran
Newly Registered User
 
Join Date: Jan 2014
Posts: 100
Thanks: 60
Thanked 1 Time in 1 Post
DeanFran is on a distinguished road
Struggling with an Inventory Tacking DB

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 cock 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.

DeanFran is offline   Reply With Quote
Old 08-16-2019, 07:54 AM   #2
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,433
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Struggling with an Inventory Tacking DB

Quote:
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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
DeanFran (08-19-2019)
Old 08-16-2019, 08:09 AM   #3
DeanFran
Newly Registered User
 
Join Date: Jan 2014
Posts: 100
Thanks: 60
Thanked 1 Time in 1 Post
DeanFran is on a distinguished road
Re: Struggling with an Inventory Tacking DB

How about the whole thing?

This is the latest iteration. I think Im getting close, but...
Attached Files
File Type: zip POBatchDB.zip (153.1 KB, 16 views)

DeanFran is offline   Reply With Quote
Old 08-16-2019, 08:43 AM   #4
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,433
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Struggling with an Inventory Tacking DB

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.
Attached Images
File Type: png x.PNG (42.7 KB, 18 views)
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
DeanFran (08-19-2019)
Old 08-16-2019, 09:47 AM   #5
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Struggling with an Inventory Tacking DB

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?
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
DeanFran (08-19-2019)
Old 08-16-2019, 10:10 AM   #6
DeanFran
Newly Registered User
 
Join Date: Jan 2014
Posts: 100
Thanks: 60
Thanked 1 Time in 1 Post
DeanFran is on a distinguished road
Re: Struggling with an Inventory Tacking DB

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.
DeanFran is offline   Reply With Quote
Old 08-16-2019, 04:27 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Struggling with an Inventory Tacking DB

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
DeanFran (08-19-2019)
Old 08-18-2019, 03:28 PM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Struggling with an Inventory Tacking DB

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".
Mark_ is offline   Reply With Quote
Old 08-18-2019, 08:44 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Struggling with an Inventory Tacking DB

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-18-2019, 09:35 PM   #10
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Struggling with an Inventory Tacking DB

Quote:
Originally Posted by Pat Hartman View Post
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.
Mark_ is offline   Reply With Quote
Old 08-19-2019, 04:39 AM   #11
DeanFran
Newly Registered User
 
Join Date: Jan 2014
Posts: 100
Thanks: 60
Thanked 1 Time in 1 Post
DeanFran is on a distinguished road
Re: Struggling with an Inventory Tacking DB

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.
DeanFran is offline   Reply With Quote
Old 08-19-2019, 05:40 AM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Struggling with an Inventory Tacking DB

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-19-2019, 06:13 AM   #13
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Struggling with an Inventory Tacking DB

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.
Mark_ is offline   Reply With Quote
Old 08-19-2019, 08:11 AM   #14
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Struggling with an Inventory Tacking DB

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-20-2019, 03:39 AM   #15
DeanFran
Newly Registered User
 
Join Date: Jan 2014
Posts: 100
Thanks: 60
Thanked 1 Time in 1 Post
DeanFran is on a distinguished road
Re: Struggling with an Inventory Tacking DB

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.

DeanFran is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Submittal Tacking Table andy1968 Tables 7 07-03-2018 09:41 AM
Struggling fadwen General 9 05-02-2016 06:50 PM
Struggling! lfoulsha Introduce Yourself 2 08-05-2014 11:37 PM
Newbie Question / Pointers for Inventory for Inventory DB boliviab Tables 2 09-27-2012 10:09 PM
Closing Inventory/Opening Inventory libby Reports 0 04-01-2000 04:00 PM




All times are GMT -8. The time now is 04:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World