If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding Unmatched records from two tables
I have two tables where all records should match. One table, however, has
more records than the other and I haven't been able to determine why. Unfortunately, the records are such that there is no primary key and there is no one field that has unique data. All fields combined make each records unique. How do I determine which records in one table are not in the other? |
#2
|
|||
|
|||
Have you tried the Find Unmatched Query Wizard (select it when you create a
new query)? "Tia" wrote: I have two tables where all records should match. One table, however, has more records than the other and I haven't been able to determine why. Unfortunately, the records are such that there is no primary key and there is no one field that has unique data. All fields combined make each records unique. How do I determine which records in one table are not in the other? |
#3
|
|||
|
|||
Tia,
I'm sorry- I'm just not that good at the Access thing. Maybe a more technical person will answer. "Tia" wrote: Yes, I tried the query but the problem is that no one field is unmatched due to the nature of the fields. the only way to determine if it is unmatched is to compare all fields in a record in Table 1 to all fields in a redord in Table 2. Is that possible to do? "Stephanie" wrote: Have you tried the Find Unmatched Query Wizard (select it when you create a new query)? "Tia" wrote: I have two tables where all records should match. One table, however, has more records than the other and I haven't been able to determine why. Unfortunately, the records are such that there is no primary key and there is no one field that has unique data. All fields combined make each records unique. How do I determine which records in one table are not in the other? |
#4
|
|||
|
|||
Yes, I tried the query but the problem is that no one field is unmatched due
to the nature of the fields. the only way to determine if it is unmatched is to compare all fields in a record in Table 1 to all fields in a redord in Table 2. Is that possible to do? "Stephanie" wrote: Have you tried the Find Unmatched Query Wizard (select it when you create a new query)? "Tia" wrote: I have two tables where all records should match. One table, however, has more records than the other and I haven't been able to determine why. Unfortunately, the records are such that there is no primary key and there is no one field that has unique data. All fields combined make each records unique. How do I determine which records in one table are not in the other? |
#5
|
|||
|
|||
How many fields are involved? IF just a few you can make your own unmatched query.
--Add Both tables to the query --Create a join line between each set of fields (TableOne.FieldA to TableTwo.FieldA) --Click on the join line and select the Option All records in tableOne (this assumes tableOne has the most records) and only matches in table two. Repeat for the other tables Add all the fields to the grid. Put IS NULL in the first criteria line under each of the fields in TableTwo. If there aren't to many fields involved this should work. If not, post back. And we can try a UNION ALL Query as the source for a totals query. =Hopefully you have at least one field in each of your two tables that always has a value. Tia wrote: Yes, I tried the query but the problem is that no one field is unmatched due to the nature of the fields. the only way to determine if it is unmatched is to compare all fields in a record in Table 1 to all fields in a redord in Table 2. Is that possible to do? "Stephanie" wrote: Have you tried the Find Unmatched Query Wizard (select it when you create a new query)? "Tia" wrote: I have two tables where all records should match. One table, however, has more records than the other and I haven't been able to determine why. Unfortunately, the records are such that there is no primary key and there is no one field that has unique data. All fields combined make each records unique. How do I determine which records in one table are not in the other? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I create a table of unique records from two or more tables | Rubble | Running & Setting Up Queries | 5 | June 23rd, 2005 12:05 PM |
Deleting specific records | [email protected] | General Discussion | 6 | June 22nd, 2005 11:35 PM |
.ADP SQL TABLES SHOW IN ACCESS BUT SOME WILL NOT SHOW RECORDS | Derek Walton | General Discussion | 3 | February 11th, 2005 05:03 PM |
Delete multiple records from multiple tables in Access? | nalgene | General Discussion | 2 | August 15th, 2004 07:10 PM |
Generating unique records from tables | Peter Jay Salzman | Running & Setting Up Queries | 1 | July 7th, 2004 12:50 AM |