Display only sequential query data?

[Kylester]

Hello, I am somewhat new to Access but I am getting my feet wet very fast in a new job. My employer utilizes numerous databases including MS Access. I have full admin rights to Access so that is where I am focusing my attention right now. With that mouthful out of the way, is there a command to compare the results of a query for sequential string data? Sample: 56 2045800247 LenRod 01/06/2012 57 2046658440 ElmRod 04/06/2012 74 5078854744 GreenRod 08/04/2012 I have patched together a query that uses a primary key and sub-keys to gather output. This is in a database of > 10K records. Right now the results that I listed would be displayed as partial output of the executed query. What I would like to do is to eliminate the record that begins with the '74' because it is not in sequence with the other two. There only need to be two or more sequential numbers for the results to matter in what I am trying to do but anything that strips away single entries would help a great deal. Any help would be appreciated. Thank you, Kylester

[June7]

This requires evaluating data in other records of same table. Usually requires subqueries. Not easy, especially since you want to exclude record(s) that doesn't continue the sequence.

Explore this about subqueries: http://allenbrowne.com/subquery-01.html

[Kylester]

I was hoping to avoid that through the use of a function that possibly does not exist: "For value Current, if value Next is not equal to null, if Current value equals value Next minus one, Display Current." This is not canon but it is the idea that I was hoping to find in an existing function available for use within Access queries. Oh, and lest I forget, thank you for getting back to me!

[June7]

Try:

Query1
SELECT Field1, Field2, Field3, Field4, (SELECT TOP 1 Dupe.Field1 FROM Table1 AS Dupe WHERE Dupe.Field1<Table1.Field1 ORDER BY Dupe.Field1 DESC) AS LastVal, (SELECT TOP 1 Dupe.Field1 FROM Table1 AS Dupe WHERE Dupe.Field1>Table1.Field1 ORDER BY Dupe.Field1) AS NextVal
FROM Table1;

Query2
SELECT Field1, Field2, Field3, Field4
FROM Query1
WHERE (([Field1]+1=[NextVal])) OR (([Field1]-1=[LastVal]));