Solved

MA Access compare table/left join

Posted on 2008-10-02
2
912 Views
Last Modified: 2013-11-29
I am trying to compare this year and last year table.  Looking for a return of any new table rows and all rows that have a change in any field.
Get all rows that have changes but not new row.
Relationship is  Include ALL records from 'New2006' and only those records from 'Old2006' where the joined fields are equal.
SELECT New2006.[Form Page Name], New2006.[Field ID], *

FROM New2006 Left JOIN Old2006 ON (New2006.[Form Page Name] = Old2006.[Form Page Name]) AND (New2006.[Field ID] = Old2006.[Field ID])

WHERE (((New2006.[DOF Field Identifier]) Not Like ([Old2006].[DOF Field Identifier]))) OR (((New2006.[Field on Form]) Not Like ([Old2006].[Field on Form]))) OR (((New2006.[Docbuilder Field Label]) Not Like ([Old2006].[Docbuilder Field Label]))) OR (((New2006.Tab) Not Like ([Old2006].[Tab]))) OR (((New2006.[DocBuilder Description]) Not Like ([Old2006].[DocBuilder Description]))) OR (((New2006.[Docbuilder Mask]) Not Like ([Old2006].[Docbuilder Mask]))) OR (((New2006.[Docbuilder Name]) Not Like ([Old2006].[Docbuilder Name]))) OR (((New2006.[Docbuilder Rekey]) Not Like ([Old2006].[Docbuilder Rekey]))) OR (((New2006.[Docbuilder Constraint]) Not Like ([Old2006].[Docbuilder Constraint]))) OR (((New2006.[ELF E-File Type]) Not Like ([Old2006].[ELF E-File Type]))) OR (((New2006.[Capture Project (Paper)]) Not Like ([Old2006].[Capture Project (Paper)]))) OR (((New2006.[Capture Project (Web)]) Not Like ([Old2006].[Capture Project (Web)]))) OR (((New2006.[Capture Project (ELF)]) Not Like ([Old2006].[Capture Project (ELF)]))) OR (((New2006.[Docbuilder Calc Field]) Not Like ([Old2006].[Docbuilder Calc Field]))) OR (((New2006.[Docbuilder Apps (FFV)]) Not Like ([Old2006].[Docbuilder Apps (FFV)]))) OR (((New2006.[Docbuilder Apps (RR)]) Not Like ([Old2006].[Docbuilder Apps (RR)]))) OR (((New2006.[Docbuilder Apps (FFH)]) Not Like ([Old2006].[Docbuilder Apps (FFH)]))) OR (((New2006.[Docbuilder Apps (ICR)]) Not Like ([Old2006].[Docbuilder Apps (ICR)]))) OR (((New2006.[Rekey Level]) Not Like ([Old2006].[Rekey Level]))) OR (((New2006.[Pass (Primary)]) Not Like ([Old2006].[Pass (Primary)]))) OR (((New2006.[Pass (Audit)]) Not Like ([Old2006].[Pass (Audit)]))) OR (((New2006.[Pass (Tax Policy)]) Not Like ([Old2006].[Pass (Tax Policy)]))) OR (((New2006.[Transmission Length]) Not Like ([Old2006].[Transmission Length]))) OR (((New2006.[Transmission Format]) Not Like ([Old2006].[Transmission Format]))) OR (((New2006.[Start Primary]) Not Like ([Old2006].[Start Primary]))) OR (((New2006.[Primary Record]) Not Like ([Old2006].[Primary Record]))) OR (((New2006.[Start Audit]) Not Like ([Old2006].[Start Audit]))) OR (((New2006.[Audit Record]) Not Like ([Old2006].[Audit Record]))) OR (((New2006.[TP Record PART]) Not Like ([Old2006].[TP Record PART]))) OR (((New2006.[TP START PART]) Not Like ([Old2006].[TP START PART]))) OR (((New2006.[TP Record PROP]) Not Like ([Old2006].[TP Record PROP]))) OR (((New2006.[TP START PROP]) Not Like ([Old2006].[TP START PROP]))) OR (((New2006.[TP Record CORP]) Not Like ([Old2006].[TP Record CORP]))) OR (((New2006.[TP START CORP]) Not Like ([Old2006].[TP START CORP]))) OR (((New2006.[Default Value]) Not Like ([Old2006].[Default Value]))) OR (((New2006.[Null OK]) Not Like ([Old2006].[Null OK]))) OR (((New2006.[Min Val]) Not Like ([Old2006].[Min Val]))) OR (((New2006.[Max Val]) Not Like ([Old2006].[Max Val]))) OR (((New2006.[Dec Ind]) Not Like ([Old2006].[Dec Ind]))) OR (((New2006.[Screening Review]) Not Like ([Old2006].[Screening Review]))) OR (((New2006.[Data Capture Rules]) Not Like ([Old2006].[Data Capture Rules]))) OR (((New2006.[Verification/System Rules]) Not Like ([Old2006].[Verification/System Rules]))) OR (((New2006.[Record Layout Comment]) Not Like ([Old2006].[Record Layout Comment]))) OR (((New2006.Notes) Not Like ([Old2006].[Notes]))) OR (((([New2006].[ELF Rules / Notes])) Not Like ([Old2006].[ELF Rules / Notes]))) OR (((New2006.[WEB Rules / Notes]) Not Like ([Old2006].[WEB Rules / Notes])))

ORDER BY New2006.[Form Page Name], New2006.[Field ID];

Open in new window

0
Comment
Question by:JuliD
2 Comments
 
LVL 6

Assisted Solution

by:mgalig1010
mgalig1010 earned 500 total points
ID: 22628184
Hi JuliD,

Assuming that  [Form Page Name] uniquely identifies each record, I would :
1) create a query to get you the [Form Page Name] of the records that have changed (Query1). You could use the query you included with your question.
2) create another to get the  [Form Page Name] of the new records (Query2)
3) create a Union query to put all of your results together (see code snippet)
4) create a query that takes every record from the Union query in step 3 and only the records from New2006 and Old2006 where the [Form Page Name] matches.
Select [Form Page Name] From Query1

Union Select All [Form Page Name] From Query2

Open in new window

0
 

Accepted Solution

by:
JuliD earned 0 total points
ID: 22635080
My boss had another answer:  he added "OR (((Old2006.[Field ID]) Is Null))" to Query1 and it worked also.


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now