Re: Help with table structure for manufacturing production counts

Tech-Archive recommends: Speed Up your PC by fixing your registry

diaare wrote:
I am attempting to convert a Production Report currently done (quite ineffectively) in Excel into an Access database I am creating from scratch. Here is where I am stuck…

I currently receive a weekly report from the plant managers that gives Model numbers, quantity built, and how many have each various options (ie: color, glass doors, casters, compressor hp, voltage etc). The buyers in the purchasing department use the data from this report to help them keep track of inventory of their purchased components (for the models and options) and predict what they need to buy in the upcoming weeks.

I currently have these lookup tables set up: Options, Colors, Models, Voltages, Compressors

I began to set up a table to use to house the data that comes from the weekly production reports (that I will then build a form to input the data each week)

Here is what I have in the table so far:

tbl_Production
ProductionID (PK)
ModelID (FK)
ProductionDate
Quantity (I’m not sure about this one)
Voltage
CompHP

And here is where I get mixed up…

My report could say we have 15 of Model A built…1 is 115volt, 14 are 220volt, 3 are red, 5 have casters, and 4 have glass doors etc. Some of the options are required (ie: voltage), some are not.

The buyers don’t care how the options relate…only that they know the totals for each option and which model they are for.

I know that having the fields Option1, Qty1, Option2, Qty2…etc go against the rules of normalization and create problems….but, do I have to have a field in my Production table for each of my options (there are over 80 of them)…and if so, will that mean I will have to have a field for each of them in my form?

Surely there is a way to do this…can someone head me in the right direction?

Thanks
Diane

What you're wanting to do is really quite complex. That's probably the reason why your spread*** is so ineffective. You are trying to
manage a bill of materials needed to complete a manufacturing process. While you may be able to design and build it, its kind of re-inventing the wheel. There are off the shelf products that will do what you need and MUCH more. I did a quick search to see if there was a least a site I could give you just so you could get an idea of whats out
there. I have no idea how large your company is or how much they are willing to spend to make processes more efficient. It sounds like your
company could increase it's efficiency a great deal with the right investment. The link below is just a sample of the software that's available. There are many more with costs ranging greatly depending on your needs. Sometimes it's just better to buy than use homegrown.

Here's a link for a sample:
http://www.pedyn.com/access/bill.htm

Disclaimer: I have no idea the reliability or reputation of the company above. I used it ONLY as an example.

gls858







.

Quantcast