Relationships

[Daniel]

I have a database that is a library of CD's A table
called Volumes has each CD listed and where it is located:
ID; CD Name; Location. ID is formatted as an Autonumber,
CD Name is formatted as text and each value is unique;
there are no duplicates.

Another table called Holdings lists each file and the CD
on which it located: ID; Filename; CD Name. ID is an
Autonumber and CD Name corresponds to where the file is
stored. CD Name can have many duplicate values for more
than one file fits on a CD.

I want to link the tables either by ID's or CD Name's and
preserve data integrity so that I may add or delete as
needed through queries, forms, etc.. When I try to
establish the relationship, I can not set it as many to
one (ie, Holdings as primary and Volumes as second) or
vice versa. Access always thinks it is one to one. I can
not select to endoforce data rules for Access tells me the
ID field or the CD Name is not unique enough.

Are there any good resources or tutorials for this? I
have an Access reference book and it gives a good step-by-
step using the Northwind sample but when I try to apply
what I learn there, it doesn't work.

 

[David]

You need to link Volumes to Holdings on the Volume ID
column. Don't have CD name in Holdings put Volume Id in
there and link the two tables on that column. Have CD name
only once in the Holdings table. Why not call it CD
instead of Holdings - which is confusing.

 

[Greg Kraushaar]

I have a database that is a library of CD's A table
called Volumes has each CD listed and where it is located:
ID; CD Name; Location. ID is formatted as an Autonumber,
CD Name is formatted as text and each value is unique;
there are no duplicates.

Good So far.

tblCDList
CDID PrimaryKey Autonumber
CDName Not Primary Key, but unique index
CDLocation could be pointer to a list of locations

Another table called Holdings lists each file and the CD
on which it located: ID; Filename; CD Name. ID is an
Autonumber and CD Name corresponds to where the file is
stored. CD Name can have many duplicate values for more
than one file fits on a CD.

Not So good Should be
tblHoldings
HoldingID PrimaryKey AutoNumber
CDID Indexed, but Duplicates OK Long Integer FKey to tblCDList
FileName

Create your Link From the PrimaryKey of tblCDList to the Foreign key
of tblHoldings (tblHoldings.CDID)

I want to link the tables either by ID's or CD Name's and
preserve data integrity so that I may add or delete as
needed through queries, forms, etc.. When I try to
establish the relationship, I can not set it as many to
one (ie, Holdings as primary and Volumes as second) or
vice versa. Access always thinks it is one to one. I can
not select to endoforce data rules for Access tells me the
ID field or the CD Name is not unique enough.

You are linking two tables by fields that have Unique Indexes
This will always create a 1-1 relationship...

Are there any good resources or tutorials for this? I
have an Access reference book and it gives a good step-by-
step using the Northwind sample but when I try to apply
what I learn there, it doesn't work.

Ken Getz's books are thebest I have seen on this and many other
topics.