MS Access Outer Join Multiple Fields to Single Record

Question

I need to do the exact same thing as shown here: INNER or LEFT Joining Multiple Table Records Into A Single Row but have it work in an MS Access Query.

Below is the scenario:

---

Phone Table

+----------------+-------------+
| Field          | Type        |
+----------------+-------------+
| f_id           | int(15)     |
| f_client_id    | int(11)     |
| f_phone_type   | varchar(50) |
| f_phone_number | varchar(13) |
+----------------+-------------+

Clients Table

+-----------------------------+--------------+------+-----+
| Field                       | Type         | Null | Key |
+-----------------------------+--------------+------+-----+
| f_id                        | int(15)      | NO   | PRI |
| f_first_name                | varchar(13)  | YES  | MUL |
| f_mi                        | char(1)      | YES  |     |
| f_last_name                 | varchar(20)  | NO   | MUL |
+-----------------------------+--------------+------+-----+

With a standard LEFT or INNER join, I get something like this:

+------------+------------+--------------+
| name       | Phone Type | Phone Number |
+------------+------------+--------------+
| John Smith | Home       | 712-555-6987 |
| John Smith | Work       | 712-555-1236 |
+------------+------------+--------------+

I need a query that will give me the work and home numbers that belong to a given client:

+------------+----------------+--------------+
| Name       | Work Number    | Home Number  |
+------------+----------------+--------------+
| John Smith | 712-555-1236   | 712-555-6987 |
+------------+----------------+--------------+

---

The solution in SQL was

SELECT CONCAT(c.f_first_name, ' ', c.f_last_name) as Client_Name, 
       wp.f_phone_number as Work_Number,
       hp.f_phone_number as Home_Number

  FROM clients c
       LEFT OUTER JOIN phone hp
       ON hp.f_client_id = c.f_id
    AND
       hp.phone_type = 'home'
       LEFT OUTER JOIN phone wp
       ON wp.f_client_id = c.f_id
    AND
       wp.phone_type = 'work'

This however does not translate to MS Access, the Join fails. What's the best way to accomplish this same thing through Access?

Answer 1

There are two problems you haven't discovered yet.

1. there is no CONCAT() function in Access or its database engine
2. your query attempts to use phone_type, but the actual field name is f_phone_type

When joining more than two tables, Access' database engine requires parentheses. It's easiest to get that right by using the query designer to set up the joins. Also the query designer will substitute LEFT JOIN for LEFT OUTER JOIN; either way works for me in Access 2003.

This one is easy to build in the query designer, but only returns rows for clients who have both home and work numbers. I used it as a starting point, then later adjusted the ON clauses similar to your original.

SELECT
    c.f_first_name & " " & c.f_last_name AS [Name],
    wp.f_phone_number AS [Work Number],
    hp.f_phone_number AS [Home Number]
FROM
    (Clients AS c
    LEFT JOIN Phones AS hp
    ON c.f_id = hp.f_client_id)
    LEFT JOIN Phones AS wp
    ON c.f_id = wp.f_client_id
WHERE
    hp.f_phone_type='Home'
    AND wp.f_phone_type='Work';

Moving those WHERE conditions into the ON expressions, as in your SQL Server example, will return all clients whether or not you have any phone numbers on file for them. However that approach will require parentheses around the ON expressions. And those JOINS can not be displayed in the query designer.

SELECT
    c.f_first_name & " " & c.f_last_name AS [Name],
    wp.f_phone_number AS [Work Number],
    hp.f_phone_number AS [Home Number]
FROM
    (Clients AS c
    LEFT JOIN Phones AS hp
    ON (c.f_id = hp.f_client_id AND hp.f_phone_type='Home'))
    LEFT JOIN Phones AS wp
    ON (c.f_id = wp.f_client_id AND wp.f_phone_type='Work');

Update: For myself, I would prefer to do this with subqueries.

SELECT
    c.f_first_name & " " & c.f_last_name AS [Name],
    wp.f_phone_number AS [Work Number],
    hp.f_phone_number AS [Home Number]
FROM
    (Clients AS c
    LEFT JOIN [
        SELECT f_client_id, f_phone_number
        FROM Phones
        WHERE f_phone_type='Home'
    ]. AS hp
    ON c.f_id = hp.f_client_id)
    LEFT JOIN [
        SELECT f_client_id, f_phone_number
        FROM Phones
        WHERE f_phone_type='Work'
    ]. AS wp
    ON c.f_id = wp.f_client_id;

Answer 2

You can also use sub query, such as:

SELECT (firstname & " " & lastname ) AS fullname,
  (SELECT f_PhoneNumber FROM tblPhones 
   WHERE  f_clientID = clients.id AND f_PhoneType = "Home")
   AS HomeNumber,
  (SELECT f_PhoneNumber FROM tblPhones 
   WHERE  f_clientID = clients.id AND f_PhoneType = "Work")
   AS WorkNumber
FROM clients