By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Compare two tables and give me results of fields that differ in new table.

P: n/a
Please can anybody help me with the following:

I have two separate excel spreadsheets that I have imported into
access, each sheet holds the same fields. Both spreadsheets hold all
information for employees however, they are from two different dates
so by comparing both sheets I should to be able to check which records
DO NOT match. From the records that DO NOT match I need to locate the
information that has changed. But I only want to bring in the fields
that have changed, so for example 1:

Spreadsheet1 - ID No:12345 DOB 28/02/78 Grade A Department Finance
Hours 37
Spreadsheet2 - ID No:12345 DOB 28/02/78 Grade B Department HR Hours 37

Result of query = ID No: 12345 DOB (blank)Grade B Department HR Hours
(blank)

Example 2:

Spreadsheet1 - ID No:56789 DOB 28/09/82 Grade B Department HR Hours 37
Spreadsheet2 - ID No:56789 DOB 28/09/82 Grade BS Department HR Hours
25

Result of query = ID No: 56789 DOB (blank)Grade BS Department
(blank)Hours 25

Any help would be a great help, thankyou in advance

Rachel
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Rachel Curran wrote:
Please can anybody help me with the following:

I have two separate excel spreadsheets that I have imported into
access, each sheet holds the same fields. Both spreadsheets hold all
information for employees however, they are from two different dates
so by comparing both sheets I should to be able to check which records
DO NOT match. From the records that DO NOT match I need to locate the
information that has changed. But I only want to bring in the fields
that have changed, so for example 1:

Spreadsheet1 - ID No:12345 DOB 28/02/78 Grade A Department Finance
Hours 37
Spreadsheet2 - ID No:12345 DOB 28/02/78 Grade B Department HR Hours 37

Result of query = ID No: 12345 DOB (blank)Grade B Department HR Hours
(blank)

Example 2:

Spreadsheet1 - ID No:56789 DOB 28/09/82 Grade B Department HR Hours 37
Spreadsheet2 - ID No:56789 DOB 28/09/82 Grade BS Department HR Hours
25

Result of query = ID No: 56789 DOB (blank)Grade BS Department
(blank)Hours 25

Any help would be a great help, thankyou in advance

Rachel


What a PITA.

I would probably write code to scan through all records and stuff the
differences into a temp table.

I suppose you could create a query and compare each field. Ex: create
a new query and add Table1 and Table2, draw a link between IDNo. THen
drag the id field down. In all other columns put a compare. Ex:
IIF(Table1.DOB = Table2.DOB,True,False)
Do this for all fields to compare.

You could now do a union query. Show all IDs and DOB not equal and join
with all IDs and Department not equal.

This might be a better method. You could create a form that displays
fields from both tables...ex: show DOB from both tables side by side or
one on top, the other below it. Then in the OnCurrent event run a
routine that compares the value from Table1 to the value in Table2. If
they match, disable the field so the value can't be changed. If they
don't match, the fields are enabled so they can be modified. Ex:
Me.T1DOB.Enabled = (Me.T1DOB = Me.T2DOB)
Me.T2DOB.Enabled = (Me.T1DOB = Me.T2DOB)
Now any fields that are enabled indicates a difference.
Nov 12 '05 #2

P: n/a
Salad <oi*@vinegar.com> wrote in message news:<l5*******************@newsread1.news.pas.ear thlink.net>...
Rachel Curran wrote:
Please can anybody help me with the following:

I have two separate excel spreadsheets that I have imported into
access, each sheet holds the same fields. Both spreadsheets hold all
information for employees however, they are from two different dates
so by comparing both sheets I should to be able to check which records
DO NOT match. From the records that DO NOT match I need to locate the
information that has changed. But I only want to bring in the fields
that have changed, so for example 1:

Spreadsheet1 - ID No:12345 DOB 28/02/78 Grade A Department Finance
Hours 37
Spreadsheet2 - ID No:12345 DOB 28/02/78 Grade B Department HR Hours 37

Result of query = ID No: 12345 DOB (blank)Grade B Department HR Hours
(blank)

Example 2:

Spreadsheet1 - ID No:56789 DOB 28/09/82 Grade B Department HR Hours 37
Spreadsheet2 - ID No:56789 DOB 28/09/82 Grade BS Department HR Hours
25

Result of query = ID No: 56789 DOB (blank)Grade BS Department
(blank)Hours 25

Any help would be a great help, thankyou in advance

Rachel


What a PITA.

I would probably write code to scan through all records and stuff the
differences into a temp table.

I suppose you could create a query and compare each field. Ex: create
a new query and add Table1 and Table2, draw a link between IDNo. THen
drag the id field down. In all other columns put a compare. Ex:
IIF(Table1.DOB = Table2.DOB,True,False)
Do this for all fields to compare.

You could now do a union query. Show all IDs and DOB not equal and join
with all IDs and Department not equal.

This might be a better method. You could create a form that displays
fields from both tables...ex: show DOB from both tables side by side or
one on top, the other below it. Then in the OnCurrent event run a
routine that compares the value from Table1 to the value in Table2. If
they match, disable the field so the value can't be changed. If they
don't match, the fields are enabled so they can be modified. Ex:
Me.T1DOB.Enabled = (Me.T1DOB = Me.T2DOB)
Me.T2DOB.Enabled = (Me.T1DOB = Me.T2DOB)
Now any fields that are enabled indicates a difference.


Thanks, I will give the union query a go!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.