Access Query to find changes from prior to current and flag if there are any changes

[MHamid]

Hello,

I've been googling this for a little while now and can't seem to find what I am looking for. I'm hoping someone can give me some direction as to where I can find this information or if they can provide some information on this topic.

I am trying to create a query in access 2010 to see if all fields in one table have changed from prior month to current month. If yes, flag in in another column. There are three tables, all fields table (Table1), archive table (Table2), and extract date (Table3). I have joined Table1 with Table2 by using Issue ID and Table 3 is joined with Table 2 by extract date (from table2) and Previous_Month_Date (from table 3).

I am a newbie when it comes to this, so please let me know what I am missing. I am not sure what to place in the columns below. I am assuming that it would be all columns from Table1, but I'm not sure how the comparison will work between Table1 and Table2 based on the dates from table3 and table2. Where can I find this info or can someone explain this to me?

Thank you

 

[xenou]

You can add a validation to the table. Just validate that the primary key must equal the current primary key.

For instance, if it has an autonumber field ID with a current value of 1, then the validation rule would be:

= 1

and then no more rows can be added.



The query you wrote should work I guess - there's a lot of redundancy in the IIFs (most of it could go one time in the where clause).

 

[MHamid]

I created a copy of the query and changed the SQL Code, but it brings back no results and there should be some results.

SELECT Raw_Data_Info.[Issue ID], Raw_Data_Info.[Issue Name], Raw_Data_Info.[Issue Status], Raw_Data_Info.[Primary Issue Owner], Raw_Data_Info.[Secondary Issue Owner], Raw_Data_Info.[Issue DSMT Alias Name], Raw_Data_Info.[Issue DSMT Assigned Managed Segment Attributes], Raw_Data_Info.[Issue DSMT Assigned Managed Geography Attributes], Raw_Data_Info.[Issue DSMT Assigned Legal Vehicle Attributes], Raw_Data_Info.[Issue DSMT Assigned Function Attributes], Raw_Data_Info.[Lines Of Defense], Raw_Data_Info.[Accept/Remediate], Raw_Data_Info.[Flagged as Changed]

FROM Raw_Data_Info INNER JOIN archive_sii_data_extract ON Raw_Data_Info.[Issue ID] = archive_sii_data_extract.[Issue ID]

WHERE (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue Name])<>Nz([archive_sii_data_extract].[Issue Name])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue Status])<>Nz([archive_sii_data_extract].[Issue Status])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Primary Issue Owner])<>Nz([archive_sii_data_extract].[Primary Issue Owner])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Secondary Issue Owner])<>Nz([archive_sii_data_extract].[Secondary Issue Owner])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue DSMT Alias Name])<>Nz([archive_sii_data_extract].[Issue DSMT Alias Name])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue DSMT Assigned Managed Segment Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Managed Segment Attributes])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue DSMT Assigned Managed Geography Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Managed Geography Attributes ])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue DSMT Assigned Legal Vehicle Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Legal Vehicle Attributes])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue DSMT Assigned Function Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Function Attributes])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Lines Of Defense])<>Nz([archive_sii_data_extract].[Lines of Defense])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates))) OR (((Raw_Data_Info.[Issue ID])=[archive_sii_data_extract].[Issue ID]) AND ((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Accept/Remediate])<>Nz([archive_sii_data_extract].[Accept/Remediate])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates)));

 

[MHamid]

The below code bring back an error message stating "Expression too complex in query expression".

