Help with table structure for manufacturing production counts

[diaare]

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

 

[KARL DEWEY]

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).
It sounds like your input data is unusable for inventory purposes.
For inventory you need to cpature how many of each unique item. A red one
with 220volt, casters, and glass doors is different than gree one with
220volt, casters, and glass doors. For every difference you need to
categorize and count separately.

 

[gls858]

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 spreadsheet 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

 

[diaare]

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 spreadsheet 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]

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

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