It's relationship time

[Walter Donavan]

I added a Books table and form to my software database and began filling out
the form. I now have a table of Program CDs and a table of Books. However,
the tables have relationship problems.

a.. Book titles are duplicated in the Programs and Books tables. The
titles should probably appear only in the Books table?
b.. CD titles are duplicated in the Books and Programs tables. CD titles
should probably only appear in the Programs table?
c.. Some books refer to more than one CD.
d.. Some CDs refer to more than one book.
e.. Some CDs contain more than one book (e-books).
f.. Some books are on CDs (e-books); others have one or more CDs (e.g.
practice files).
g.. I don't know how to define the relationships so I can remove the
redundant data entries.
a.. I don't know if I need sub forms (I think so).
b.. I don't know how to get the Books table to have a primary key CD ID
AutoNumber field that is the same as the one in the Programs table.
c.. I don't know how to get the Programs table to have a primary key
Book ID AutoNumber field that is the same as the one in the Books table.
?

 

[Walter Donavan]

Forgot to mention it's Access 2002.

 

[John Vinson]

I added a Books table and form to my software database and began filling out
the form. I now have a table of Program CDs and a table of Books. However,
the tables have relationship problems.

a.. Book titles are duplicated in the Programs and Books tables. The
titles should probably appear only in the Books table?
b.. CD titles are duplicated in the Books and Programs tables. CD titles
should probably only appear in the Programs table?

Correct to both.

c.. Some books refer to more than one CD.
d.. Some CDs refer to more than one book.
e.. Some CDs contain more than one book (e-books).
f.. Some books are on CDs (e-books); others have one or more CDs (e.g.
practice files).

You need MORE TABLES. Whenever you have a many to many relationship,
you need to add a new table to model that relationship. The BooksCDs
table (for example) would have the BookID (the Primary Key of the
Books table) as a foreign key to Books, and the CDID as a foreign key
to CDs, and these two fields could constitute a joint two-field
Primary Key.

g.. I don't know how to define the relationships so I can remove the
redundant data entries.

Probably by adding the new table or tables.

a.. I don't know if I need sub forms (I think so).

Subforms are very useful tools with which to enter data into the
"many" side of one to many relationships so... you probably do. But
subforms don't contain data nor do they define relationships; they're
just useful tools!

b.. I don't know how to get the Books table to have a primary key CD ID
AutoNumber field that is the same as the one in the Programs table.

A Foreign Key must NOT be an Autonumber. If it's intended to link to
an Autonumber, use a Long Integer (and make it the Child Link Field of
a Subform, with the mainform being based on the "one" side table).

c.. I don't know how to get the Programs table to have a primary key
Book ID AutoNumber field that is the same as the one in the Books table.

You don't. You need the third table.

 

[Walter Donavan]

Thank you, John. I printed the thread and I will work my way through it. As
it turns out, my silly little database is becoming a good way to learn
Access.