Solved

MA Access compare table/left join

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

829 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