AnsweredAssumed Answered

Show fields from another table that match different fields

Question asked by raymond on Jan 15, 2010
Latest reply on Jan 15, 2010 by philmodjunk


Show fields from another table that match different fields


I have a table (called Employee Records) with employee name and interviews they attended. Same employee has attended five different interviews at five different locations. In the employee table these are stated as Location 1, Location 2 etc.


The Location field is looked up from another table (called Locations) containing the location name, address, city, post code etc.


On a new layout I wish to print a list of the employee name, Location name (from Location 1) and the matching address & city (from table Locations). Then on the second line Location name (from Location 2) and the matching address & city (from table Locations) and so on for all five locations. If say locations 4 & 4 are blank then the address & city will be blank.


I have managed to link the relationship between Employee Records table and Locations table with Location 1 and Location Name. But I cannot create any relationship between Location 2 and Location Name because it only allows on field to have relationship between both tables.


How do I get the address & city for say Location 2 when I cannot link? I do not want to create any more fields in table Employee Records that look up Address 2, City 2 etc because these are only to print the list rather than for any other use.