Trying to compare four different phone numbers from two different tables to find like values

[gojets1721]

So I've got two tables: Customers and Employees. For each group, there is a home phone and mobile phone listed. I've attached a very simple example DB

I'm trying to use a query to key in on matches in the phone numbers across the two tables. So if person 1's home phone in table1 matches a mobile in table2, boom that's a match. If person 2's mobile phone in table1 matches a home phone in table2, boom that's a match. And so on.

I thought it'd be fairly simple but its turned into quite a headache because it's four total fields and I'm unsure how to best design it. Any thoughts?

 

[theDBguy]

Hi. Is this the result you're expecting from your sample data?

 

[MajP]

SELECT
  tblCustomers.CustomerID,
  tblCustomers.CustomerHomePhone as PhoneNumber,
  "Home Phone" as Phone_Type
FROM tblCustomers
  WHERE Customerhomephone is not null
UNION SELECT
  tblCustomers.CustomerID,
  tblCustomers.CustomerMobilePhone,
  "Mobile " as Phone_Type
FROM tblCustomers
  Where CustomerMobilePhone is not null;

qryCustomer qryCustomer

CustomerID	PhoneNumber	Phone_Type
10	123-456-7890	Home Phone
10	123-456-7890	Mobile
20	098-765-4321	Home Phone
20	999-999-9999	Mobile
30	111-222-3333	Home Phone

SELECT
  tblEmployees.EmployeeID,
  tblEmployees.EmployeeHomePhone as PhoneNumber,
  "Home Phone" as Phone_Type
FROM tblEmployees
WHERE EmployeeHomePhone Is Not null
UNION SELECT
  tblEmployees.EmployeeID,
  tblEmployees.EmployeeMobilePhone,
  "Mobile " as Phone_Type
FROM tblEmployees
  WHERE EmployeeMobilePhone is not null;

qryEmployees qryEmployees

EmployeeID	PhoneNumber	Phone_Type
1	999-999-9999	Mobile
2	010-010-1010	Home Phone
2	010-010-1010	Mobile
3	123-456-7890	Mobile
3	232-232-2323	Home Phone

SELECT
 tblCustomers.CustomerFirstName,
  tblCustomers.CustomerLastName,
  qryCustomer.PhoneNumber,
  qryCustomer.Phone_Type,
  qryEmployees.Phone_Type,
  tblEmployees.EmployeeFirstName,
  tblEmployees.EmployeeLastName
FROM (tblCustomers INNER JOIN
     (qryCustomer INNER JOIN qryEmployees ON qryCustomer.PhoneNumber = qryEmployees.PhoneNumber)
     ON tblCustomers.CustomerID = qryCustomer.CustomerID)
     INNER JOIN tblEmployees ON qryEmployees.EmployeeID = tblEmployees.EmployeeID;

qryCommon qryCommon

CustomerFirstName	CustomerLastName	PhoneNumber	qryCustomer.Phone_Type	qryEmployees.Phone_Type	EmployeeFirstName	EmployeeLastName
Peter	Rogers	999-999-9999	Mobile	Mobile	Peter	Rogers
John	Sullivan	123-456-7890	Mobile	Mobile	John	Sullivan
John	Sullivan	123-456-7890	Home Phone	Mobile	John	Sullivan

 

[gojets1721]

Hi. Is this the result you're expecting from your sample data?
View attachment 92355

Yes exactly

 

[theDBguy]

Yes exactly

Okay, just in case it helps, here's the SQL statement I used.

SELECT tblCustomers.CustomerID, tblEmployees.EmployeeID, tblCustomers.CustomerHomePhone, tblEmployees.EmployeeMobilePhone, tblCustomers.CustomerMobilePhone, tblEmployees.EmployeeHomePhone
FROM tblCustomers, tblEmployees
WHERE (((tblCustomers.CustomerHomePhone)=[tblEmployees].[EmployeeMobilePhone])) OR (((tblEmployees.EmployeeHomePhone)=[tblCustomers].[CustomerMobilePhone])) OR (((tblCustomers.CustomerMobilePhone)=[tblEmployees].[EmployeeHomePhone])) OR (((tblEmployees.EmployeeMobilePhone)=[tblCustomers].[CustomerHomePhone]));

 

[arnelgp]

SELECT tblCustomers.CustomerID,
tblCustomers.CustomerFirstName,
tblCustomers.CustomerLastName,
tblEmployees.EmployeeID,
tblEmployees.EmployeeFirstName,
tblEmployees.EmployeeLastName
FROM tblCustomers, tblEmployees
WHERE ((("|" & [CustomerHomePhone] & "|" & [CustomerMobilePhone] & "|") Like "*|" & Nz([EmployeeHomePhone],"~") & "|*")) OR ((("|" & [CustomerHomePhone] & "|" & [CustomerMobilePhone] & "|") Like "*|" & Nz([EmployeeMobilePhone],"~") & "|*"));