Link to home
Start Free TrialLog in
Avatar of qz8dsw
qz8dswFlag for New Zealand

asked on

Comparing 2 Microsft 2003 Access tables

Hi all,

I should know better and it's probably laziness (Or I'm just too tired) on my part but can someone help me out here?
I have 2 tables in an access 2003 database.
Each have field1 and datefield (Both indexed).
I want to find records where something occurs in one table that is not in the other.
Both field1 and datefield "should" match so I just want when they don't.

Thanks in advance,
Terry
Avatar of sgerling
sgerling

How about a tired/lazy answer? :-)

Use the Unmatched Query Wizard or something like this:

SELECT TableA.field1, TableB.field1  
FROM TableA, TableB
WHERE TableA.key = TableB.key
AND TableA.field1 <> TableB.field1  
Avatar of qz8dsw

ASKER

Thanks sqerling,

It's not exactly working.
Let me look at this tomorrow.
I know I'm better than this.

Cheers,
Terry
Avatar of peter57r
I think you might need to expand on what you mean by items not matching.

(I presume that every record on each table will 'not match' almost every record on the other one).
Avatar of qz8dsw

ASKER

the 2 tables have identical columns and "should" contain identical data.
I want to find the combination of field1 and datefield for each row that occur in Table1 that do not occur in table2
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of qz8dsw

ASKER

The "number" of records was in the millions. (Yes past tense)
Order did not worry me.
This is REALLY 2 tables with 2 fields where the data , data types, everything does match. (Right down to the date formats for the date field)
I am very surprised no-one suggested the unmatched data wizard internal to Access 2003.

Although a basic answer, when I wrote the question even looking at the access help I could not find it. (I was really tired, and a 22 hour day followed)
The next day, I could not find it. It was only after that when I figured out the help referring to tabs was wrong I figured out how to do it.

It might have been my explanation of the problem that added to the confusion.
Still looking at it, Microsofts help does not really explain very well (IMHO) how to do this under Access 2003. (I'm more used to using SQL commands and I'm alot more at home with that)
 
boag2000, you can have the points as your SQL was closer to what I wanted (when adapted) although not a perfect fit.
Avatar of qz8dsw

ASKER

I should have known myself how to fix it.
I'm sorry to have troubled you about such a basic thing.

Terry
qz8dsw,

<I am very surprised no-one suggested the unmatched data wizard internal to Access 2003.>
Expert, sgerling suggested just that in the very first post:
https://www.experts-exchange.com/questions/23505343/Comparing-2-Microsft-2003-Access-tables.html?cid=238&anchorAnswerId=21839821#a21839821
... and you said : <It's not exactly working. >
https://www.experts-exchange.com/questions/23505343/Comparing-2-Microsft-2003-Access-tables.html?cid=238&anchorAnswerId=21839914#a21839914
... So someone did mention it.
;-)

Again, the question of "Dulplicate" or "Unmatched" always needs to be clarified.

It can mean different things to different people.
1. You never really clarified your definition.
2. I asked for sample data, but you never provided any.

I am sure any expert here could have provided a more "targeted" solution if you had provided the above information.

JeffCoachman