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]

Basically, we need to know how to identify a "changed column". This involves
1) knowing what columns you are looking for changes in and
2) know what records to compare.

The first requires field names (which I don't know) from the tables being compared.

The second is where keys come into play. How do you know that record number 1 has changes compared to record number 54 (or put another way, how do you know that record number 1 and record number 54 need to be compared).

Basically, until we have field names, and keys to join on, we can only talk generally, instead of writing the sql code which is where you want to be. This is all slightly complicated by the fact that apparently there are three tables, not two, but that's just something to take into account when doing the joins, I think, since the third table is providing additional conditions for creating joins.

 

[MHamid]

I need to compare the following columns. These columns are in both the Raw_Data_Info table (Table1) and archive_sii_data_extract table (Table2):
Issue ID
Issue Name
Issue Status
Primary Issue Owner
Secondary Issue Owner
Issue DSMT Alias Name
Issue DSMT Assigned Managed Segment Attributes
Issue DSMT Assigned Managed Geography Attributes
Issue DSMT Assigned Legal Vehicle Attributes
Issue DSMT Assigned Function Attributes
Lines Of Defense
Accept/Remediate

Need to compare the above columns from Table1 to Table2 to see if any of these columns have changed.
The Issue ID should be exactly the same. So we are looking for the Issue ID from Table1 and seeing if it exists in Table2. If the ID exists in Table2, then this is where the Previous_Month_End_Date column comes into play from Table3 (input_extract_dates). Once the Issue ID is located in Table2, then we want to go into the Previous_Month_End_Date column in Table3 and find that date in Table2 under extract_date column.
At this point, if an Issue ID from Table1 was found in Table2, then the extract_date should match to the Previous_Month_End_Date. Once these pre-conditions have been met, then we will need to compare the above columns (with the exception of Issue ID)and see if any of these columns have changed. If there are any that have been changed form prior month to current month, then these will need to be flagged in a new column. Does this make sense? I hope it does, lol.

Thank you

 

[xenou]

Not entirely. What's in the table input_extract_dates? How do you decide what Previous_month_End_Date to choose? This would only work if there was only one row in that table (so only one Previous_Month_end_Date to choose).

 

[xenou]

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),

and assuming all the fields except Issue ID are string/text data,

then this (hopefully) finds the changed issues:

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

 

[MHamid]

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.

All text fields with one memo field ... Date/Time fields for all columns in the inout_extract_dates table.


What is t1 and t2 in the code? Is that supposed to be the table names?

 

[MHamid]

I updated the code to the table names and I got he following message to enter parameter value for Raw_Data_Info.IssueID.

SELECT 
 Raw_Data_Info.[Issue ID]
FROM 
 Raw_Data_Info 
 INNER JOIN archive_sii_data_extract
 ON Raw_Data_Info.IssueID = archive_sii_data_extract.IssueID
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])
  )

 

[xenou]

Change IssueID to [Issue ID] in the SQL

 

[MHamid]

Ok, I made the change and when I ran it all I see is the Issue ID column.

Now I want to also see all the columns I mentioned previously and somehow I need to add a new column flagging that a change occurred.

For the first part, if I go to the Design View, would I just need to select the box in Show for each column or would I also need to add the columns I want.
Also, how can I tell that this data is correct?

 

[xenou]

Now I want to also see all the columns I mentioned previously and somehow I need to add a new column flagging that a change occurred.

Do you want to see the old values or the new values?

For the first part, if I go to the Design View, would I just need to select the box in Show for each column or would I also need to add the columns I want.

If you don't add new columns, you can see the changes in a query, but nothing would be saved or stored permanently. If you want to save data you have to have some kind of permanent data structure (i.e., a table, log file, new columns, etc).

Also, how can I tell that this data is correct?

You should always test your results. Ideally you create test data where you can verify that actual results match expected results, and include cases where no changes should occur so that you also know that unchanged data is also being evaluated correctly.

 

[MHamid]

Hello,

I would like to see the new values.
However, would it be possible to see what the old value(s) was/were in a new column?

Once I get this query to work the way we need it to, I will have to create a report out of it.

I'm still working on the excel version to see what results I get. Since it's a lot of data, it is taking forever for my VDI session to filter, mark the changed items, and then un-filter. I have to do this one column at a time. ugh!!!