Comparing two tables

  • Thread starter Thread starter DZ
  • Start date Start date
D

DZ

Is there an easy way to compare two tables (or Queries) on all fields and all
data.
Some of the data is null.
 
DZ said:
Is there an easy way to compare two tables (or Queries) on all fields
and all data. Some of the data is null.
Click to expand...

1. Create a query that inner-joins the 2 tables on all fields. Save as (say)
qryMatch.

2. Create another query, using the 'unmatched query wizard' (in the first
dialog when you create a new query) to get the records from table A that are
not in qryMatch.

3. Repeat step 2 for table B.

4. (Optional) create another new query to UNION ALL the results of step 2
and step 3.
 
I must be doing something wrong.

In step one of your instructions, I inner joined all corresponding fields
and output all fields in a query. Tables A & B are identical. I made them
identical to test the results.

The query returned no records. Both tables have identical fields and data.

When I close the query and try to reopen it in design view, Sometimes I get
an error message.

"Access cannot represent the join expression in design view" then it lists
the join and repeats the error message for several other join. When in
finally opens in Design view, the joins mentioned in the error message
becomes unjoined in design view.

Other times when I close the query and try to reopen it in design view, i
don't get the error message. .. buy the results of the query are always no
records.

Thanks for any insight into this
 
How many fields do you have?

Are any of them memo fields, OLE Object fields, Hyperlinks, Attachments, or
multi-valued fields (Access 2007)?

Post the SQL statement that doesn't work.
 
Here's the SQL for qryMatch

SELECT Dallas.*, Dallas2.*
FROM Dallas INNER JOIN Dallas2 ON (Dallas2.[Registered Under What Address] =
Dallas.[Registered Under What Address]) AND (Dallas2.[Registered Under What
Name] = Dallas.[Registered Under What Name]) AND (Dallas2.[Asset #] =
Dallas.[Asset #]) AND (Dallas2.[License Plate] = Dallas.[License Plate]) AND
(Dallas2.[SSI Veh #] = Dallas.[SSI Veh #]) AND (Dallas2.[Vehicle Vin Number]
= Dallas.[Vehicle Vin Number]) AND (Dallas2.[Acquisition Date] =
Dallas.[Acquisition Date]) AND (Dallas2.[Date Removed Permanently] =
Dallas.[Date Removed Permanently]) AND (Dallas.[Not in Service Company Owned]
= Dallas2.[Not in Service Company Owned]) AND (Dallas.[Not in Service UF
Owned] = Dallas2.[Not in Service UF Owned]) AND (Dallas.[In Service] =
Dallas2.[In Service]) AND (Dallas.PAX = Dallas2.PAX) AND (Dallas.Description
= Dallas2.Description) AND (Dallas.[Title Holder] = Dallas2.[Title Holder])
AND (Dallas.[State Registered] = Dallas2.[State Registered]);
 
cought hat will only return a result if the data in every field in
each table is exatally the same

this is what you should do

table1
table1id
field1
field2
field3
etc ....

table2
table2id
table1id
field1
field2
field3
etc

now the fields in table 1 are different in field 2 and the table1id in
table 2 links the two together

an example would be

tblstudent
studentid
firstname
lastname

tblcourse
courseid
studentid
coursetitle

the data would be

tblstudent
studentid firstname lastname
1 john doe
2 jane doe

tblcourse
courseid studentid coursetitle
1 1 science
1 2 science
2 1 maths
3 2 english

so that data tells me that student 1 john doe is taking the course 1
and 2 science and maths and student 2 jane doe is taking course 1 and
3 science and english

i hope this helps

regards
kelvan
 
sorry forgot the query to join them

select firstname,lastname,coursetitle
from student,course
where tblstudent.studentid = tblcourse.studentid

and that will output

firstname lastname coursetitle
john doe science
jane doe science
john doe maths
jane doe english

another form of queries is the join queries such as INNER JOIN which
is technically the same as what i hane done above just with more
typing

select firstname,lastname,coursetitle
from student INNER JOIN course
ON tblstudent.studentid = tblcourse.studentid

or something like that i dont type out join queries i just do =

as a note dont use spaces in table or field names it just causes
problems and if you do for whatever reason you need to place brackets
around them [student table] as an example

hope this helps as well

Regards
Kelvan
 
Try adding Nz() to each field:

SELECT Dallas.*, Dallas2.*
FROM Dallas INNER JOIN Dallas2 ON
(Nz(Dallas2.[Registered Under What Address])
= Nz(Dallas.[Registered Under What Address]))
AND (Nz(Dallas2.[Registered Under What Name])
= Nz(Dallas.[Registered Under What Name]))
AND (Nz(Dallas2.[Asset #]) = Nz(Dallas.[Asset #]))
AND (Nz(Dallas2.[License Plate]) = Nz(Dallas.[License Plate]))
AND ...
 
First, if any value in a field is null it is not going to match on a join.

SELECT *
FROM A INNER JOIN B
ON A.FieldA = B.FieldA
AND A.FieldB = B.FieldB

If FieldA is null in all the records in both tables then you will get no
records returned even if there is a match for every record in both tables
based on FieldB. Nulls NEVER match anything and that includes other Nulls.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I am trying to do something similar to DZ. I have two tables that have
similar information and I just want to compare the columns and pull the only
the information at is different in certain columns to Excel. What is the
simplest way to do this.
 
create a new topic and give use the table structure in each table and
identify the fields that are the same in the table structure

Regards
Kelvan
 
Back
Top