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]

Generally you would write a query for this kind of thing. Some sample data would help if you want a specific example.

 

[MHamid]

This is the current SQL that I have.

SELECT Raw_Data_Info.ID, 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]
FROM input_extract_dates LEFT JOIN (archive_sii_data_extract LEFT JOIN Raw_Data_Info ON archive_sii_data_extract.[Issue ID] = Raw_Data_Info.[Issue ID]) ON input_extract_dates.Previous_Month_End_Date = archive_sii_data_extract.extract_date;

 

[xenou]

Does this query show you the new data?

 

[MHamid]

no, it's blank when I select DataView.

I didn't create a criteria that would compare the raw data table with the archive data table using the Prior month selection from the extract date table within the archive data table. And I have not created a column that would flag the ones that have changed from prior month to current month. This is where I am confused as to how to approach.

The archive data table will have everything for various dates. We just want to see the prior month (based off of the selection in the extract table). Raw data table will always be current month.

Thank you

 

[MHamid]

Correcting first sentence

no, it's blank when I select DataView.

When I select "Data View" it brings back some data from the raw data table that matches with the ID from the archive data. The column that I brought over from the archive table (Issue ID), will show blank rows indicating that these IDs are not in the current month, but I think because of the missing criteria I can't tell if these are changed or not.

 

[xenou]

The way you have written your query (using left join) would suggest you are pulling new data that wasn't there before. I'm not sure what you mean by missing criteria. Things that aren't there aren't changed, except in the sense that the change is new data.

To get changed data you have to join on the primary keys and then compare the other relevant columns for non-matches.

Note that to get new and changed data you have to do both - pull a set of records that are new (not there before) and also add to it another set of data that is there before but now has changes.

You're probably going to have to explain what you mean by an extract table. That is not self-descriptive so I don't know how it relates to the "raw data" table or the "archive table" (these are not entirely self-descriptive either although I would assume raw data is some kind of new data that has just been retrieved and archive data is old data that is no longer considered "current".

 

[MHamid]

Let's see if I can explain this.

Table 1 (Raw Data) only has current month data (FYI - these are the columns to check if changes were made from prior month).
Table 2 (Archive Data) has all data (start date until prior month).
Tabel 3 (Extract Dates) only has 3 columns (current extract date, previous month date, and two month's back. This is based on the selections a user makes in the form.
The "previous month date" in Table3 is joined to the extract date in Table2. These are the only tables with dates. Table 1 does not have dates. So the idea is that by joining the dates between Table3 and Table2, it will only bring back the prior month dates from Table2 (archive data) because we are only interested in seeing what changed from prior month to current month.

When I say "missing criteria", I'm just assuming that I would need a criteria for each column (in the Design View) for a comparison. How else would it know to look for a change in each column from the raw data to the archive data? I'm not interested in new data, I'm only interested in data that has changed from previous to current.

I used the unmatched query wizard, but I can't figure out how to join Table3 (extract date) to pick up just the prior month data.

Thank you

 

[xenou]

That's weird. It doesn't sound like you have any primary keys in your tables. I don't know how you can write good queries in the absence of primary keys. You'll have to provide some sample data (hopefully, it's not really big tables here with lots of columns).

 

[MHamid]

These are tables that someone else created.
Table 1 (Raw Data) has 12 columns plus an auto-generated ID (these 12 columns were taken from the Archive data so these are shared columns)
Table 2 (Archive Data) has way too many columns (98) plus an auto-generated ID
Table 3 (Extract Dates) only has 3 columns ... as I mentioned in my last post

The query result will be based on the Raw Data, so there will be 12 columns ... plus the flagged column showing the Issue ID with changes

It doesn't look like I can use primary key in the Archive Table since the Issue ID can be duplicated based on the date.
I believe a primary key can be assigned to the Raw Data Table for Issue ID ... doesn't look like this will be duplicated here since it's current date.

I'm also currently working on doing this the long way in Excel to see if there should be any results. File is so big that it freezes my computer with every change that I do, lol.

What kind of sample are you looking to see?