Re: Help with table structure for manufacturing production counts

diaare wrote:
Thank you both for your responses.

I understand what you each are saying...and I agree. Unfortunately I work (for only a couple of months now) for a very large company that is still in the dark ages.

Each department has its own system, built on its own platform, and none of them talk to the other. (much of the data is housed on JDE...but I'm still looking for someone who really knows how to access it)

The purchasing department has no system (electronic that is). Each buyer actually keeps track of their own inventory by hand (thats right pencil and paper) in a cardex file.

Actually the spread*** I produce weekly gives them the data they need to update the cardex...it is just a huge pain to input the data into each week.

I was hoping I could (at the bear minimum) create a form in access to ease the time it takes to input the data each week.

Someone, somewhere, on some system has unique ID's for each specific item, and possibly some sort of BOM set up. I am attempting to get my hands on it to see it it is usable, but for now (since I have the time) I thought I would sharpen my access skills by attempting to recreate this very inefficient system in access in the hopes it would at the least, make the data input each week easier.


"gls858" wrote:

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









You have my sympathy :-) I had a similar situation, just a smaller company. When it was sold we went from the dark ages to a fully computerized real time inventory, order entry, accounting, reporting, the works. I helped the owner select the software and was handed a manual and told "make it work", been doing it for 20 years now. If you can get to the right people with some solid ideas and some cost analysis and show them how much money they can save, you could prove to be a very
valuable asset to your new employer.

My skills in Access aren't sufficient to be of much help but there are others here that a quite capable. As a starter you might take a look at
the sample database in the link below. Just look for the one that says BOM. It doesn't have a lot of detail but it may give you an idea on how
to set up the tables and a form. Good luck.

http://www.mvps.org/access/resources/downloads.htm

gls858
.