Trying to compare four different phone numbers from two different tables to find like values (1 Viewer)

templeowls

Registered User.
Local time
Today, 15:18
Joined
Jun 11, 2019
Messages
112
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?
 

Attachments

  • Example.accdb
    672 KB · Views: 27

theDBguy

I’m here to help
Staff member
Local time
Today, 15:18
Joined
Oct 29, 2018
Messages
16,203
Hi. Is this the result you're expecting from your sample data?
1623701813680.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
5,404
Code:
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

CustomerIDPhoneNumberPhone_Type
10123-456-7890Home Phone
10123-456-7890Mobile
20098-765-4321Home Phone
20999-999-9999Mobile
30111-222-3333Home Phone
Code:
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

EmployeeIDPhoneNumberPhone_Type
1999-999-9999Mobile
2010-010-1010Home Phone
2010-010-1010Mobile
3123-456-7890Mobile
3232-232-2323Home Phone
Code:
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

CustomerFirstNameCustomerLastNamePhoneNumberqryCustomer.Phone_TypeqryEmployees.Phone_TypeEmployeeFirstNameEmployeeLastName
PeterRogers999-999-9999Mobile Mobile PeterRogers
JohnSullivan123-456-7890Mobile Mobile JohnSullivan
JohnSullivan123-456-7890Home PhoneMobile JohnSullivan
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:18
Joined
Oct 29, 2018
Messages
16,203
Yes exactly
Okay, just in case it helps, here's the SQL statement I used.
SQL:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
13,775
Code:
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],"~") & "|*"));
 

Users who are viewing this thread

Top Bottom