Re: MS Access iterators?


"MGFoster" <me@xxxxxxxxxxx> wrote in message
news:Zq7Gf.11089$1n4.4262@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-----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+kDNoechKqOuFEgEQKuygCdFd7x1lXTZEUNDDB9Gp31SGVUrBkAn1Hp
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;


.

Relevant Pages

  • Re: Need Help with Formula Expansion
    ... Hash: SHA1 ... It could slow down the query a lot 'cuz each occurrence scans the entire ... Now I would like to expand the formula to allow a count for the quarter, which means I would like to be able to get a count of files for 3 selected review dates instead of just 1. ...
    (microsoft.public.access.queries)
  • Re: Need Help with Formula Expansion
    ... Hash: SHA1 ... You could just ask for the start date and how many months the query ... I ask becuase there are times when the users would only need the results for only 2 Review Dates, ...
    (microsoft.public.access.queries)
  • Re: Help with query
    ... Hash: SHA1 ... You could show the query you're trying to use and a sample of the data ... Clients may have made contact over several years, then gone away, and come back or they may be completely new. ...
    (microsoft.public.access.queries)
  • Re: Converting Access Data to an Ascii text file
    ... Hash: SHA1 ... Use the Format() function on the query's column values. ... right click on the query & select Export. ...
    (microsoft.public.access.queries)
  • Re: Converting Access Data to an Ascii text file
    ... Hash: SHA1 ... Use the Format() function on the query's column values. ... right click on the query & select Export. ...
    (microsoft.public.access.queries)
Loading