Solved

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

Posted on 2013-01-20
2
386 Views
Last Modified: 2013-01-20
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
0
Comment
Question by:wlwebb
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38798292
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
 

Author Closing Comment

by:wlwebb
ID: 38798316
Perfect........  Thank you!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now