SELECT statement comparing multiple fields from multiple tables

Question

I have to two seperate tables (Table A and Table B). Table A has about 15 columns, the only ones of significance are [First Name] and [Last Name]. Table B has many more columns, and again the only ones I care about are FirstName and LastName (Table B's fields do not contain spaces, where as Table A's do).

They're both in Access and I'm trying to write a query that will find people from Table B who are not in Table A. I'm quite amateur with SQL, but here's what I came up with:

SELECT ([Table A].[First Name] + [Table B].[Last Name]) AS FullName
FROM [Table A] 
LEFT JOIN [Table B] 
ON [Table A].FullName=[Table B].([First Name] + [Table B].[Last Name]) AS FullName
WHERE [Table A].FullName IS NULL;

It doesn't like me trying to make an alias for First Name + Last Name from Table B, but I have no idea how I'd do it otherwise.

Answer 1

Try this:

SELECT ([Table A].[First Name] + ' ' + [Table A].[Last Name]) AS FullName
FROM [Table B] 
LEFT OUTER JOIN [Table A] 
ON [Table A].[First Name]=[Table B].[First Name] AND [Table B].[Last Name] = [Table A].[Last Name]
WHERE [Table A].[First Name] IS NULL AND [Table A].[Last Name] IS NULL