SELECT Raw_Data_Info.[Issue ID], Raw_Data_Info.[Issue Name], Raw_Data_Info.[Issue Status], Raw_Data_Info.[Primary Issue Owner], Raw_Data_Info.[Secondary Issue Owner], Raw_Data_Info.[Issue DSMT Alias Name], Raw_Data_Info.[Issue DSMT Assigned Managed Segment Attributes], Raw_Data_Info.[Issue DSMT Assigned Managed Geography Attributes], Raw_Data_Info.[Issue DSMT Assigned Legal Vehicle Attributes], Raw_Data_Info.[Issue DSMT Assigned Function Attributes], Raw_Data_Info.[Lines Of Defense], Raw_Data_Info.[Accept/Remediate], Raw_Data_Info.[Flagged as Changed]
FROM Raw_Data_Info INNER JOIN archive_sii_data_extract ON Raw_Data_Info.[Issue ID] = archive_sii_data_extract.[Issue ID]
WHERE (((Raw_Data_Info.[Flagged as Changed])=Iif([Raw_Data_Info].[Issue ID]=[archive_sii_data_extract].[Issue ID],IIf((Nz([Raw_Data_Info].[Issue Name])<>Nz([archive_sii_data_extract].[Issue Name])) OR IIf((Nz([Raw_Data_Info].[Issue Status])<>Nz([archive_sii_data_extract].[Issue Status]) OR IIf((Nz([Raw_Data_Info].[Primary Issue Owner])<>Nz([archive_sii_data_extract].[Primary Issue Owner]) OR IIf((Nz([Raw_Data_Info].[Secondary Issue Owner])<>Nz([archive_sii_data_extract].[Secondary Issue Owner])) OR IIf((Nz([Raw_Data_Info].[Issue DSMT Alias Name])<>Nz([archive_sii_data_extract].[Issue DSMT Alias Name])) OR IIf((Nz([Raw_Data_Info].[Issue DSMT Assigned Managed Segment Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Managed Segment Attributes])) OR IIf((Nz([Raw_Data_Info].[[Issue DSMT Assigned Managed Geography Attributes])<>Nz([archive_sii_data_extract].[[Issue DSMT Assigned Managed Geography Attributes]))  OR IIf((Nz([Raw_Data_Info].[Issue DSMT Assigned Legal Vehicle Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Legal Vehicle Attributes]))  OR IIf((Nz([Raw_Data_Info].[Issue DSMT Assigned Function Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Function Attributes]))  OR IIf((Nz([Raw_Data_Info].[Lines Of Defense])<>Nz([archive_sii_data_extract].[Lines Of Defense])) OR IIf((Nz([Raw_Data_Info].[Accept/Remediate])<>Nz([archive_sii_data_extract].[Accept/Remediate])),"Yes","No")) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates)))

 

[xenou]

I'll just repost my SQL from Post number 16, with an update included at the top (instead of select). That is how you would write SQL. Using numerous IIF functions isn't adding anything useful to this query at all.

UPDATE
 Raw_Data_Info 
 INNER JOIN archive_sii_data_extract
 ON Raw_Data_Info.IssueID = archive_sii_data_extract.IssueID
SET
 [Flagged as Changed] = "Yes" 
WHERE
 Raw_Data_Info.[Issue ID] = archive_sii_data_extract.[Issue ID]
 AND archive_sii_data_extract.extract_date = (SELECT Previous_Month_End_Date FROM input_extract_dates)
 AND
  (
  Nz(Raw_Data_Info.[Issue Name]) <> Nz(archive_sii_data_extract.[Issue Name])
  OR Nz(Raw_Data_Info.[Issue Status]) <> Nz(archive_sii_data_extract.[Issue Status]) 
  OR Nz(Raw_Data_Info.[Primary Issue Owner]) <> Nz(archive_sii_data_extract.[Primary Issue Owner]) 
  OR Nz(Raw_Data_Info.[Secondary Issue Owner]) <> Nz(archive_sii_data_extract.[Secondary Issue Owner]) 
  OR Nz(Raw_Data_Info.[Issue DSMT Alias Name]) <> Nz(archive_sii_data_extract.[Issue DSMT Alias Name]) 
  OR Nz(Raw_Data_Info.[Issue DSMT Assigned Managed Segment Attributes]) <> Nz(archive_sii_data_extract.[Issue DSMT Assigned Managed Segment Attributes]) 
  OR Nz(Raw_Data_Info.[Issue DSMT Assigned Managed Geography Attributes]) <> Nz(archive_sii_data_extract.[Issue DSMT Assigned Managed Geography Attributes]) 
  OR Nz(Raw_Data_Info.[Issue DSMT Assigned Legal Vehicle Attributes]) <> Nz(archive_sii_data_extract.[Issue DSMT Assigned Legal Vehicle Attributes]) 
  OR Nz(Raw_Data_Info.[Issue DSMT Assigned Function Attributes]) <> Nz(archive_sii_data_extract.[Issue DSMT Assigned Function Attributes]) 
  OR Nz(Raw_Data_Info.[Lines Of Defense]) <> Nz(archive_sii_data_extract.[Lines Of Defense]) 
  OR Nz(Raw_Data_Info.[Accept/Remediate]) <> Nz(archive_sii_data_extract.[Accept/Remediate])
  )

 

[MHamid]

I just updated the code with your update and I'm getting an error message "Microsoft Access can't update 2637 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rules violations."

 

[MHamid]

Hello,

Never mind, I corrected the mistake.

I updated this:

SET
 [Flagged as Changed] = "Yes"

to this and it works now

SET
 [Flagged as Changed] = Yes

Thank you for all your help. On to my next task

 

[MHamid]

How would you recommend I make a report to show these differences?

 

[xenou]

I would simply show the new records and the old records (all of them, changed or not) in one row. Only records that have some change in one of the fields, of course.

An alternative is to show them in two rows (so you can see the old/new right next to each other, but in alternating rows - which makes finding the changes easier.

Another option is to put the data in Excel since Excel formulas can also be used and many people have better Excel skills than SQL skills.

These are the simplest approaches and can be done without having a lot of advanced skills in Access or SQL since it's a pretty straightforward query.