Create a relationship between tables based on concatenated fields

Question

I am trying to create a relationship between two tables based on multiple fields. In Excel, I would use concatenate to combine the field names into one unique value to do my vlookup. For example, I would lookup City and Department using a combined CityDepartment. How can I make this link similarly in Access tables? If I create a relationship just between City and just between Department I do not get that unique CityDepartment.

If I wanted to do a JOIN on multiple fields...why wouldn't something like this work? I am getting a syntax error.

SELECT HRBI.ID (For all fields)
FROM HRBI 
LEFT JOIN MINC
ON HRBI.PayGroupRegionCode = MINC.Region AND HRBI.PayGroupCountryDesc = MINC.Country AND HRBI.[BusinessLvl1(Group)Code] = MINC.BusinessGroup;

There is no reason why a relationship should not use two fields (columns) as long as both will always contain data. — Fionnuala, Sep 09 '15 at 15:25
You might like to see http://stackoverflow.com/questions/12145258/distinct-row-based-on-two-fields — Fionnuala, Sep 09 '15 at 15:37
Can you take a look at my JOIN code? What's wrong with the syntax? — Chris2015, Sep 09 '15 at 15:46
Do you not have use of MS Access query design window? Why left join when all you want is matches? — Fionnuala, Sep 09 '15 at 15:51
I have use of query design window, but I am not sure what you mean... — Chris2015, Sep 09 '15 at 15:56
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89184/discussion-between-fionnuala-and-user1813558). — Fionnuala, Sep 09 '15 at 15:57

score 0 · Accepted Answer · answered Sep 11 '15 at 08:50

I was able to combine the two fields by creating the relationships in Design View. I then created a query and edited the SQL to the following:

  SELECT HRBI.PayGroupCountryDesc, HRBI.[BusinessLvl1(Group)Code], MINC.Country, MINC.BusinessGroup, MINC.MINCPercentageofSalary
    FROM HRBI LEFT JOIN MINC ON (HRBI.[BusinessLvl1(Group)Code] = MINC.BusinessGroup) AND (HRBI.[PayGroupCountryDesc] = MINC.[Country])
    ORDER BY HRBI.PayGroupCountryDesc;

No issues after that.

Create a relationship between tables based on concatenated fields

1 Answers1