Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships & Joins (2003)

    I have a problem creating the correct relationship between two tables:

    tbl_Town and tbl_Location

    tbl_Town = TownID (PK), Town, CountryID(FK)
    tbl_Location = LocationID(PK), Location

    One Town can have many locations, e.g. St Marys, St James
    One Location can appear in many Towns, e.g. St Mary Southampton, St Mary Rochester

    So I have created tbl_TownLocation = LocationID(PK), TownID(PK) to join them together

    I then imported the data from Excel:
    tbl_Town has 252 rows
    tbl_Location has 73 rows
    tbl_Townlocation has 98 rows

    I am trying to add this to a larger query:

    tbl_Town connects okay but as soon as I try to bring in tbl_TownLocation it breaks down. I am expecting my query to return 239 rows, instead I get 400

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships & Joins (2003)

    Shouldn't this question be in the Access forum? I can move it there, if necessary.

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships & Joins (2003)

    Hi, Yes... Apologies, brain rot setting in....

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships & Joins (2003)

    This is not really a many-to-many relationship, since the St Mary location in Southampton is not the same as the St Mary location in Rochester. The marriage location should not be "St Mary", but the combination "Rochester St Mary".

    I would omit the tbl_Location table altogether, and use only a modified version of tbl_TownLocation.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships & Joins (2003)

    I am not sure that understand correctly, but I wonder if this is the problem.

    Does your other data (it sounds like marriage records) have location data in it? If so do you want your query to return the town and location of the marriage?

    Then join the Marriage table ( if that is what it is) directly to the Locations table. For this query you can leave out the table tbl_TownLocation. You might need it elsewhere, but not here.
    Regards
    John



  6. #6
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships & Joins (2003)

    Hi

    tbl_Marriage = PersonEventID(PK), TownID(PK), PartnerID(FK)
    tbl_Town = TownID(PK), Town, CountyID(FK)

    These two tables link fine. I get the details of the marriage plus the town it occurred in. Problem arises when I try to add tbl_Location.

    I did originally have this as one table:
    tbl_Place = PlaceID(PK), Location, Town, County but it contained a lot of duplication (repetition of Town and County names) which I was attempting to remove.

    I guess reverting to this (as per Hans V's post) may be where I end up.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships & Joins (2003)

    You shouldn't put Town and County in tbl_Place, but TownID instead. So tbl_Place would have the following fields:

    PlaceID (PK)
    Location
    TownID (FK)

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships & Joins (2003)

    If you have multiple locations in one town, and you want to know what was the location of a marriage, then the marriage table needs to include a locations field, not just a town field.

    You could have a townID and a locationID and then join directly to tbl_TownLocation by joing both fields, or join each separately to the town and locations tables.
    Regards
    John



  9. #9
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships & Joins (2003)

    I used a combination of your suggestions: Adding PlaceID to tbl_Marriage (per johnhutchison) and revising tbl_Place (per HansV). This works fine. Very many thanks for your help.

  10. #10
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships & Joins (2003)

    I used a combination of your suggestions: Adding PlaceID to tbl_Marriage (per johnhutchison) and revising tbl_Place (per HansV). This works fine. Very many thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •