Learn Power Query
Thanks Thanks:  0
Page 4 of 4 FirstFirst ... 234
Results 31 to 38 of 38

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

  1. #31
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,862
    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

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

    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. #32
    Board Regular
    Join Date
    Jan 2013
    Posts
    284
    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 created a copy of the query and changed the SQL Code, but it brings back no results and there should be some results.

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

  3. #33
    Board Regular
    Join Date
    Jan 2013
    Posts
    284
    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

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

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

  4. #34
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,862
    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

    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.

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

    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. #35
    Board Regular
    Join Date
    Jan 2013
    Posts
    284
    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 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."

  6. #36
    Board Regular
    Join Date
    Jan 2013
    Posts
    284
    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,

    Never mind, I corrected the mistake.

    I updated this:
    Code:
    SET
     [Flagged as Changed] = "Yes"
    to this and it works now
    Code:
    SET
     [Flagged as Changed] = Yes

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

  7. #37
    Board Regular
    Join Date
    Jan 2013
    Posts
    284
    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

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

  8. #38
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,862
    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

    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.
    Last edited by xenou; Feb 20th, 2018 at 01:01 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

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
  •