?
Solved

MA Access compare table/left join

Posted on 2008-10-02
2
Medium Priority
?
935 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

771 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