I can use the unmatched query wizard to find items between 2 tables that do not match or by changing the parameter to "is not null" can find those that do match. But how can I get a query to match 2 fields. I have 2 spreadsheets of data both using first and last names with lots of other different info. I created a blank database and loaded 2 tables and then matched last names but matching the first and last would winnow the data down without so much manual work. Any suggestions?
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 Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Unmatched query wizard
- Thread starter Tiggertoo
- Start date
- Thread starter
- #3
Ok.
1. Choose "Create Query in Design View" from the query tab.
2. A window will pop up listing your tables. Add both the tables that you want to compare by clicking on each name and choosing Add. Close the pop-up window.
3. You are now looking at the query design screen with two tables in it. Click on First Name in table #1 and drag it across to table #2 First Name. A line will be drawn between First Name in table #1 and First Name in table #2. Repeat with Last Name.
4. You can now add fields to the grid either by drag-and-drop or by double-clicking on the fields.
5. Choose View->Datasheet View to see the results of your query.
Building a query in this way, you will end up with the default join which is "Only include rows where the joined field from both tables are equal". You can edit this by clicking on the line between the names and choosing Join Properties. If you change the join type for First Name, make sure you choose the same join for the Last Name.
You can also apply these ideas to the query built for you by the wizard. Using the Find Unmatched Query, add a join for Last Name by the method described above. You will find that the join created by the wizard is "Include ALL records from Table #1 and only those records from table #2 where the joined fields are equal." Change the join that you have just made to match.
1. Choose "Create Query in Design View" from the query tab.
2. A window will pop up listing your tables. Add both the tables that you want to compare by clicking on each name and choosing Add. Close the pop-up window.
3. You are now looking at the query design screen with two tables in it. Click on First Name in table #1 and drag it across to table #2 First Name. A line will be drawn between First Name in table #1 and First Name in table #2. Repeat with Last Name.
4. You can now add fields to the grid either by drag-and-drop or by double-clicking on the fields.
5. Choose View->Datasheet View to see the results of your query.
Building a query in this way, you will end up with the default join which is "Only include rows where the joined field from both tables are equal". You can edit this by clicking on the line between the names and choosing Join Properties. If you change the join type for First Name, make sure you choose the same join for the Last Name.
You can also apply these ideas to the query built for you by the wizard. Using the Find Unmatched Query, add a join for Last Name by the method described above. You will find that the join created by the wizard is "Include ALL records from Table #1 and only those records from table #2 where the joined fields are equal." Change the join that you have just made to match.
- Thread starter
- #5
- Status
Similar threads
- Question