Solved

MA Access compare table/left join

Posted on 2008-10-02
2
922 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

742 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