Comparing two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
 
Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
Click to expand...

Why do you have the same data in two tables? It only leads to exactly this
problem: not knowing what's missing and not knowing which table is right and
which table is wrong!

What you can do is create a Query joining the two tables on some appropriate
field (and I have no way to know what's appropriate, as you didn't post any
information about the tables), and put a criterion on each field in the second
table such as

<> [Firsttable].[fieldname]

Put all these criteria on separate lines on the grid so Access will use "or"
logic, and show you the result if any one of the fields is a mismatch.

You'll need somewhat different techniques (using the Unmatched Query Wizard in
the query window) to find missing *records* in one table or the other.

John W. Vinson [MVP]
 
Sorry,

I have data exported on a certain date, and then exported the same data on a
later date (from an application to Excel then imported to Access) and needed
to see what data has changed.

Eg. Table 1 was created from data on 14/06/07
Table 2 was created from data on 05/07/07

Hope this is some help for you??

Otherwise i'll give what you mentioned a try.

Thanks

John W. Vinson said:
Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
Click to expand...

Why do you have the same data in two tables? It only leads to exactly this
problem: not knowing what's missing and not knowing which table is right and
which table is wrong!

What you can do is create a Query joining the two tables on some appropriate
field (and I have no way to know what's appropriate, as you didn't post any
information about the tables), and put a criterion on each field in the second
table such as

<> [Firsttable].[fieldname]

Put all these criteria on separate lines on the grid so Access will use "or"
logic, and show you the result if any one of the fields is a mismatch.

You'll need somewhat different techniques (using the Unmatched Query Wizard in
the query window) to find missing *records* in one table or the other.

John W. Vinson [MVP]
Click to expand...
 
Agree with John, without more detail of your table structure, it is
difficult to say.

1. Do you have a field in the table which indicates the date that the
record was changed?

2. Does the table contain a primary key (either single or multiple columns)

3. What do you want to do with the data when you get it? Are you trying to
identify records where one or more of the values have changed, or are you
just looking to identify records that are in Table2 that are not in Table1.

Answer these questions and we can give you a more specific answer.

Dale

Adrian said:
Sorry,

I have data exported on a certain date, and then exported the same data on
a
later date (from an application to Excel then imported to Access) and
needed
to see what data has changed.

Eg. Table 1 was created from data on 14/06/07
Table 2 was created from data on 05/07/07

Hope this is some help for you??

Otherwise i'll give what you mentioned a try.

Thanks

John W. Vinson said:
Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to
compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
Click to expand...

Why do you have the same data in two tables? It only leads to exactly
this
problem: not knowing what's missing and not knowing which table is right
and
which table is wrong!

What you can do is create a Query joining the two tables on some
appropriate
field (and I have no way to know what's appropriate, as you didn't post
any
information about the tables), and put a criterion on each field in the
second
table such as

<> [Firsttable].[fieldname]

Put all these criteria on separate lines on the grid so Access will use
"or"
logic, and show you the result if any one of the fields is a mismatch.

You'll need somewhat different techniques (using the Unmatched Query
Wizard in
the query window) to find missing *records* in one table or the other.

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1
 
Adrian said:
Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
Click to expand...

Adrian,

It appears to me that you are trying to find the "difference" between the two tables
(subtracting one from another).

General Examples:

Not Exists:

SELECT I1.ItemID
FROM Items AS I1
WHERE NOT EXISTS
(SELECT *
FROM ItemsDup AS ID1
WHERE ID1.ItemID = I1.ItemID)

Frustrated Outer Join (Left Join):

SELECT I1.ItemID
FROM Items AS I1
LEFT JOIN
ItemsDup AS ID1
ON I1.ItemID = ID1.ItemID
WHERE ID1.ItemID IS NULL

Notes:

The frustrated outer join is what MS Access' own "Find Unmatched Query Wizard" produces
when it is used.

-------------------------------

Tables:

CREATE TABLE Items
(ItemID INTEGER
,CONSTRAINT pk_Items
PRIMARY KEY (ItemID)
)

CREATE TABLE ItemsDup
(ItemID INTEGER
,CONSTRAINT pk_ItemsDup
PRIMARY KEY (ItemID)
)

Sample Data:

Items
Key
1
2
3
4
5
6

ItemsDup
Key
2
4
6


Results

NotExists:

1
2
3

FrustratedOuterJoin:

1
2
3


Sincerely,

Chris O.
 
These newsgroups are for the FREE exchange of ideas, information, and
assistance. This is absolutely NOT the place to troll for business: such
postings are not welcome, and they make such a bad impression that it can
only hurt your reputation in the Access community.

If you want to post actual advice and assistance here in the newsgroups for
all to benefit from, or post questions of your own, then your participation
will be welcomed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1


Adrian said:
Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to
compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
Click to expand...
Click to expand...
 
Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
Click to expand...

Is there something about the data that won't let you use the "Find Unmatched
Query Wizard"?
 
Adrian said:
Hi,
I'm still getting used to Access and needed a little help.

I have two tables with virtually the same data and i was wanting to
compare
them using an SQL query so i can see what data is missing?

Is anyone able to help?

Thanks
Click to expand...

In Queries, you could click "New" and run the "Find Unmatched Querie Wizard"

Yrs,
Jim
 
What you can do is create a Query joining the twotableson some
appropriate
field (and I have no way to know what's appropriate, as you didn't
post any
information about thetables), and put a criterion on each field in the
second
table such as

<> [Firsttable].[fieldname]

Put all these criteria on separate lines on the grid soAccesswill use
"or"
logic, and show you the result if any one of the fields is a mismatch.

You'll need somewhat different techniques (using the Unmatched Query
Wizard in
the query window) to find missing *records* in one table or the other.

John W. Vinson [MVP]

************

I use Access 2003 for my db and I have the same issue. The Unmatched
Wizard is not helping for some unknown reason.

I am able to get my comparison to work with "<> [Firsttable].
[fieldname]" (Yeah!) per the great info from John. However, I'm
wondering if there is a quicker solution for my large tables (200+
fields).

Is there a VB solution that could do the comparison and loop through
all the fields? I am a VB novice.

Thanks!

Nick
 
Sweet googly moogly! Thanks Roger!

I will looking around the rest of your website after I am done with
this little project.

Nick
 
Back
Top