Comparing tables and merging

[Syed(Excel)]

Dear All,

I have to download data from server every month and prepare a report.

I have two tables in access database namely as A & B. Both have some
identical & different rows of data. I wanna compare these missing data
and then merged missing field of table A into table B. Here is the
sample :

Table A

Student ID Name Class Fees
1 ABC 1 $100/-
2 BBC 1 $200/-
5 FBC 8 $500/-

Now here is Table B

Table B
Student ID Name Class Fees
2 BBC 1 $200/-
3 CCC 2 $250/-
4 EBC 6 $350/-
5 FBC 8 $500/-

************Required data in report is as below ************

Table C

Student ID Name Class Fees- A Fees B
1 ABC 1 $100/-
2 BBC 1 $200/-
3 CCC 2 $250/-
4 EBC 6 $350/-
5 FBC 8 $500/-

Hope i am clear !
Thanks for your time & support.

 

[Tom Ellison]

Dear Syed:

I recommend that you first identify various sets of rows in a table
according to what they should do.

First, there will be rows that are "new". Will you determine this by Name
or by Student ID? I will assume ID.

These can be identified using the query below:

SELECT *
FROM [Table A] A
LEFT JOIN [Table C] C
ON A.[Student ID] = C.[Student ID]
WHERE C.[Student ID] IS NULL

After checking this carefully, transform it into an Append query.

Another class of rows are those where the ID already exists. Do you want to
replace ANY column that differs? This could be:

SELECT *
FROM [Table A] A
INNER JOIN [Table C] C
ON A.[Student ID] = C.[Student ID]
WHERE A.[Name] <> C.[Name]
OR A.Class <> C.Class
OR A.Fees <> C.[Fees B]

This will show the existing rows in Table C where there is some change in
the incoming data. You can make an Update query of this after checking it.

Now, I would keep track of the incoming tables in terms of number of rows.
I would also count the number of rows that will add, that will update, or
that have no change. The query for the no change is:

FROM [Table A] A
INNER JOIN [Table C] C
ON A.[Student ID] = C.[Student ID]
WHERE A.[Name] = C.[Name]
AND A.Class = C.Class
AND A.Fees = C.[Fees B]

The count of the rows from these 3 queries needs to add up to the number of
rows in Table A. This is a good check of your new software.

Do all the work in a copy of the database first, not in the production
version. If there is a problem, you can put Table C back as it was, fix the
problem, and try again.

There are many variations possible. I would be concerned, for example, if
an incoming row attempts to change the Name of an existing student. I would
want a report of such name changes before proceeding with the updates. A
name change from Mike Smith to Michael Smith is probably OK, but a change to
Mary Johnson would be upsetting, indicating some problem in the data.

Tom Ellison

 

[Guest]

A simple UNION operation will do it as this (unlike a UNION ALL operation)
suppresses duplicated rows:

SELECT [Student ID], Name, Class, Fees
FROM [Table A]
UNION
SELECT [Student ID], Name, Class, Fees
FROM [Table B];

Ken Sheridan
Stafford, England