Unique Indexes - Multifield Primary Key

[megan]

Hi.
I'm trying to set up relationships between 2 tables. A product table that has a multi field primary key. And the individual tables that comprise those fields ==>category table [catID], measure [measID], etc.
When I try to create the relationship I get an error message saying no unique index. Each product has several widths and measures per description. I'm going to need to roll these into price sheets based on customer as well, so it's gotta be easy. And easy for the other users to understand when entering new data.
thanks.
Megan

 

[Scott McDaniel]

Do your individual tables have unique indexes (i.e. Primary Keys)? You must
have a primary key in order to create a relationship. From the sound of it,
you are indicating that you'll have a "many-to-many" relationship between
your tables. If that's the case, you'll need a "linking" table, a table
which stores the primary key of your first table with the primary key of
your second table to indicate relationships.

For example, if Table1 has a PK of lngWidgetID, and Table2 has a PK of
lngSubWidgetID, your linking table would be a combination of those two
fields (uniquely indexed) ... so therefore if Table1(1224) is comprised of
Table2(1111) and Table2(998), your linking table would contain two records:

tblLink
Field1 Field2
1224 1111
1224 998


--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

Hi.
I'm trying to set up relationships between 2 tables. A product table that

has a multi field primary key. And the individual tables that comprise those
fields ==>category table [catID], measure [measID], etc.

When I try to create the relationship I get an error message saying no

unique index. Each product has several widths and measures per description.
I'm going to need to roll these into price sheets based on customer as well,
so it's gotta be easy. And easy for the other users to understand when
entering new data.

 

[Arvin Meyer]

The Product table (with the multi-field PK) must be the foreign key table in
the relationship. The Category table will contain the PK.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hi.
I'm trying to set up relationships between 2 tables. A product table that

has a multi field primary key. And the individual tables that comprise those
fields ==>category table [catID], measure [measID], etc.

When I try to create the relationship I get an error message saying no

unique index. Each product has several widths and measures per description.
I'm going to need to roll these into price sheets based on customer as well,
so it's gotta be easy. And easy for the other users to understand when
entering new data.