A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

identify missing numbers in sequence of records



 
 
Thread Tools Display Modes
  #1  
Old July 9th, 2009, 05:49 PM posted to microsoft.public.access.queries
Tom Whyte
external usenet poster
 
Posts: 1
Default identify missing numbers in sequence of records

Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks.
  #2  
Old July 9th, 2009, 06:22 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default identify missing numbers in sequence of records

Rank the data, then subtract the value from the rank. GROUP on that
difference, finding the MIN (start of un-interrupted sequence) and MAX (end
of un-interrupted sequence) for each of these groups:


SELECT MIN(valueField) As start, MAX(valueField) AS ending
FROM somequery
GROUP BY valueField-Rank



To get the rank, many possibilities. Here is one (which will be the
'somequery' you will use here up) :

SELECT a.valueField, COUNT(*) AS rank
FROM table AS a INNER JOIN table AS b
ON a.valueField =b.valueField
GROUP BY a.valueField



As example, with:

Table
valueField
1
2
3
5
6
7
8
9
11
12



The somequery (the last one) return:


valueField rank
1 1
2 2
3 3
5 4
6 5
7 6
8 7
9 8
11 9
12 10


And the first query will compute valueField-rank, and group on these
differences:

valueField rank
1 1 0
2 2 0
3 3 0
5 4 1
6 5 1
7 6 1
8 7 1
9 8 1
11 9 2
12 10 2



returning
start ending
1 3
5 9
11 12


since the un-interrupted sequences are from 1 to 3, 5 to 9, and 11 to 12



Vanderghast, Access MVP




"Tom Whyte" wrote in message
...
Please can someone tell me how to identify gaps in supposedly sequential
data
and calculate the value of teh difference. Thanks.


  #3  
Old July 9th, 2009, 06:23 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default identify missing numbers in sequence of records

I got this from here before and it works. It is slow if you have many
thousands of records.
Table tblListOfNumbers has field ID as autonumber and MyList as integer
field with the number sequences to be checked.

SELECT (SELECT A.MyList + 1 FROM tblListOfNumbers AS A WHERE A.ID = (SELECT
A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID = tblListOfNumbers.ID + 1 AND
A.MyList tblListOfNumbers.MyList + 1)) & ' to ' & (SELECT A.MyList - 1
FROM tblListOfNumbers AS A WHERE A.ID = (SELECT A.ID FROM tblListOfNumbers
As A WHERE A.ID = tblListOfNumbers.ID + 1)) AS MissingRange
FROM tblListOfNumbers
WHERE (SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList tblListOfNumbers.MyList + 1) IS NOT
NULL;


"Tom Whyte" wrote:

Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks.

  #4  
Old July 9th, 2009, 06:40 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default identify missing numbers in sequence of records

The list needs to be in ascending along with the ID as below --
ID MyList
1 1
2 2
3 3
4 6
5 7
6 8
7 9
8 11

It will result in '4 to 5' and '10 to 10'

"KARL DEWEY" wrote:

I got this from here before and it works. It is slow if you have many
thousands of records.
Table tblListOfNumbers has field ID as autonumber and MyList as integer
field with the number sequences to be checked.

SELECT (SELECT A.MyList + 1 FROM tblListOfNumbers AS A WHERE A.ID = (SELECT
A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID = tblListOfNumbers.ID + 1 AND
A.MyList tblListOfNumbers.MyList + 1)) & ' to ' & (SELECT A.MyList - 1
FROM tblListOfNumbers AS A WHERE A.ID = (SELECT A.ID FROM tblListOfNumbers
As A WHERE A.ID = tblListOfNumbers.ID + 1)) AS MissingRange
FROM tblListOfNumbers
WHERE (SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList tblListOfNumbers.MyList + 1) IS NOT
NULL;


"Tom Whyte" wrote:

Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks.

  #5  
Old July 9th, 2009, 09:18 PM posted to microsoft.public.access.queries
MGFoster
external usenet poster
 
Posts: 653
Default identify missing numbers in sequence of records

Tom Whyte wrote:
Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I found this solution on an SQL Server forum (translated to JET SQL):

SELECT last_nbr
, next_nbr
, last_nbr + 1 As first_avail
, next_nbr - 1 As last_avail
, next_nbr - (last_nbr + 1) As total_nbrs
FROM (SELECT
(SELECT NZ(Max(Seq2.SeqNumber),0) As SeqNumber
FROM SequenceTable Seq2
WHERE Seq2.SeqNumber Seq1.SeqNumber) As last_nbr
, SeqNumber As next_nbr
FROM SequenceTable Seq1) as A
WHERE next_nbr - last_nbr 1
ORDER BY last_nbr

Output example:

last_nbr next_nbr first_avail last_avail total_nbrs
0 10 1 9 9
103 105 104 104 1
107 114 108 113 6
116 129 117 128 12


--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSlZQnoechKqOuFEgEQIy0QCgjkq8fUZbxVef3n1LgulrjW YbPQYAoK5L
Ol9OsAFUps4yyDB+PQL/J+iK
=jhwq
-----END PGP SIGNATURE-----
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.