Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Linking Tables

Discussion in 'Information Technology' started by Hank in KC, Jul 28, 2006.

Tags:
  1. Hank in KC

    Hank in KC
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    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" wrote:

    > 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
    >
    >
    >
     
  4. Hank in KC

    Hank in KC
    Expand Collapse
    Guest

    Thanks Ken,

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

    Hank

    "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    news:453EF267-4A98-44C3-BF76-703A75680745@microsoft.com...
    > 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" wrote:
    >
    >> 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
    >>
    >>
    >>

    >
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    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

    "Hank in KC" wrote:

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

Share This Page