Unique Indexes - Multifield Primary Key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
megan said:
Hi.
I'm trying to set up relationships between 2 tables. A product table that
Click to expand...
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
Click to expand...
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.
 
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

megan said:
Hi.
I'm trying to set up relationships between 2 tables. A product table that
Click to expand...
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
Click to expand...
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.
 
Back
Top