I am about to complete a mailing and need to dedupe a prospect table against our main table in Access. There is a query wizard (unmatched query) that is basically what I want to do, but I can't really customize it. Basically, I need to kick out anyone from the prospect list with the same name (first and last) and zip code as our main table of current members and display the new deduped prospect list. Is that possible? Some Help me please!!!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HELP!!!
- Thread starter ACTHOMAS
- Start date
GeppoDarkson
Programmer
The right query is:
*****************************************************
Select *
From Table1
Where Not Exists(Select * From Table2 Where Table1.Field1 = Table2.Field2 and [so on...])
*****************************************************
By omitting Table1 in the subquery the SQL server performs a query for each record of Table1, if returns records Exists(...) is True, so ...Where NOT Exists(...) is False.
Let me give you an advice for free: there are more or less 10 important SQL keywords. Try to understand each one and you'll never should use a wizard anymore.
Wizards never teach his knowledge.
Bye!
(and sorry my bad english)
Geppo Darkson.
*****************************************************
Select *
From Table1
Where Not Exists(Select * From Table2 Where Table1.Field1 = Table2.Field2 and [so on...])
*****************************************************
By omitting Table1 in the subquery the SQL server performs a query for each record of Table1, if returns records Exists(...) is True, so ...Where NOT Exists(...) is False.
Let me give you an advice for free: there are more or less 10 important SQL keywords. Try to understand each one and you'll never should use a wizard anymore.
Wizards never teach his knowledge.
Bye!
(and sorry my bad english)
Geppo Darkson.
This is fairly straightforward to accomplish. You'll need to make use of a join and some crafty criteria usage to select the rows your are really needing to send letters too in your prospect table.
First- Set up a query that includes both the Prospect table and the Main table in the query design pane. The left table needs to be your ProspectTable, the right table will need to be your MainTable.
Next - set the Join relationships.
In this case you want to use 3 fields in your join.
Click on the Zipcode field in the Prospect table and drag and drop it on the Zipcode field in the Main table.
It prompts you with 3 options. You want the option that gives you all records from the ProspectTable, and only matching records from the MainTable.
Now do the same for the First name fields and finally the last name fields.
No try running this and see what you get. You should be seeing All records from the Prospect table and on the same rows you will be seeing any record where there was a matching record from the MainTable.
Notice that any line with blanks in the MainTable fields means that there was not a match. These are the people you want to isolate for your mailing.
Here is the critical part.
In the Zipcode field criteria line in the query pane add the following criteria:
IS NULL
Now run the query. You will see only rows without any matching records from the Main table. The "is null" criteria eliminated the records you didn't want to send letters to.
Now go back to design view. You will want to uncheck the "display" checkboxes on the fields that you don't really need to see anymore from the Maintable fields.
When you run the query now you are only seeing fields from the Prospect table. And more importantly your dups are now Not showing in the query result. Now you can use this query to run your mail merge.
Blake
First- Set up a query that includes both the Prospect table and the Main table in the query design pane. The left table needs to be your ProspectTable, the right table will need to be your MainTable.
Next - set the Join relationships.
In this case you want to use 3 fields in your join.
Click on the Zipcode field in the Prospect table and drag and drop it on the Zipcode field in the Main table.
It prompts you with 3 options. You want the option that gives you all records from the ProspectTable, and only matching records from the MainTable.
Now do the same for the First name fields and finally the last name fields.
No try running this and see what you get. You should be seeing All records from the Prospect table and on the same rows you will be seeing any record where there was a matching record from the MainTable.
Notice that any line with blanks in the MainTable fields means that there was not a match. These are the people you want to isolate for your mailing.
Here is the critical part.
In the Zipcode field criteria line in the query pane add the following criteria:
IS NULL
Now run the query. You will see only rows without any matching records from the Main table. The "is null" criteria eliminated the records you didn't want to send letters to.
Now go back to design view. You will want to uncheck the "display" checkboxes on the fields that you don't really need to see anymore from the Maintable fields.
When you run the query now you are only seeing fields from the Prospect table. And more importantly your dups are now Not showing in the query result. Now you can use this query to run your mail merge.
Blake
- Status
Similar threads
- Locked
- Question
- Locked
- Question