| re: Compare two tables and give me results of fields that differ in new table.
Salad <oil@vinegar.com> wrote in message news:<l5toc.17425$V97.16889@newsread1.news.pas.ear thlink.net>...[color=blue]
> Rachel Curran wrote:
>[color=green]
> > 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[/color]
>
> 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.[/color]
Thanks, I will give the union query a go!
| 
