Power Query Course in Spanish
Thanks Thanks:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 38

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

  1. #11
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,892
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  2. #12
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Last edited by MHamid; Feb 14th, 2018 at 09:31 AM.

  3. #13
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,892
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #14
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,892
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    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:

    Code:
    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])
    		)

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #15
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    Last edited by MHamid; Feb 14th, 2018 at 12:53 PM.

  6. #16
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Code:
    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])
      )

  7. #17
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,892
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    Change IssueID to [Issue ID] in the SQL

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #18
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  9. #19
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,892
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Last edited by xenou; Feb 14th, 2018 at 05:07 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #20
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •