Strange Relations

[dymondjack]

Hello again, and again TIA for any input.

I'm trying to build some relationships between a few tables create an
effective BOM table for Parts, and I was wonder if someone might be able to
give a second opinion on my setup, as I have never really tried to tackle
something like this before.


A bit of background (brief as I can):

I've got my tblParts, which is fairly straightforward... ID, PartNumber,
Rev, etc, and I want to have a BOM setup for it. The thing is, each BOM item
can be one of three 'types'...

1) a Component Item
2) a Raw Material Item
3) a Purchased Part Item

The Raw Material and Purchased Parts are fairly straightforward as well,
each of the two has its own table (tblRawStocks and tblPurchasedParts), each
with a Autonumber PK. So to handle that I've got two junction tables set up,
jtblBOM_RawStocks and jtblBOM_PurchasedParts, to accomodate the many to many
relationship between Parts and BOM Items. As each of these 'types' has a
completely different set of properties to them, I believe they each need to
be in their own table, therefore my final BOM will be three seperate junction
tables. I do not see this being a particular issue later down the road.




The Problem:

The third junction table that I'm trying to set up is the one that I'm a bit
perplexed about. I need to set up a many to many relationship between
tblParts and tblParts. I've never tried doing this, and have no idea if its
the right way to go about it (I'm new to many-to-many's).

So here's what I have:

tblParts
fldID <-- Autnum, PK
...
...

jtblBOM_Components
fldID <-- Autonum, PK
fldPartID <-- Child of tblParts (the active part)
fldComponentID <-- Child of tblParts (the components of the active part)

I *think* this will allow me to have a Part with a BOM that can contain any
number of other Parts. I'm curious about how this relationship actually
works though. When I work in the relationship window, I've attempted this
two ways:



Method 1)

tblParts jtblBOM_Components
fldID fldID
fldPartID
fldComponentID

First I drag from tblParts.fldID to tblBOM_Components.fldPartID and setup a
one-to-many. Then, I tried dragging jtblBOM_Components.fldComponentID to
tblParts.fldID, and access gives me the option of deleting the existing
relationship or create a new one (or cancel).

If I create a new one using this method, and leave the existing, would this
create the correct relationship?




Method 2)

tblParts jtblBOM_Components tblParts_1
fldID fldID fldID
fldPartID
fldComponentID


So when I add the second tblParts to the relationship view, access suffixes
the table with "_1". I assume this is normal and won't effect anything, but
I don't know for sure. Doing it like this, I can drag from tblParts.fldID to
jtblBOM.fldPartID, and then drag tblParts_1.fldID to jtblBOM.fldComponentID,
and it acts as it normally does with other many-to-many's.

Is there any difference between these two methods? Unfortunately I know
very little (nothing, actually) about the 'inner workings' of relationships,
and have no idea if one was is better than the other (or even I should be
trying to set relations between the same table??).

If anyone has every run into a situation like this before and can offer some
advice on whether this is a valid relationship or not, I would be extremely
appreciative.

Sorry for the long post. Thanks again!! (hopefully my 'relationship' views
display correctly with the word wrap...)

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[dymondjack]

I had considered putting everything into one Materials table (which would
contain Parts, Raw Stock, Fasteners, Hardware, basically every tangible
object in the facility), and trying to sort it out from there, but had
thought that I might be better off with different tables for different types
based on the differences between each type.

I'm not quite sure how I would handle the variations in required fields to
store the pertinent information for each type.

I'll definately give this approach some more thought, and if you do happen
to come across an example, I would be happy to see it.

thanks

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[AlexM]

Sorry my brain expired reading the email(too much detail).

But one thing that stuck in my mind is that you have separate tables for Raw
materials, Purchased, Components.
You should keep all of them in the same table with a field PartType or Class
(R, P, C) or something like that.
Esentially all of the parts go into the same table, Those that are finished
goods, have components, those that are components might be raw material (no
subcomponents) or might have subcomponents.
You need to create a function that will tell you for each part how many deep
it is.
Then you have to run a cross tab to make it happen.

I remember doing this about 2 years ago.
If I can find this info, I will post it.

 

[CrazyAccessProgrammer]

I'm not sure about how to answer your core question regarding the many to
many relationship. But like a previous post wrote, I recommend against
putting your different inventory types into separate tables.

I own a machine shop and spent 5 years putting together an MRP System and I
broke out our inventories into different tables (1 table for parts we
manufacture, 1 table for standard hardware that you can purchase, 1 table for
Raw Materials, and finally 1 table for what I called consumables which is
essentially anything that you buy that is a liquid).

While doing this made sense at first, down the road as my database grew in
functionality, having to deal with the separate tables quadrupled work for
any other function you may add to your database down the road that deals with
inventory.

I recomend keeping your inventory of all types in a single table and here is
how I would do it:

Essentially, all inventory items share in common the following:

1) Number
2) Nomenclature

For Manufactured Parts, Purchased Standard Hardware, and Consumables it
works very straight forward as shown:

Manufactured Part Example

