wlwebb
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER