full name vs first and last name

J

Jay

I have two tables. One has "customer full name" in one field. The other is
from another database and has two fields, : "first name" and "last name". I
want to retrieve all records that match :customer full name" in one table
with "first and last name" in other table. I don't know how to do it. Or
better suggestions? Thanks you very much in advance.
 
A

Allen Browne

In the statment below, replace "Table2" with the name of the table that has
the full name, and "Table1" with the name of the table that has the separate
names:
SELECT [Table1].*
FROM [Table1] INNER JOIN [Table2]
ON [Table1].[first name] & " " & [Table1].[last name] = [Table2].[customer
full name];

On the Queries tab of the Database window, click New.
Accept Design view.
Cancel the Add Tables dialog.
Swith to SQL View (View menu).
Paste in the query statement.

Here's an alternative statement:
SELECT [Table1].*
FROM [Table1], [Table2]
WHERE [Table1].[first name] & " " & [Table1].[last name] =
[Table2].[customer full name];
 
Top