Type Number Nomenclature
Manufactured Part 65B04355-59 Cam Latch
Standard Hardware NAS1075P Rivet
Consumable BMS 10-11 Primer

Raw Material was tricker because there is more information than a number and
a nomenclature. For our business a typical raw material descriptor would be:

7075-T7351 Aluminum Round Bar Per QQ-A-250/12

There are 5 Elements to this description
1) 7075 (Type)
2) T7351 (Temper) Optional
3) Aluminum (Description of the material)
4) Round Bar (Shape of the Material)
5) QQ-A-250/12 (Quality Standard Material Fabricated To)

So what I would do for raw material is when a user flags the inventory
record as being raw material a popup window would appear providing a field
for each of the 5 Elements - fields that are only used when an inventory item
is raw material.

After the user fills in the fields they close the popup window and the
parent form would then display as follows

Type Number Nomenclature
Raw Material 7075-T7351 Aluminum Round Bar Per QQ-A-250/12

When the inventory item is flagged as raw material the fields for Number and
Name are disabled because they are controlled by the elements the user fills
out in the popup window.

In a nutshell, most anyting you can think of that you purchase or make in a
manufacturing environment has, at minimum a number and name. Items types that
have additional elements to them you create a popup window to enter those
elements, and then thru VBA you concatenate those elements in a way where
they fit the Number and Name fields.

Also, if you take this approach, I'd recomend using separate checkboxes for
the user to indicate the inventory type. For us that would be a separate
checkbox for each of the following:

1) Manufactured Part
2) Standard Hardware
3) Raw Material
4) Consumable

In our company, many times we may purchase a standard hardware item. But
once in a while where we'd normally purchase a standard hardware item we
instead manufacture it ourselves (usually becaue the item has an unacceptable
lead time or we just can't find it). Being able to flag an inventory item as
more than one type has its advantages.

Anyway, that's how I'd do it. Saves a lot of headache down the road to have
inventory in one nice big table and it will also make analyzing history
regarding your inventory so much easier. And with any luck, maybe this would
get rid of your whole problem with the many-to-many problem you are wrestling
with now.

 

[dymondjack]

Thanks for the reply, this has really hit the nail on the head for my more
unspoken question. While I'm still unsure exactly how to handle the
situation, I think I will drop the idea of using separate tables for
different types of material items and put my effors towards handling them all
in the same table.

I do believe this will rid me of the requirement to setup a many-tomany
relationship against a single table.

thanks again
--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[pietlinden]

not much to add here, but before you go reinventing the wheel, start
with something that's at least reasonably round... There are a couple
of articles on this website about bills of materials, including Joe
Celko's nested sets. Might be a good place to check out before you
make your final design... might save you a bit of headache.

http://www.mvps.org/access/modules/mdl0027.htm

 

[dymondjack]

Thanks for the links. Yesterday morning I downloaded the BOM db from mvps
(after having it catch my eye for about two years) and played around a little
bit. I'll continue playing with it until I fully understand it. I had not,
however, found the bit on queries by Joe Celko. It is now bookmarked for
when the slate is wiped and I am ready to start.

Thanks for the tips


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[AlexM]

Ultimately it's not a deal breaker if your different components are in
different tables.
You can always run a union to get one table.
You can store specifics about each component in a separate table as long as
every sku has an id you can look it up. and every table should have a primary
key.
I found the db I worked on few years back.
Send me an email to alex { at } milojko [dot] com
and I will email you what I did.

 

[dymondjack]

Ok, so based on all of your input, I think I've finally got a semblance of a
plan forming in my head for this.


I'm going to go with one base table for all inventory items, which will hold
only information that is common for every item, regardless of its Type. I'll
throw a type field in there for easy sorting later on. This should keep
things fairly basic at a general level, and allow me to uniquely identify
every item across the board.

As for the details of each item type, I'll set up some related tables based
on the type. Some of these will wind up being really basic, with just some
related info on details for that particular inventory item. Othes will be
much deeper, such as Manufactured Parts (assemblies) and Raw Materials.

I think I'll need to structure a lot off this base table, such as methods of
manufacturing for Parts, transactions for purchased items to record lot
numbers/material certifications etc, for each lot coming in, on hand
quantities, etc.. I'll need to do some heavy research on inventory handling
before I start actually laying out tables, but at this point I think I
actually have something decent to start with.

Its funny, the first project that I ever did with access was a converter to
read the programming code of a particular manufacturing machine, and convert
it to readable code for machines based off different language standards.
Admittedly, this was almost all done in VBA, with Access being nothing more
than a host really, but at the time I thought that might have been the most
difficult programming project I'd ever have to take on. That was pretty pale
in comparison to the design structures required for something along these
lines.

This has certainly been an interesting and challenging subject. Thanks
again for all of your input. Once again the people here have allowed me to
give much consideration ahead of time to what would have cost me countless
hours of trial and error (not that I won't have countless hours of trial and
error ahead of me on this one, but I'm sure this has reduced the number).

Thanks!!

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain