Search code examples
ms-access

Create MS Access query with matching columns similar to Index-Match?

Let's say I have 2 tables

Table 1

PersonID PersonName
1 Ann
2 Bob
3 Charles

Table 2

ItemID PersonID-1 PersonID-2
1 1 2
2 3 1

I want to create a query from table 2 that matches the PersonName from table 1:

ItemID PersonName-1 PersonName-2
1 Ann Bob
2 Charles Ann

If it's only 1 column then I can create a merge query but when it's 2 columns or more i dont know how. In Excel this can easily be done with Index-Match. I'm new to Access so if anyone can guide my I'm very thankful.

Solution

  • You can create a query with this SQL:

    SELECT I.ItemID, P1.PersonName AS [PersonName-1], P2.PersonName AS [PersonName-2]
FROM
    ItemTable I
    INNER JOIN PersonTable P1 ON I.[PersonID-1] = P1.PersonID
    INNER JOIN PersonTable P2 ON I.[PersonID-2] = P2.PersonID
ORDER BY I.ItemID

    Note that it is easier to work with column names being valid identifiers, i.e., names consisting only of alphanumeric characters and underscores and not starting with a digit. Examples: PersonName_1, PersonName1, Person_ID. Because then you will not have to enclose the names in square brackets.

    If you write a column name as I.PersonID-1, Access thinks it must subtract 1 from a column named I.PersonID.