A tricky select Query for me

[swark]

Running MS Access 2000 ver 9.0.3821 SR-1
I have a table thus:
(ID)(project name)(level)
1 xxx 1
2 xxx 2
3 xxx 1
4 xxx 1
5 xxx 1
6 xxx 2
7 xxx 2
etc...
I need query that returns all the level 2 entries PLUS the preceeding level
1, i.e. ID 1, 2 (not 3,4) 5,6,7.
Can anyone get me started? -thanks in advance.

 

[Jeff Boyce]

Are you saying that you want to see records where [level] = 1 or 2?

Or are you saying you want to see records where [level] = n or n-1 (and you
provide the 'n')?

If the former, open your query in design view, add the table and the
field(s) you want to see in the output and put something like the following
in the selection criterion under [level]:

1 or 2

If the latter, set up the query the same way and put something like the
following in the selection criterion:

[Enter a level] or [Enter a level]-1

Then run the query.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Barrows [MVP]]

Running MS Access 2000 ver 9.0.3821 SR-1
I have a table thus:
(ID)(project name)(level)
1 xxx 1
2 xxx 2
3 xxx 1
4 xxx 1
5 xxx 1
6 xxx 2
7 xxx 2
etc...
I need query that returns all the level 2 entries PLUS the preceeding
level 1, i.e. ID 1, 2 (not 3,4) 5,6,7.
Can anyone get me started? -thanks in advance.

I think I understand ...
Try:
SELECT ID FROM table WHERE level = 2
UNION ALL
SELECT t1.ID
FROM table As t1 join table as t2 on t1.ID = t2.ID - 1
WHERE t1.level = 1 AND t2.level = 2
ORDER BY ID

 

[swark]

Jeff- what I'm trying to get from:
1 xxx 1
2 xxx 2
3 xxx 1
4 xxx 1
5 xxx 1
6 xxx 2
7 xxx 2
-IS-
1 xxx 1
2 xxx 2
5 xxx 1
6 xxx 2
7 xxx 2
I want [ID]=1 because is has [level]=2 after it.
I do not want 3 and 4 because they do not have [level]=2 after the them.
I want [ID]=5 and the next 2 lines while [level]=2....

Meanwhile I'm trying Mr. Barrows union query (with no success yet).

Thanks SW

 

[John Spencer]

SELECT A.ID
FROM YourTable as A INNER JOIN YourTable As B
ON A.ID = B.ID -1
WHERE B.Level = 2 and A.Level=1

UNION

SELECT B.ID
FROM YourTable As B
WHERE B.Level = 2

IF you want both ID in one record
SELECT A.ID as Level1ID
, B.ID as Level2ID
FROM YourTable as A INNER JOIN YourTable As B
ON A.ID = B.ID -1
WHERE B.Level = 2 and A.Level=1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bob Barrows [MVP]]

Jeff- what I'm trying to get from:
1 xxx 1
2 xxx 2
3 xxx 1
4 xxx 1
5 xxx 1
6 xxx 2
7 xxx 2
-IS-
1 xxx 1
2 xxx 2
5 xxx 1
6 xxx 2
7 xxx 2
I want [ID]=1 because is has [level]=2 after it.
I do not want 3 and 4 because they do not have [level]=2 after the
them. I want [ID]=5 and the next 2 lines while [level]=2....

Meanwhile I'm trying Mr. Barrows union query (with no success yet).

Thanks SW

I can't help you if you don't describe your symptoms ...