Linking Tables

[Hank in KC]

I have two tables, one with CD titles and the other with song titles. Since
this is a many to many relationship I have a third table which contains a
one to many relationship with each of the other two tables. This third table
has a CD-ID and a Song-ID for each record.

Each record in the Song table has a box to the left of it with a +. Clicking
on this plus sign brings a drop down box with the IDs of the CDs containing
this song. This is good, as I can easily add a new ID for a CD without going
to an entry form.

I constructed a similar database, I thought, for another application ...
LPs. (I know, pitch them out!) This time, however, when I click on the +
sign I get a dropdown box with ALL the IDs of the LPs, not just the IDs of
the LPs containing that song.

Can anyone suggest how or what I should do to fix this?

TAI

Hank

 

[Guest]

Hank:

Sounds like the relationship might be based on an OUTER JOIN rather than an
INNER JOIN. Open the relationships window and right click on the join line
between the two tables. Select 'Edit Relationship' from the shortcut menu.
Click the Join Type button and make sure it’s the first option selected;
'Only include rows where the joined fields from both tables are equal'.

Ken Sheridan
Stafford, England

 

[Hank in KC]

Thanks Ken,

Tried that earlier, but checked just in case I missed something at first. No
such luck, my problem remains.

Hank

 

[Guest]

Hank:

In that case the data itself comes under suspicion. What you see when you
click on the + sign in datasheet view is a sub-datasheet. Its much like a
subform in a form and shows the rows in the referencing table (the one which
models the many-to-many relationship between LPs and Songs in you case) where
the foreign key matches the primary key of the current row in the referenced
table (Songs in your case). If the sub-datasheet for each row in the songs
table shows all the LPs from the LPs table this would suggest that the third
table has rows for all combinations of Songs and LPs. If this is the case
then something has clearly gone wrong in entering the data into the third
table, if not it sounds like the database is corrupted.

What happens when you open the LPs table in datasheet view BTW? Do you get
just the songs from each LP in the sub-datasheets or do you get all songs in
the database for each LP row? If you get the former this points to the
database being corrupted as you'd get the latter if it’s the data in the
third table that's wrong.

If the third table correctly contains only those combinations of Song-ID and
LP-ID where the song is on the LP in question then you can try repairing the
database, but if this doesn't cure the problem the best thing to do would be
to import the tables into a new blank database, recreate the relationships
and hopefully it will work correctly.

Ken Sheridan
Stafford, England