Relationship Issue

[Jason Lepack]

tbl_parts:
part_id - autonumber - pk
part_description - text

tbl_bom: (bill of material)
bom_id - autonumber - indexed(no dupes)
part_id - number - PK
sub_part_id - number - PK

In this situation how would I create the relationships between the two
tables? I need to enforce referential integrity on both part_id and
sub_part_id in tbl_bom to part_id in tbl_parts.

Is it acceptable to load tbl_parts into the relationship window twice
and link the two fields to the seperate instances? Are there any
unforseen issues with this method?

Thanks In Advance,
Jason Lepack

 

[Roger Carlson]

Yes, that is exactly the way to create two separate relationships between
two tables. The second instance will be called tbl_parts_1, but Access will
reference the correct table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jason Lepack]

Thanks Roger.

Yes, that is exactly the way to create two separate relationships between
two tables. The second instance will be called tbl_parts_1, but Access will
reference the correct table.

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L










- Show quoted text -

 

[Jason Lepack]

Interesting... Good note Jamie.

Goes back and pulls out his University text on Foliage... I mean
trees.

Cheers,
Jason Lepack

 

[Jason Lepack]

Pardon my ignorance for a moment.

I looked at this example that you pointed me to and have a question.
When I add a new item do I not have to modify every record in the
tree? That sounds like a lot of work. Granted I looked at my
original structure and decided that querying it would be quite
disgusting. But the question still exists.

Thanks,
Jason

 

[Jason Lepack]

Ok, so I'm over the fact that I would possibly have to update
potentially over 90% of my recordset every time I add a new part
(depending on how far to the left of the tree I insert into), but I
have another question.

I'm going to end up with the same part (which is built from sub_parts)
occurring in multiple places within this tree. Am I going to have to
include them all each time?

Adjacency Model:
bom_id, part_id, sub_part_id
1, 1, 2
2, 1, 3
3, 3, 4
4, 5, 3

Based on the above model it contains this structure:
1
2
3
4
5
3
4

Tree Model:
part_id, lft, rgt
0, 1, 16 - root of tree
1, 2, 9
2, 3, 4
3, 5, 8
4, 6, 7
5, 10, 15
3, 11, 14
4, 12, 13

I'm not too worried about the addition of the root of tree nodes and
the individual nodes added for the master parts (1, 5) but I'm greatly
disturbed by the fact that I would have to add the extra records when
a part is required in two seperate parts. Is this the case or is
there a way to work around this?

Cheers,
Jason Lepack