Food Manufacturing Lot Number Tracking Database

[bwarren]

I've spent a while looking at other forums trying to figure out the problem I am having with building a MS Access Database for tracking a lot number through manufacturing from the Raw Materials Inventory to Finished Goods. I have a pretty solid background of MS Access concepts and some experience in VBA but am confused on where to start for this project.

To give some background:
The firm I work for produces a variety (20+) of baked goods
The firm operates on primarily paper documentation and daily excel spreadsheets regarding production, causing difficulties in traceability for a specific batch or lot number.
The firm also has a poor inventory keeping system (or lack thereof) so the functionality of an inventory system is desired

The MAIN goal of the database is to be able to track a lot number from a finished good all the way back to the lot number it was associated with in the raw material phase.

With that, I have already made a very basic inventory control database that could potentially be expanded if it is applicable. What I have now is a form that is linked to a inventoryTable, that includes a combo box to select the product, a text box to enter a value, and three buttons reading Add, Reduce, and Override to impact the inventory levels in different ways.
I also created an OrderAndUpdate form that is linked to inventoryTable, that includes a combo box to choose what product you want to produce and below the Raw Materials and their amounts used to create that product. The form runs an update query to reflect the changes in amounts for those products in the inventoryTable. This could be useful for the database I am trying to construct, but I am having trouble seeing where all these pieces will fit in with each other.

In a perfect world, the database I would desire would be one that can input received inventory into rawMaterialsInventory, deduct the appropriate amount of raw materials based on the formula, and then transfer the now finished goods into the finishedGoodsInventory, all while being able to track the lot number. My question is, is this project too complex to be doing on MS Access or is it feasible?

 

[plog]

My question is, is this project too complex to be doing on MS Access or is it feasible?

Yes, its feasible, but not to be too harsh; I don't know if its feasible for you. I'm sure you can get there, but I don't think you have the skills yet to do this. So, my real question is, how much time/effort can you devote to learning and when would you like a finished database product?

What I have now is a form that is linked to a inventoryTable, that includes a combo box .. a text box...three buttons... an OrderAndUpdate form... a combo box...The form runs an update query...

This is why I am concerned. You mention "form" three times, describe 6 objects on forms, mention an UPDATE query but only talk about one table. That's a bad sign. Tables are the foundation and main portion of any database. Forms are the last thing to be concerned about--although they are often the first thing most beginners expend effort on.

Right now, its all about tables. You need to set them up properly before you do anything else. That process is called database normalization:

Database normalization - Wikipedia

en.wikipedia.org

I suggest you read up on that, work through a few tutorials and then give it a shot with your database. You said you have spreadsheets currently, I would start with them. Use them to build your tables with all the fields you think you need. Once done and you think you've applied everything you've learned about normalization to them, complete Access's Relationship Tool, expand all the tables so we can see their fields, take a screenshot adn post it back here so we can help you build your tables properly.

 

[Kayleigh]

Sometimes the best option is to outsource the project instead of getting yourself tangled in knots. I could give you some excellent recommendations. (We have built a couple of systems similar to the one you have described - PM for more info as this discussion is not intended as marketing)

 

[bwarren]

I ask if it is feasible because I have looked on a number of forums for similar projects with a lot of responses saying that the project is too large, or that there are third-party ERP systems that can more easily handle the situation. I also have not set up any of the tables yet because I do not have access to any of the data, so I have been making tables and relationships with a small number of sample data. I brought this question here primarily to see if anyone has worked on a similar database and any tips they might have.

 

[Kayleigh]

For instance we designed a database for a door/window manufacture company which is modelled according to the process it goes through during production line. It accommodates for various types of clients (trade/retail etc) and many other nuances specific to this company. The process is so streamlined that anyone in the manufacture process can pick up a job and know exactly what has been done and what is required. This is due to well-placed data, CRMs, summaries and other organised methods of logging data.

The main advantage of a third-party ERP is that it is usually designed for a specific niche and has been well tested before release, but it will be limited to the features built into it by its development team. However MS Access is much more versatile and can be tweaked to fit around the firm's work processes (it is usually advisable to work with the business rather than impose new processes on account of new software).

I would advise to think very carefully about the concepts in the business and how they relate to each other before designing the tables, as this is the bread and butter of the database - if it is not built correctly from the foundations it will quite quickly collapse.

 

[GPGeorge]

Cross posted at Microsoft Answers.

 

[LarryE]

It sounds like you have:

1. Multiple suppliers of raw material that assign batch (lot) numbers to each shipment of any particular raw material
2. Which becomes your raw materials inventory
3. Which is used by your company to use in various recipies to produce various baked goods
4. Which become part of a finished products inventory for sale

Is that correct? If it is approximately right, then I can see a table structure something like below, although it is missing a Sales table for the moment.

In this scenario, multiple vendors contribute to multiple PO's, which contribute to multiple items in Raw Material inventory, which contribute to multiple recipes, which contribute to multiple products for sale in the Inventory table. I just guessed that Lot # (LotNum) is assigned by the vendor and you only know that number when material is received. I don't know if that's true, but you didn't say where the Lot numbers come from or where they are produced. Maybe this can help you at least get on the right track. I agree that it is way too early to talk about forms, reports or any other database objects except tables and what fields are in them. Get the table structure done first. You can easily add/delete/move fields if you need to at this point.

 

[ADIGA88]

Programming an inventory management system isn't an easy task, especially if it's integrated with warehouse management or a full document cycle (sales/purchase). but if you need a stand-alone and simple inventory system you can look into the Access Northwind database, you need may need to do some trimming to the application like removing the sales and acquisition functionality and simplifying it to be only inventory management.