Solved

MA Access compare table/left join

Posted on 2008-10-02
2
916 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

831 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