Bill of material part 2

[EVA]

Hello, I am still struggling with trying to build a BOM. The situation is as
follows:

I have 39 brandnames which are made from 7 different assemblies.

22 Brandnames are made from Assembly 1.
8 Brandnames are made from Assembly 2.
Assembly 3, 4, 5 and 6 each contain 2 brandnames
Assembly 7 contains 1 brandname.

The criteria of a brandname belonging to one of the 7 assemblies is based on
assemblyname and packsize.

I like to keep the assemblies in separate tables (7 tables) and the
Brandnames in one table. This is for when I need to include a new assembly it
would be easier to intergrate the assembly.

I would like to build a form where depending on the assemblyname and the
packsize I can display one Brandname including the full assembly.

Can this be done in Access? Can someone give me some tips how to do this?

Thank you in advance,
Kind regards,
Elena

 

[scubadiver]

"I like to keep the assemblies in separate tables (7 tables) and the
Brandnames in one table."

If you write that kind of statement you will get hammered unless you have a
justification for it because it isnt good normalisation.

How would you propose to relate the brandname table to the assembly tables?

 

[FredFred]

You'd have to clarify what (in structural manufacturing terms) you mean by
"brand Name". Is it an assembly (of one or more items) or is it an item,
or is it merely an attribute of one of these items. Also, the same for the
vague term "made from". Possibly the process of having to nail
that down would in itself be a main part of the answer.

But I can sat that I've never seen a separate table for each assembly be a
good idea. Usually a sparse "BOMItems" table where a record is an instance
of a part being used for an assembly is a good way.

 

[EVA]

Ok, I will try to explain. First I think the Brandname title was complicated
I will name it producttype.

So I have 8 tables

tblBrandname
Brandcode
Partnumber
Description
Productype
Packsize

tblAssembly1
Partnumber
Description
Producttype
Packsize

tblAssembly2
Partnumber
Description
Producttype
Packsize

All other assemblies are like assemby 1 and 2

What I want to do is have two joints from tblBrandname to the Assembly
table. Depending on the Producttype and the Packsize a list of Brandcodes
should be made visible. Then if I select one of the Brandcodes, I would like
to see the specific Brandcode and the assembly in a form (This will be my
Bill of Material)

Basicaly I want to query the tables every time and display the right
Brandcode with the right assembly as a subform.

Is this possible in access?

Thank you in advance,
Kind regards,
Elena

 

[FredFred]

Hopefully my shortness / directness is helpful and does not sound rude.

A few things are still implied in your explanation, but as I understand it,
each "Brand Name" is made from one "assembly". If so, in manufacturing /
BOM terms for the info at hand, your "BrandName" is an assembly, and your
"assembly" isn't an assembly, it's an item. But I'll use your terminology.
And since the most complex BOM (within this info) consists of one piece,
this is more of a simple "one-to-many" (one assembly used in many brandnames)
relationship than a BOM situation.

If I understand you properly, then your "table per pert" approach is wrong
by any accepted database design practices and is making this ten times harder
than it needs to be. You should have just two tables. A "BrandName"
table which is what you have already except an extra field which says which
"assembly" it uses.

And then you need one "assembly" which has the same fields as your
assembly tables plus one additional one for the saaemblynumber. And enter
each assembly as merely on record in that table. Then link the assy fields
in the two tables (e.g. in a query) to each other and then you're done.

Then use that query as a source for a form and I think it will be easy.

Again, hopefully my shortness / directness is helpful and does not sound
rude.

Sincerely,
Fred

 

[EVA]

Hi Fred,

Don't worry about directness and shortness if it will help to solve the
problem it is fine by me.

Just for more clarification, what I mean by assembly is:

Brandname A consists of Assemby 1 which consists of :

x amount of blisters
x amount of foils
x cartons
x labels
x shippercartons
etc.

The reason that I want to keep each assembly in separate tables is because
if a new assembly comes up for a new Brandname, I will have to add each part
of the assembly manualy in to the list. If there will be, for example, 10
brandnames attached to the assembly I will have to add the same parts the 10
times in the table. So if I can make a program that checks the assembly type
first and then attaches the brandname to the assembly in the appropriate
form, that will save time. Also what happens often is that there is a change
to the artwork of the Brandname, so the brandname number changes while the
assembly stays the same. In that case I am manualy adjusting all the
brandnames to join the correct parts of the assembly. I know I am making it
sound very complicated, I have tryed to simplify it but it is not easy.

Thanks for your Help so far

Regards,
Elena

 

[FredFred]

Hi Eve,

Those are valid concerns, but the wrong way to solve them.

Now I see that your "assemblies" really are assemblies in whihc case you
have a multi-level BOM situation, "How To" do those in DB's is more like a
book than a post. But here's main thoughts on a way:

Create a "items" table that has a "Part number" and a description for EVERY
item. Every Brand Name, assembly, foil, label etc.

Create a "BOMitems" table which lists each instance of an item being
DIRECTLY used to make an item. Just three fields:

AssemblyNumber
Quantity
ItemNumber

The part number and quantity of the item used, and the part number of the
assembly it's used in. So, each BrandName will be covered by one record,
(it's built from one item, your "assembly" and each of your "Assemblies"
will be covered by about 5 reaords which is the partnumbers and quantities of
the foils, labels etc. used to build it.

Then link "itemNumber field in BOMitems table to "PartNumber" field. Then
your forms and reports should easly shake out once that's done. (Group
on assemblynumber in the reports)

Sincerley,

Fred

 

[EVA]

Hi Fred,

Thank you for this I will try it over the weekend. If I am stuck, can I
still try to ask more questions to you on Monday?

Regards,
Elena

 

[FredFred]

Hi Elena,

Happy to do it. But it's going to be hard to watch for it. You might want
to email me (home email) to tell me if you put another question for me on
the forum.

tureks
@
ameritech.net