Access - Query to get Previous Active Record for Each next record.......

Hello all
I need a query that for each record I have a field that indicates what the last active record number was.

Let's say I have a table called [test1] that has fields:

[TestID]- autonumber
[Control] - long
[Voided] - Y/N

Let's say my records are:
TestID   -  Control  - 0/-1
1 ------------ 10 ------------ 0
2 ------------ 16 ------------ 0
3 ------------ 19 ------------ -1
4 ------------ 20 ------------ 0
5 ------------ 30 ------------ -1
6 ------------ 31 ------------ 0
7 ------------ 38 ------------ 0
8 ------------ 41 ------------ 0
9 ------------ 44 ------------ -1
10 ------------ 45 ------------ 0

So then the Query Result that I would want is:
TestID   -  Control  - PreviousTestID
1 ------------ 10 ------------ Null
2 ------------ 16 ------------ 1
4 ------------ 20 ------------ 2
6 ------------ 31 ------------  4
7 ------------ 38 ------------ 6
8 ------------ 41 ------------  7
10 ------------ 45 ------------- 8
wlwebbAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
Try...

Select T.*,  (select max(testID) from test1 where Voided = false and TestId<T.TestID) as PrevId
from test1 as T
where Voided = false
0
 
wlwebbAuthor Commented:
Perfect........  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.