MS Access Query to show rows where one column value changes (1 Viewer)

NalZ

New member
Local time
Today, 06:50
Joined
Dec 5, 2019
Messages
4
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:50
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! If row 11 was a 0, would you have wanted row 12 in the result as well, even if it's a different ID? Just checking...
 

NalZ

New member
Local time
Today, 06:50
Joined
Dec 5, 2019
Messages
4
Hi, yes I want to capture all different IDs as well, but no not if the value changes for new IDs
 

NalZ

New member
Local time
Today, 06:50
Joined
Dec 5, 2019
Messages
4
Micron, cross posted to make use of the different platforms and expertise available... this is time sensitive...
 

NalZ

New member
Local time
Today, 06:50
Joined
Dec 5, 2019
Messages
4
I had planned on posting the link to the correct response once I had one that worked on the still working forum, but ok....
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:50
Joined
Aug 11, 2003
Messages
11,696
Does the basis of this work on the date or on the ID?
is it on purpose you are missing some dates? Or should both date and ID be "closed"

It is realively easy to make a query such to join the previous rownum to the "current" rownum
Assuming you have rownum.... and it enforces your "order"
Code:
Select *
From Yourtable  a
left join Yourtable b on a.Rownum + 1 = b.rownum
where a.Value <> b.Value
if you only want to search where ID is the same
Code:
Select *
From Yourtable  a
left join Yourtable b on a.Rownum + 1 = b.rownum and a.ID = b.ID
where a.Value <> b.Value

this is time sensitive...
Real stuff that is real important and on a time constraint ... not to sound like an arse but if it is that time sensitive and that important perhaps you need a more senior developer working this issue.
 

Users who are viewing this thread

Top Bottom