• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

Creating an unmatched query using two queries with formatted data

Using the code snippet below, I've tried to make an unmatched query using the query wizard to show all records in qrySample where no records from qryDNCNumbers match any fields in qrySample..  DNC meaning 'Do not call'.  These two queries are just simply reformatted phone lists that I have stored in two tables.  qrySample has an expression that formats all the phone numbers in tblSample to make all of the phone numbers appear in a certain format (Ex: (123) 456-7890), and qryDNCNumbers does the same thing for tblDNC.  Reason being, both of these tables that have phone numbers may be formatted (651) 123-2456, 651-123-2456, or 651 123 2456, and other formats.  So, I formatted the two tables to share a universal format to match the data.  I've already created a query to show data that matches from the two formatted queries, but I can't seem to get a query that shows all data from qrySample except for where qrySample.Sample1 = qryDNCNumbers.DNC1.  Access keeps giving me the "data type mismatch" error, even though both columns from each table are type 'text'.  What do I need to do to get the Sample list to not include numbers from the formatted DNC query?
Thanks for your help ahead of time.
SELECT qrySample.Sample1
FROM qrySample LEFT JOIN qryDNCNumbers ON qrySample.Sample1 = qryDNCNumbers.DNC1
WHERE (((qryDNCNumbers.DNC1) Is Null));

Open in new window

0
jdol2949
Asked:
jdol2949
  • 2
1 Solution
 
jdol2949Author Commented:
To make things a little easier.. I'm going to post the test DB that I've been working on.
test.mdb
0
 
Chris LuttrellSenior Database ArchitectCommented:
I got the following query to run against your db and it excluded the (651) 452-5630 number that is in both tables.  Will this work for you.
SELECT Sample1
FROM qrySample
where Sample1 not in
(select DNC1
from qryDNCNumbers);

Open in new window

0
 
jdol2949Author Commented:
Exactly what I was looking for.  Thanks CGLuttrell!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now