SQL Server Join Tables By Combining 2 Columns

Question

This sounds ridiculously easy but I've tried so many different approaches. This query is just set up weird and I'm trying to JOIN it but there's not a common column I can do that with. There is, however, a LastFirst column (consists of LastName then FirstName) written in the context of DOE, JOHN. Then on the columns I'm trying to join that with it's just FirstName (John) and LastName (Doe).

I'm actually trying to select data from 4 tables that all are returning 1 row. These 2 tables can be joined:

SELECT 
    RIFQuery.*, 
    _Employee.EmployeeLastName + ', ' + _Employee.EmployeeFirstName AS EmployeeLastFirst, 
    _Employee.EmployeeTitle, _Employee.Phone As EmployeePhone, 
    _Employee.EmailAddress As EmployeeEmailAddress 
FROM 
    RIFQuery 
INNER JOIN 
    _Employee ON RIFQuery.CreatedBy = _Employee.AutoNumber 
WHERE 
    RIFQuery.Autonumber = 1

This one has nothing to join with so I'll probably union it and null remaining columns:

SELECT * 
FROM tblOrganization

This is the table that contains LastName and FirstName that I'm trying to join with RIFQuery.LastFirst:

SELECT 
    Gender As ClientGender, DOB As ClientDOB, SSN As ClientSSN
FROM 
    _Clients 
WHERE 
    _Clients.LASTNAME = left(RIFQuery.LastFirst, len(RIFQuery.LastFirst)-CHARINDEX(',', REVERSE(RIFQuery.LastFirst))) 
     AND _Clients.FIRSTNAME = ltrim(substring(RIFQuery.LastFirst, len(RIFQuery.LastFirst)-CHARINDEX(',', REVERSE(RIFQuery.LastFirst))+2, len(RIFQuery.LastFirst)))

In that WHERE statement the code will split the LastFirst column and get the row by searching their LastName and FirstName. I'm wondering if there's a way I can write that into a JOIN? Otherwise I can probably UNION and null remaining columns but it will look very ugly.

UPDATE

I tried 2 suggestions from here and both result in a syntax error. I forgot to mention that I'm executing this code inside Microsoft Access VBA and trying to retrieve a DAO.RecordSet. I had to remove some table names in the SELECT statement to get past a syntax error from there, so maybe I should update the question to reflect MS ACCESS and not SQL Server, although only the query is the only pure Access object and the rest are linked ODBC tables to SQL Server.

UPDATE

Just one of those issues where I can't sleep until it's fixed and will obsess until it is. If I take out all _Employee references (from SELECT and JOIN statements), it wants to work but errors about too few parameters. I just now know this is related to _Employee. Getting different results from applying parentheses and just hoping I'll get lucky and hit on it.

The error is caused by this line:

INNER JOIN [_Employee] ON [_Employee].[AutoNumber] = [RIFQuery].[CreatedBy]

I get this error: "Syntax error (missing operator) in query expression".

As seen in this screenshot:

Here's my latest query I'm playing with, minus the parentheses:

str = "SELECT [RIFQuery].*, " & vbCrLf & _
"       ([_Employee].[EmployeeLastName] & ', ' & [_Employee].[EmployeeFirstName]) AS [EmployeeLastFirst], " & vbCrLf & _
"       [_Employee].[EmployeeTitle], " & vbCrLf & _
"       [_Employee].[Phone] AS [EmployeePhone], " & vbCrLf & _
"       [_Employee].[EmailAddress] AS [EmployeeEmailAddress], " & vbCrLf & _
"       [_Clients].[Gender] AS [ClientGender], " & vbCrLf & _
"       [_Clients].[DOB] AS [ClientDOB], " & vbCrLf & _
"       [_Clients].[SSN] AS [ClientSSN] " & vbCrLf & _
"FROM [_Clients] " & vbCrLf & _
"      INNER JOIN [RIFQuery] ON [RIFQuery].[LastFirst] = [_Clients].[LASTNAME] & ', ' & [_Clients].[FIRSTNAME] " & vbCrLf & _
"      INNER JOIN [_Employee] ON [_Employee].[AutoNumber] = [RIFQuery].[CreatedBy] " & vbCrLf & _
"WHERE [RIFQuery].[Autonumber] = 1;"

For debugging purposes, if I remove those last 2 lines and the _Employee SELECT statements, it'll process the query without a problem. If anyone has any ideas just let me know.

I was focused on that RIFQuery JOIN statement being the culprit for the longest time but I've found that simply is not the issue any more. With that said, this thread has been essentially solved and I appreciate the help.

Answer 1

MS Access is using a slightly different syntax than SQL Server when it comes to using more than one JOIN. You could leave out the JOIN with the _Clients (making it a cross join) and move that condition to the WHERE clause, which would make the query look like this (and which would allow you to display the design window for the query without any problem)

SELECT RIFQuery.*, 
  EmployeeLastName + ', ' + EmployeeFirstName As EmployeeLastFirst, 
  EmployeeTitle, Phone As EmployeePhone, EmailAddress As EmployeeEmailAddress, 
  Gender As ClientGender, DOB As ClientDOB, SSN As ClientSSN
FROM _Clients, RIFQuery INNER JOIN _Employee ON RIFQuery.CreatedBy = _Employee.AutoNumber
WHERE RIFQuery.LastFirst = _Clients.LASTNAME & ", " & _Clients.FIRSTNAME;

Instead of assembling the query string in VBA just for being able to change the parameter value, I suggest to save the following query as an Access object (maybe qryRIF):

PARAMETERS lgRIF Long;
SELECT RIFQuery.*, 
  EmployeeLastName + ', ' + EmployeeFirstName As EmployeeLastFirst, 
  EmployeeTitle, Phone As EmployeePhone, EmailAddress As EmployeeEmailAddress, 
  Gender As ClientGender, DOB As ClientDOB, SSN As ClientSSN
FROM _Clients, RIFQuery INNER JOIN _Employee ON RIFQuery.CreatedBy = _Employee.AutoNumber
WHERE RIFQuery.LastFirst = _Clients.LASTNAME & ", " & _Clients.FIRSTNAME
  AND RIFQuery.Autonumber = [lgRIF];

In your VBA code, you can use code like the following to grab the QueryDef object, assign the parameter value and open a recordset:

With CurrentDb.QueryDefs!qryRIF
    !lgRIF = lgRIF
    With .OpenRecordset()
        ' ... your code ...
        .Close
    End With
    .Close
End With