Results 1 to 6 of 6
  1. #1
    NalZ is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    3

    MS Access Query to show rows where one column value changes

    I have a table called test.
    In test I have an ID and a value and a date.
    The dates are ordered for each ID.
    I want to select rows for an ID, before and after a change of value, so the following example table.

    RowNum--------ID------- Value -------- Date
    1------------------001 ---------1----------- 01/01/2015
    2------------------001 ---------1----------- 02/01/2015
    3------------------001 ---------1----------- 04/01/2015


    4------------------001 ---------1----------- 05/01/2015
    5------------------001 ---------1----------- 06/01/2015
    6------------------001 ---------1----------- 08/01/2015
    7------------------001 ---------0----------- 09/01/2015
    8------------------001 ---------0----------- 10/01/2015
    9------------------001 ---------0----------- 11/01/2015
    10-----------------001 ---------1----------- 12/01/2015
    11-----------------001 ---------1----------- 14/01/2015
    12------------------002 ---------1----------- 01/01/2015
    13------------------002 ---------1----------- 04/01/2015
    14------------------002 ---------0----------- 05/01/2015
    15------------------002 ---------0----------- 07/01/2015

    The result would return rows 6, 7, 9, 10, 13, 14

    Note I do not have the rows numbered - just included in the example. Only 3 columns.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have a code bias, methinks, so I don't see a query solution. Not even a subquery solution, but then I've seen some do wonderful query things in this forum.
    If it comes down to code, I can see a recordset ordered by ID, and a loop that
    - gets the first ID and value and assigns to variables
    - moves to the next record and compares the pair to the variables
    - if the same, repeat.
    - if not, what if the ID changes but the value does not?
    when the value changes and the ID is the same, you already have the prior value and ID in the variables.

    You then do something with the 4 values - not sure what 'return rows' means. The lack of a record numbering field probably will make that more difficult. Maybe very much so.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Welcome to the forum
    Is this a homework or coursework question as the problem looks familiar from a previous post.
    Suggest you look at this example using a subquery Get the value in the previous record and check for values where the difference in values <>0

    BTW you shouldn't use Value or Date as field names as both are reserved words in Access
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    NalZ is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    3
    Hi, thanks for the welcome. No, this isn’t for a homework question but a problem at work where I’m trying to filter a lot of data (so I only want to see when transitions happen in “value” - which I’ll rename).

  5. #5
    NalZ is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    3
    ideally I could do this in a query but I’ll play around in VBA if nothing else

    What I mean by “return rows” is that the query result would only have those rows as opposed to all the data

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-09-2018, 12:54 PM
  2. Replies: 3
    Last Post: 04-03-2016, 03:00 PM
  3. Show % of current rows in total rows.
    By redbull in forum Programming
    Replies: 8
    Last Post: 03-29-2016, 09:26 AM
  4. Replies: 1
    Last Post: 07-01-2013, 08:21 AM
  5. Replies: 1
    Last Post: 01-24-2011, 03:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums