Solved

Creating an unmatched query using two queries with formatted data

Posted on 2009-04-14
3
502 Views
Last Modified: 2013-11-27
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
Comment
Question by:jdol2949
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:jdol2949
ID: 24142189
To make things a little easier.. I'm going to post the test DB that I've been working on.
test.mdb
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24144564
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
 

Author Closing Comment

by:jdol2949
ID: 31570124
Exactly what I was looking for.  Thanks CGLuttrell!!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Omit After Update event 5 43
docmd has stopped working 3 42
HIghlights of SSIS? 3 42
SUBFORM on ACCESS 2013 8 28
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question