?
Solved

Comparing 2 Microsft 2003 Access tables

Posted on 2008-06-21
9
Medium Priority
?
767 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:qz8dsw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 1

Expert Comment

by:sgerling
ID: 21839821
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  
0
 
LVL 15

Author Comment

by:qz8dsw
ID: 21839914
Thanks sqerling,

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

Cheers,
Terry
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21839974
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).
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 15

Author Comment

by:qz8dsw
ID: 21841954
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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 21843019
qz8dsw,

*Please* provide some sample data.
<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.>

<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>

Neither of these two statements mention the *number* off records in the table.
Is this a consideration in your "matching" criteria?
For example:
tbl1
Pete
Jeff
Sally

Table2
Pete
Jeff
In this case the standard Unmatched query wizard would list the "Sally" record as being "unmatched"
SELECT tbl1.Field1, tbl1.DateField
FROM tbl1 LEFT JOIN tbl2 ON tbl1.Field1 = tbl2.Field1
WHERE (((tbl2.Field1) Is Null));
(You would have to do this for tbl2 as well)



How about the *order* of Records:
Table1
Pete
Jeff
Sally

Table2
Sally
Pete
Jeff
These two tables contain "Identical Data", but you could argue all day if they really "Match".

In other words, post some sample data, then post what you need the Unmatched Output to be.
("This record/Field/Value is unmatched because...")

JeffCoachman
0
 
LVL 15

Author Comment

by:qz8dsw
ID: 21863734
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.
0
 
LVL 15

Author Closing Comment

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

Terry
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21866663
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_23505343.html?cid=238#a21839821
... and you said : <It's not exactly working. >
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_23505343.html?cid=238#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
 

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question