Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014

    Unanswered: 3 fields one table linking to one field in another

    Hi guys,

    I have only done one database unit back at uni and because of this I am now in charge of completing a database the company I am working for will use statewide. I have one problem however in that I am trying to link 3 fields in one table to one field in another. I want 3 road names / intersections in one table that all link to different rows from a road / intersection table (Attached is the tables to help you understand if I am not getting my point across). Is there a better way to go about this or how would I link these tables together?

    P.S. All the mentions of road / intersection in the location table are different i.e. Road/Intersection, Start Road and End Road are all road names but are different.

    Click image for larger version. 

Name:	location problem.png 
Views:	5 
Size:	7.1 KB 
ID:	15905

    Thanks for any help, Dan.

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 34
    you can only link 1 field to 1 field. If you need to do more, then you need 3 queries, each with different links.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    ...or join the location table to tjhe road intersection table 3 times (once for each required element)

      FROM [Roads / Intersections] as RT
      left join Location as LT1
        on RT.[Road Name / Intersection] = LT1.[Road/Intersection]
      left join Location as LT2
        on RT.[Road Name / Intersection] = LT2.[Start Road]
      left join Location as LT3
        on RT.[Road Name / Intersection] = LT3.[End Road]
    whether that would work right know I dunno, mainly because my Joins in Access are a bit rusty (Access/JET uses its own syntax for joins) however you can do this yourself using the query designer (its one of the few times the query designer pays for itself in my books. place the location table on the designer 3 times, drag and drop from the roads / intersections table to each instance of the location table specifying that you want all the values from the roads / intersection table and whatebver rows match from each instance of the location table.

    however that does presume that you can actually use / as a symbol inside a table or column name. suggests not.

    I'd strongly recommend that you stick to the guidelines for naming columns and or tables..
    dont use spaces
    don't use reserved words
    use either capitalisation or underscores to separate words (eg EndRoad or end_road).
    don't be overly verbose in naming columns or tables (use abbreviations where they make sense

    of course you could take all the fun out of the process by normalising your design
    have a read of:-
    then have a look at:-
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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