Oganization Problem

[jer84]

Hello

I would like to be able to keep a list of all the parts that are on a
piece of equipment. I have a table of all the equipment (about 50) and
a table of all the parts (hundreds). This would be a many-to-many
relationship since each peice of equipment can have any number of parts
and each part may be found on any number of equipment. How do I relate
these two?

My first thought was to make a table for each peice of equipment, but
I'm thinking that there has to be a better way. i've never related a
many-to-many before. I'm obviously new to Access and would like to
learn how this can be done.

Thank you for you help

Jer

[Jeff]

Hi Jer

You need a table for the equipment, another for the parts, and a third to
make the many-to-many relationship.

The third table needs only the id of the equipment and the id of the part,
and potentially a quantity field, notes etc. Think of it as a BOM (Bill Of
Materials). If you are tracking bolts for example you would need the
quantity field. Notes are always handy.

The relationships between the equipment and parts tables to the third table
would be the usual one-to-many.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"jer84" <je*******@gmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Hello

I would like to be able to keep a list of all the parts that are on a
piece of equipment. I have a table of all the equipment (about 50) and
a table of all the parts (hundreds). This would be a many-to-many
relationship since each peice of equipment can have any number of parts
and each part may be found on any number of equipment. How do I relate
these two?

My first thought was to make a table for each peice of equipment, but
I'm thinking that there has to be a better way. i've never related a
many-to-many before. I'm obviously new to Access and would like to
learn how this can be done.

Thank you for you help

Jer

[jer84]

Hi Jeff

Thank you so much. I couldn't think of how to manage with just the two
tables. This will help so much and save me from making an an absurd
database...

So the third table would hold for example:

equipment1 part1
equipment1 part2
equipment2 part2
equipment2 part3
etc.

This is what you mean, right?

Thanks again
Jeremy

[jer84]

One more thing:

What would I use as a key? An auto-number?

Thanks again

Jer

[Smartin]

jer84 wrote:

One more thing:

What would I use as a key? An auto-number?

Thanks again

Jer

Dead on, on both guesses. You /could/ opt to use equipment and part to
create an index to ensure uniqueness in lieu of autonumber, but this
method is a but more difficult to maintain.

--
Smartin

[Bob Quintal]

"jer84" <je*******@gmail.comwrote in
news:11**********************@p79g2000cwp.googlegr oups.com:

Hello

I would like to be able to keep a list of all the parts that
are on a piece of equipment. I have a table of all the
equipment (about 50) and a table of all the parts (hundreds).
This would be a many-to-many relationship since each peice of
equipment can have any number of parts and each part may be
found on any number of equipment. How do I relate these two?

My first thought was to make a table for each peice of
equipment, but I'm thinking that there has to be a better way.
i've never related a many-to-many before. I'm obviously new to
Access and would like to learn how this can be done.

Thank you for you help

Jer

Basically, you create two tables, PARTS, which contains one row
for each part, and a second table which contains
ParentPart,ChildPart,Quantity and optionally a reference
designator or serial number. I've seen this named Assmbly, BOM
or Structure. The primary hey for this is an index on both
ParentPart and ChildPart

parentPart ChildPart Qty
Part1 Part2 1
part1 Part3 6
Part2 Part3 12

A ParentPart may also be a childPart, and the issue is to go
through the structure, calling out each child recursively until
there are no more children. You can write this off to a
temporary table or to a treeview list, or just print it out.

Part1
..Part2,1
...Part3,12
..Part3,6
etc.

research info on creating a Bill of Materials database.
http://groups.google.com/group/comp.databases.ms-
access/browse_frm/thread/297c01f1d5257aef/818b9d061280179e?
lnk=gst&q=quintal+Explosion&rnum=3#818b9d061280179 e

is also http://tinyurl.com/ntfgx

shows one way to do that. iirc, the code needs a little
tweaking.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Jeff]

You could add a no dups index using both fields. That will stop you
inadvertently adding a part more than once. However, sometimes this is
valid. For example, adding multiple parts the same with unique notes about
installation would require the ability to add a part more than once. So your
call on that.

As far as a primary index, always a good idea to have one. But remember that
indexes should only be added if useful. Indexes have to be maintained and
consume both space and time.

If you don't have the no dups index I mentioned above then probably add a
auto number field for this. But what will you use it for?

I can't imagine why you would add any new tables related to this one!

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"jer84" <je*******@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

One more thing:

What would I use as a key? An auto-number?

Thanks again

Jer

[jer84]

Thank you for all the help. It's working great!