Compare 2 tables - Microsoft Access / VBA

ethoemmes

I have 2 tables (tblSAPDetails & tblVendorDetails) they both have
Vendor as the PK. I would like to run a query which would highligh the
differences between the 2 tables on 2 fields (ABC and Industry)

Eg:

TblSAPDetails:
Vendor ABC Industry
1004010 A MK

TblVendorDetails:
Vendor ABC Industry
1004010 B MK

Should somehow return the difference between the 2 tables.

Can anyone point me in the right direction?

TIA

Nov 13 '05 #1

Subscribe Reply

1552

Keith

et*******@blueyonder.co.uk wrote:

I have 2 tables (tblSAPDetails & tblVendorDetails) they both have
Vendor as the PK. I would like to run a query which would highligh the
differences between the 2 tables on 2 fields (ABC and Industry)

Eg:

TblSAPDetails:
Vendor ABC Industry
1004010 A MK

TblVendorDetails:
Vendor ABC Industry
1004010 B MK

Should somehow return the difference between the 2 tables.

Try concatonating the 3 fields for TblSAPDetails in one query and
concatonating the fields for TblVendorDetails in another. If I'm
understanding you correctly, you could then include them in a third query
with an outer join on the concatonated fields and test for nulls.

Regards,
Keith.
www.keithwilby.com

Nov 13 '05 #2

Alan Webb

Keith,
Why not do a calculated join on the two columns, for example:
SELECT VENDOR_OLD.NAME AS OLD_VENDOR_NAME, VENDOR_NEW.NAME AS
NEW_VENDOR_NAME
FROM tblSAPDetails as VENDOR_OLD, tblVendorDetails AS VENDOR_NEW
WHERE VENDOR_OLD.ABC <> VENDOR_NEW.ABC
AND VENDOR_OLD.INDUSTRY <> VENDOR_NEW.INDUSTRY

This will give you anything that does not have a matching ABC and Industry.

"Keith" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn************************@10.15.188.42...

et*******@blueyonder.co.uk wrote:
I have 2 tables (tblSAPDetails & tblVendorDetails) they both have
Vendor as the PK. I would like to run a query which would highligh the
differences between the 2 tables on 2 fields (ABC and Industry)

Eg:

TblSAPDetails:
Vendor ABC Industry
1004010 A MK

TblVendorDetails:
Vendor ABC Industry
1004010 B MK

Should somehow return the difference between the 2 tables.

Try concatonating the 3 fields for TblSAPDetails in one query and
concatonating the fields for TblVendorDetails in another. If I'm
understanding you correctly, you could then include them in a third query
with an outer join on the concatonated fields and test for nulls.

Regards,
Keith.
www.keithwilby.com

Nov 13 '05 #3

Similar topics

52835

Efficient way to compare data in Two identically structured tables.

by: Maur | last post by:

Hi all, I have 2 tables say t_OLD and t_NEW. The new has corrections for audit purposes. They are identical in all respects (i.e. new is a copy of old and then changes are made to t_new) ...

Microsoft Access / VBA

10849

How to compare fields from similar tables

by: Megan | last post by:

Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...

Microsoft Access / VBA

7112

Compare 2 mdbs

by: Vincent | last post by:

has any one seen a program to compare mdbs'. I have ran into a few of them, but none seem to really do that job. Basically what I need to do is, take 2 access mdb's and check the differences...

Microsoft Access / VBA

4966

compare tables

by: dfs9 | last post by:

In the article "Delete Duplicate Records From Access Tables" By Danny Lesandrini writes the following: This final suggestion is the most flexible and accurate. Given any table, it generates a...

Microsoft Access / VBA

6594

compare 2 tables and store result in 3rd table??

by: VMI | last post by:

I have two tables and I want to compare these two tables with a query( ie. "select * from A where B.key = A.key") and the result will be stored in a 3rd table (table C). is this possible? If...

C# / C Sharp

1872

2 tables, compare data help??

by: Mark | last post by:

by m.r.davies I have 2 tables on seperate Db's (and servers) I want to use a datareader on the first table to pick the booking ref, and then use that booking ref to query the 2nd DB when i have...

ASP.NET

1803

How to compare two table contents?

by: Shaw | last post by:

Our database is constantly updated (input data) from another DB, and sometimes it crashes our ASP.NET applications. My boss told me to write a DB utility app to check DB and make sure all apps are...

ASP.NET

4489

Variables in SP do not compare as equal when both are NULL

by: Mark A | last post by:

DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP 11). Using the SAMPLE database, tables EMP and EMLOYEE. In the followng stored procedure, 2 NULL columns (COMM) are...

DB2 Database

2648

Compare tables based on Combobox selections

by: Edd E | last post by:

Hi, I have a database to store my analyses (Access 2002, WinXp), the basic structure is: TABLE 1 = Sample Info TABLE 2 = Analysis type 1 TABLE 3 = Analysis type 2 TABLE 4 = Analysis type 3 ...

Microsoft Access / VBA

3032

How to compare entries in 2 seperate tables and store results in a 3rd table

by: chazzy69 | last post by:

Ok heres what im trying to achive i am constantly updating the information in a table but know i what know if the data im replacing it with is new, i.e. whether or not i already had the data in the...

MySQL Database

7103

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

7137

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

5403

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

4838

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

4527

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

3044

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

Networking - Hardware / Configuration

3038

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1355

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...

C# / C Sharp

234

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

General