473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

MS Access iterators?

I have an application that stores data using iterators (as below)

ID Description Level
600 Demolition -1
670 Demo Exterior Flat Work 0
675 Remove Foundation Walls 0
680 Remove Concrete Stoops/Steps 0
900 Concrete Curb Cutting & Coring -1
910 Core Through Existining 0

I want to retreive the data from 0600 upto the next record with a
"Level = -1."

SELECT ID, Description, Level
FROM Table
WHERE ID > 600 but < Next Record with "Level= -1"

Thanks for your time!

Feb 7 '06 #1
3 1332
jibber wrote:
I have an application that stores data using iterators (as below)

ID Description Level
600 Demolition -1
670 Demo Exterior Flat Work 0
675 Remove Foundation Walls 0
680 Remove Concrete Stoops/Steps 0
900 Concrete Curb Cutting & Coring -1
910 Core Through Existining 0

I want to retreive the data from 0600 upto the next record with a
"Level = -1."

SELECT ID, Description, Level
FROM Table
WHERE ID > 600 but < Next Record with "Level= -1"

Thanks for your time!


I recommend adding another field so that you don't have to rely on the
order of records in the table. You might try something like (air SQL):

SELECT ID, Description, Level, GroupNumber FROM tblWorkCategories WHERE
GroupNumber = (SELECT GroupNumber FROM tblWorkCategories WHERE ID =
600) AND Level <> -1 ORDER BY ID;

That is, ID's in the 600's would have their own GroupNumber (Long).
The Level <> -1 excludes the record where ID = 600 and returns the
rest.

James A. Fortune
CD********@FortuneJames.com

Feb 7 '06 #2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What do you mean by "next record"? There is no order to the rows in a
table. The only order is imposed by the ORDER BY clause in a query, or
implied in the criteria (the WHERE clause) of the query.

If by "next" you mean the next ID number in numerical order; and, your
example would mean that ID is 900 would be the "next" row with a Level
of -1, then try this:

SELECT ID, Description, Level
FROM Table
WHERE ID >= 600
AND ID < (SELECT MIN(ID) FROM Table
WHERE ID > 600 AND Level = -1)

The "next" row is implied in the subquery.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+kDNoechKqOuFEgEQKuygCdFd7x1lXTZEUNDDB9Gp31SG VUrBkAn1Hp
q3IbP1S7KFWPMFfjbWhFxl00
=3LC+
-----END PGP SIGNATURE-----
jibber wrote:
I have an application that stores data using iterators (as below)

ID Description Level
600 Demolition -1
670 Demo Exterior Flat Work 0
675 Remove Foundation Walls 0
680 Remove Concrete Stoops/Steps 0
900 Concrete Curb Cutting & Coring -1
910 Core Through Existining 0

I want to retreive the data from 0600 upto the next record with a
"Level = -1."

SELECT ID, Description, Level
FROM Table
WHERE ID > 600 but < Next Record with "Level= -1"

Feb 7 '06 #3

"MGFoster" <me@privacy.com> wrote in message
news:Zq******************@newsread2.news.pas.earth link.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What do you mean by "next record"? There is no order to the rows in a
table. The only order is imposed by the ORDER BY clause in a query, or
implied in the criteria (the WHERE clause) of the query.

If by "next" you mean the next ID number in numerical order; and, your
example would mean that ID is 900 would be the "next" row with a Level
of -1, then try this:

SELECT ID, Description, Level
FROM Table
WHERE ID >= 600
AND ID < (SELECT MIN(ID) FROM Table
WHERE ID > 600 AND Level = -1)

The "next" row is implied in the subquery.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+kDNoechKqOuFEgEQKuygCdFd7x1lXTZEUNDDB9Gp31SG VUrBkAn1Hp
q3IbP1S7KFWPMFfjbWhFxl00
=3LC+
-----END PGP SIGNATURE-----
jibber wrote:
I have an application that stores data using iterators (as below)

ID Description Level
600 Demolition -1
670 Demo Exterior Flat Work 0
675 Remove Foundation Walls 0
680 Remove Concrete Stoops/Steps 0
900 Concrete Curb Cutting & Coring -1
910 Core Through Existining 0

I want to retreive the data from 0600 upto the next record with a
"Level = -1."

SELECT ID, Description, Level
FROM Table
WHERE ID > 600 but < Next Record with "Level= -1"


I agree with all you have said, and you just beat me to a solution. The
only comment I would have is that, in general, there may not be that 'next
record' so you could use a Nz function with a number 'bigger than the normal
range for the table' so that if the record isn't found, it selects all
records from 600 upwards:

SELECT tblTable.*
FROM tblTable
WHERE id>600 and id<Nz((select min(id) from mytable where id>600 and
level=-1),999999)
ORDER BY id;
Feb 7 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Alex Martelli | last post by:
Interested parties (essentially, people who write user-coded iterator, or are intense users of iterators as "stream of data", of standard library module itertools, etc) are welcome to read, and...
10
by: Steven Bethard | last post by:
So, as I understand it, in Python 3000, zip will basically be replaced with izip, meaning that instead of returning a list, it will return an iterator. This is great for situations like: zip(*)...
3
by: Alexander Stippler | last post by:
Hi, I have to design some two dimensional iterators and I'm not quite sure about the design. I'd like to have the iterators mostly STL-like. The STL does not contain two dimensional iterators, I...
8
by: babak | last post by:
Hi everyone I have a problem with Iterators and containers in STL that hopefully someone can help me with. This is what I try to do: I have an associative (map) container and I have a...
24
by: Lasse Vågsæther Karlsen | last post by:
I need to merge several sources of values into one stream of values. All of the sources are sorted already and I need to retrieve the values from them all in sorted order. In other words: s1 = ...
4
by: kalita | last post by:
Hi All, typedef std::list<int> Cont; void f(Cont &c1, Cont &c2) { Cont::iterator it = c1.begin(); c1.swap(c2); it == c2.begin(); // is this ill formed? }
14
by: Jiri Kripac | last post by:
Languages such as Simula 67 contain a general concept of coroutines that allow the execution of a method to be suspended without rolling back the stack and then later resumed at the same place as...
90
by: John Salerno | last post by:
I'm a little confused. Why doesn't s evaluate to True in the first part, but it does in the second? Is the first statement something different? False print 'hi' hi Thanks.
8
by: pvonnied | last post by:
Hi, Once more a question (sorry for the different e-mail addresses , here @ work we have to use Google to post to newsgroups): If I use an iterator, say from std::map, I initialize it like so:...
7
by: desktop | last post by:
I am not sure I quite understand the use of iterators. I have this int array: int a = {1,2,3,4,5} I would now like to make an iterator to the first and last element: std::iterator<intiter;...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.