Table Design Help

[JKJQW]

I have a database to track part numbers in an engineering dept.
The primary key is part_no and one of the fields is called layout_no. I need to set it up so that the same part can be a part in many different layouts. I then need to produce a report based on the layout_no for a bill of materials. Just not sure how to approach this one. Is some kind of a relationship the way to go?
Thanks for any help.

 

[Mile-O]

The primary key is part_no and one of the fields is called layout_no.

don't make part_no the primary key; in fact don't call the field part_no i.e. don't use the _ in a field name as it's a better practice to condense words and capitalise the first letter i.e. PartNo

Make a new field:

PartID (autonumber)
PartNo (indexed - no duplicates)
LayoutNo - ?

 

[JKJQW]

thanks mile
Too much water under the bridge to change that stuff now.
Any ideas about the second part of my question?

jkjqw

 

[Mile-O]

Knowing that a field is called layout_no is not enough information. I don't know anything about it.

Is it a foreign key ?
Is it dependant upon the part_no ?
What data type is it ?
Can there be many layout_no with respect to one part_no ?

 

[JKJQW]

Not sure what a foreign key is.
It is not dependent on the part_no.
data type is text.
there can be many layout_no with respect to one part_no.
think of the layout_no as an assembly drawing with many parts.
and part_no as a single component part
part_no 123 can be in layout_no 456 and 789 and so on.
as it is now layout_no is a field in the same table that part_no is the primary key but I'm open to changing that aspect of it.

 

[Mile-O]

Not sure what a foreign key is.
It is not dependent on the part_no.

A foreign key is a field that is the primary key in another table - a relational term.

data type is text.
there can be many layout_no with respect to one part_no.
think of the layout_no as an assembly drawing with many parts.
and part_no as a single component part
part_no 123 can be in layout_no 456 and 789 and so on.
as it is now layout_no is a field in the same table that part_no is the primary key but I'm open to changing that aspect of it.

As you've identified a one-to-many relationship you now want a table for Layouts.

LayoutID
Layout_no
other details pertaining to layout.

The Layout_ID or layout_no wouldn't be in the table you have but the part_no would appear in the Layout table.

Unless, of course, one part can appear in many layouts, and one layout can have many parts. Then that is a many-to-many relationship that needs simulating and there are a lot of examples on this forum to be downloaded.