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 easily get the old and new values. I don't now what a VDI session is.

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

 

[MHamid]

VDI - Virtual Desktop Infrastructure. Basically, a server based computer that hosts a desktop operating system.
Since I am on a VDI, large data becomes impossible to deal with and frequently hangs up my session for long periods of time making it difficult to manipulate large data manually to get the result that I need to compare to the database.

Would it be possible to get this into one column? Data is kind of looking messing.
Can we just add a column that would say where the change occurred instead of adding columns for old and new data?
The data that we should see in the results is from the Raw_Data_Infor tab. So that data is fine to bring back, I just need a new column stating that there was a change (Yes/No) and possibly another column stating where there was change (multiple fields if possible into the 1 column).

 

[xenou]

Sorry, I don't have time for working out solutions like that. You could flag changes in one column (yes/no). Creating a list of columns that change would involve some extra logic (of some kind). Not really something I do very often.

 

[MHamid]

Oh I didn't mention it to be solved ... I was just complaining about how my session is making things a little bit harder.

Ok, no problem. I'll try to figure something out.

Thank you

 

[MHamid]

I created a column labeled as "Flagged as Changed" and I have this formula to go under that field but I am getting a Data Type mismatch in criteria expression.

Is there another way to do this formula? Am I getting this error message because the column name "Flagged as Changed is not in the criteria expression?

((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue Name])<>Nz([archive_sii_data_extract].[Issue Name])),"Yes","No"))

 

[xenou]

All i see is an expression.
Is this in a query? What is the rest of it?

 

[MHamid]

yes, this is in the same query we've been working on. This is part of the query that I just added attempting to get what I want into one field.

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.[Issue Name])<>Nz([archive_sii_data_extract].[Issue Name])) AND [COLOR=#FF0000]((Raw_Data_Info.[Flagged as Changed])=IIf((Nz([Raw_Data_Info].[Issue Name])<>Nz([archive_sii_data_extract].[Issue Name])),"Yes","No")) [/COLOR]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.[Issue Status])<>Nz([archive_sii_data_extract].[Issue Status])) 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.[Primary Issue Owner])<>Nz([archive_sii_data_extract].[Primary Issue Owner])) 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.[Secondary Issue Owner])<>Nz([archive_sii_data_extract].[Secondary Issue Owner])) 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.[Issue DSMT Alias Name])<>Nz([archive_sii_data_extract].[Issue DSMT Alias Name])) 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.[Issue DSMT Assigned Managed Segment Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Managed Segment Attributes])) 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.[Issue DSMT Assigned Managed Geography Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Managed Geography Attributes])) 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.[Issue DSMT Assigned Legal Vehicle Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Legal Vehicle Attributes])) 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.[Issue DSMT Assigned Function Attributes])<>Nz([archive_sii_data_extract].[Issue DSMT Assigned Function Attributes])) 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.[Lines Of Defense])<>Nz([archive_sii_data_extract].[Lines Of Defense])) 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.[Accept/Remediate])<>Nz([archive_sii_data_extract].[Accept/Remediate])) AND ((archive_sii_data_extract.extract_date)=(SELECT Previous_Month_End_Date FROM input_extract_dates)));

 

[xenou]

The data type mismatch could be anywhere in that entire query. What is the data type of "Flagged as Changed". What values do you put in that column?

 

[xenou]

I mentioned a big assumption which you never responded to.

Assuming above answer is that there is only row in input_extract_dates (which you have to monitor rigorously, if that is the case - preferably by making it impossible to have more than two rows),

So ... how many rows are in the table input_extract_dates?

 

[MHamid]

Duh, the Data Type was set to Yes/No. I just changed it to text. Still testing to see if this works or not.
I originally wanted the values to shows with a check mark, but if it's a text Data Type, then "Yes"/"No" is ok for now.

As for your post #29 . I did answer this question in my post#15.

There is only one row in the table input_extract_dates. So the date under the Previous_month_end_date will always be just one date that is updated every month via a Form.

As I mentioned previously, I am working with someone else creating this database and he created that table. I'm not entirely certain yet as to how we are going to ensure that the table will only stay at 1 row being updated monthly.