• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • 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

  • 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.
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

jdol2949Author Commented:
Exactly what I was looking for.  Thanks CGLuttrell!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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