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.

 

[Pat Hartman]

Most of the multi-million dollar mainframe applications I developed in a former life could have been done with Access. The biggest limitation with Access is actually the number of developers needed to complete the project in your timeframe. The other limitations are the amount of data needed to be stored and the number of concurrent users both of which can be resolved by switching to SQL Server for the BE.

Access is very difficult as a development platform if you need to use multiple developers to meet your deadline. Not impossible, just difficult.

Even with that, very few large companies would even consider a major application built with Access but I have several clients who run their entire businesses using a purchased Accounting system and custom built Access applications for everything else. So, as long as you have the skills and the time, you can do this. It is sometimes easier to teach a technically savvy user how to build an Access application than to teach a programmer enough about how your business works to get a usable application out of him.

As others have mentioned, the schema is the heart of the application. If you can get the data defined and normalized correctly early on, you will have a sound foundation on which to build your interface. So, don't give into instant gratification. Spend enough time to work out what data you need to keep and how it is all related. If you find yourself thinking, I have three of these and twelve of these and 4 of the other things and you start defining fields with suffixes, you are on the wrong track. Whenever you have more than one of something, you have "many" and "many" requires a separate table. If you were making a Christmas card database for yourself and you decided to track the names of the children of your friends, you might think, "I'll just add four name fields". None of my friends has more than two children. And then you make a new friend who has five children. What are you going to do?

Naming standards are important but nowhere near as important as structure so we'll tell you how to name your objects to help you to minimize your issues later. Consistency is your friend. But, we need to start with your schema. You can post the whole database if you are comfortable with that or just the tables (after you obfuscate any sensitive data) so we can see how the tables are related and help you get that straight.

 

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