Listing gaps in numbering sequences

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a query or routine that can be ran against a database table that
would list the gaps in a numbering sequence? For example, a part master
table containing a field "PartNumber" with part number values ranging between
10000 and 20000. The user decides what the next part number he/she will use
however, upon further research you find that between gaps in the selection
process and deletions there is a lot of waste.

How can I generate this "p/n not used" list?
 
Create a table of numbers from 10000 to 20000. Then create an unmatched
query to look for numers in the number table that are not in the part number
table.
 
PC Datasheet said:
Create a table of numbers from 10000 to 20000. Then create an unmatched
query to look for numers in the number table that are not in the part number
table.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...

**********************************************
To Steve, who started his advertising and job-hunting again ...
**********************************************
-- You abuse this group and others for job-hunting and advertising over and over again
-- You are insulting lots of people here when they ask you to stop this
-- You hide your identity while asking questions
-- You try to sell a CD ($125,--) with FREE code you gathered from these groups here
-- You posted as Steve, Ron, Tom, Rachel, Kathy, Kristine, Heather and ???
(the latest 'star' is: 'Access Resource')
-- There even has been a 'Scam-alert' about you

So why would ANYBODY ever trust you and hire you?
********************************************************

To all:
Why this answer ? ==>> We are trying to stop the advertising here.
(Until Steve stops the advertising *every* post of Steve get's this reply)

Explanation and more on the above: http://home.tiscali.nl/arracom/stopsteve.html

Arno R
 
Come on. Give Steve a break on this. He provided free advice and didn't
directly make any comments like "Contact me off line for help".

His signature may be border-line but it doesn't bother me much.

--
Duane Hookom
MS Access MVP



PC Datasheet said:
Create a table of numbers from 10000 to 20000. Then create an unmatched
query to look for numers in the number table that are not in the part
number
table.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...

**********************************************
To Steve, who started his advertising and job-hunting again ...
**********************************************
-- You abuse this group and others for job-hunting and advertising over and
over again
-- You are insulting lots of people here when they ask you to stop this
-- You hide your identity while asking questions
-- You try to sell a CD ($125,--) with FREE code you gathered from these
groups here
-- You posted as Steve, Ron, Tom, Rachel, Kathy, Kristine, Heather and ???
(the latest 'star' is: 'Access Resource')
-- There even has been a 'Scam-alert' about you

So why would ANYBODY ever trust you and hire you?
********************************************************

To all:
Why this answer ? ==>> We are trying to stop the advertising here.
(Until Steve stops the advertising *every* post of Steve get's this reply)

Explanation and more on the above:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R
 
RDE said:
Is there a query or routine that can be ran against a database table that
would list the gaps in a numbering sequence? For example, a part master
table containing a field "PartNumber" with part number values ranging between
10000 and 20000. The user decides what the next part number he/she will use
however, upon further research you find that between gaps in the selection
process and deletions there is a lot of waste.

How can I generate this "p/n not used" list?
Click to expand...


You should be able to adapt this example:

SELECT IdGaps.SeqNum+1 AS Avail
FROM IdGaps LEFT JOIN IdGaps AS X
ON IdGaps.SeqNum+1 = X.SeqNum
WHERE X.SeqNum Is Null
ORDER BY 1
 
Duane,

Thank you for the response!

Do your homework and you will be startled who Arno R is and what he has been
doing for a long time!

Steve
 
Duane Hookom said:
Come on. Give Steve a break on this. He provided free advice and didn't
directly make any comments like "Contact me off line for help".

His signature may be border-line but it doesn't bother me much.
Click to expand...

I can understand what you are writing Duane, and I fully agree with you.
But this is *NOT* about the sig-line. (I beginning to think Steve is borderline, not his sig...)
When you click the link I provide, or re-read the thread "Stop Steve, how ??? (if you feel like that....) you will understand.
In that thread I announced a plan to stop Steve, which is in fact this:
==> Hunt *every* post of PC, no matter if he is advertising.
==> UNTIL he stops the adds.
*All* other attempts to stop him have failed, remember?
So this is really NOT about the sig. I have stated that before. It's an effort to stop the advertising.

On June 1 I wrote this to Steve: (If you click the link I provide there is also a link to this thread)
<start quote Arno R>
Now since you obviously 'woke up' again I would like to warn you:
If I see more posts like Tom's (and Access resource ?) where you are jobhunting again ...
I *will* start answering ALL your posts like I said earlier.
I *am* considering this allready.

It was very nice for 1 month withour your advertising/jobhunting.
Let's keep it nice...
<end quote Arno R>

Besides the obvious: <start quote> GO TO HELL !!!! <end quote>
He followed with:
<start quote PCDatasheet>
And by the way Greaseball,

I got seven projects from the newsgroup in the last four weeks. Three were
the result of my responses on the newsgroup AND four (YES 4) were the result
of you advertising for me. KEEP UP THE GOOD WORK!!!!

Steve
<end quote PCDatasheet>

Arno R
 
I have criticized and corrected Steve in previous posts. If he steps over
the (my) line or provides mis-information, I will do it again.

I won't make the effort to criticize him in a thread where I don't think he
deserves it. That would be too much like a stalker.

--
Duane Hookom
MS Access MVP



Duane Hookom said:
Come on. Give Steve a break on this. He provided free advice and didn't
directly make any comments like "Contact me off line for help".

His signature may be border-line but it doesn't bother me much.
Click to expand...

I can understand what you are writing Duane, and I fully agree with you.
But this is *NOT* about the sig-line. (I beginning to think Steve is
borderline, not his sig...)
When you click the link I provide, or re-read the thread "Stop Steve, how
??? (if you feel like that....) you will understand.
In that thread I announced a plan to stop Steve, which is in fact this:
==> Hunt *every* post of PC, no matter if he is advertising.
==> UNTIL he stops the adds.
*All* other attempts to stop him have failed, remember?
So this is really NOT about the sig. I have stated that before. It's an
effort to stop the advertising.

On June 1 I wrote this to Steve: (If you click the link I provide there is
also a link to this thread)
<start quote Arno R>
Now since you obviously 'woke up' again I would like to warn you:
If I see more posts like Tom's (and Access resource ?) where you are
jobhunting again ...
I *will* start answering ALL your posts like I said earlier.
I *am* considering this allready.

It was very nice for 1 month withour your advertising/jobhunting.
Let's keep it nice...
<end quote Arno R>

Besides the obvious: <start quote> GO TO HELL !!!! <end quote>
He followed with:
<start quote PCDatasheet>
And by the way Greaseball,

I got seven projects from the newsgroup in the last four weeks. Three were
the result of my responses on the newsgroup AND four (YES 4) were the result
of you advertising for me. KEEP UP THE GOOD WORK!!!!

Steve
<end quote PCDatasheet>

Arno R
 
Hi,


Here is another alternative,


SELECT a.PartNumber, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.PartNumber >=b.PartNumber


Saved it as q1.


SELECT MIN(PartNumber), MAX(PartNumber)
FROM q1
GROUP BY PartNumber-rank


will return the ranges without holes, like



12 76
98 145
....


meaning 12 to 76 (inclusive) are used, then 98 to 145 also used... leaving,
in this case, 77 to 97 as un-used. Clearly, minus-infinity to 11 are not
used either.

It is based on the observation that if "i" is present, and is the j-th
smaller value, then "ï+1" will be the in position "j+1" when ordered (if
there is no interruption in the sequence). If we subtract the rank from the
value, we get i - j in both cases. Keeping MIN(i) and MAX(i) for
all the values having the same "value - rank" will thus list the limits of
unbroken sequences.


Hoping it may help,
Vanderghast, Access MVP
 
Duane Hookom said:
I have criticized and corrected Steve in previous posts. If he steps over
the (my) line or provides mis-information, I will do it again.
Click to expand...
Unfortunately this won't help much ....
This has been done several times, for several years, by several posters (also by me)
Steve is unwilling to listen to reason, he will *never* give up unless he is forced to...
I won't make the effort to criticize him in a thread where I don't think he
deserves it. That would be too much like a stalker.
Click to expand...
I *am* stalking him in a way, yes indeed.
I don't like it myself. I am not enjoying this. It's *not much* fun ... (aaaarrrrggggghhh)

It would be a much better situation here when Steve would stop the advertising himself.
There is NO justification whatever for his job-hunting in the newsgroups

FYI: I am considering stopping the <hunting every post> and only 'go after him' when he advertises.
I expect him to stop the advertising soon (at least for a while, as he did in April)

Arno R
 
Hi,
Sorry don't mean to butt in on some one else's question, but I the SQL
statement on my db and got this error for q1:

"You tried to execute a query that does not include the specified expression
'<field name>' as part of an aggregate function."

q1 SQL:
SELECT a.CutLocationID, COUNT(*) AS rank
FROM tblCutsbyLocation AS a INNER JOIN tblCutsbyLocation AS b ON
a.CutLocationID >= b.CutLocationID

what did I do wrong?
 
Hi,


You are right, the first query is missing


GROUP BY a.CutLocationID


It should be:


SELECT a.CutLocationID, COUNT(*) AS rank
FROM tblCutsbyLocation AS a INNER JOIN tblCutsbyLocation AS b
ON a.CutLocationID >= b.CutLocationID
GROUP BY a.CutLocationID



When there is an aggregate in the SELECT clause, like here, COUNT(*), then
all the expressions in the SELECT should be either aggregated, either part
of the GROUP BY list (either an arithmetic combination of these).



Hoping it may help,
Vanderghast, Access MVP
 
Thank you, that did the trick. I am having trouble with the "Running query"
only getting to 3 bars and just sitting there. I don't think it is actually
locking up I think the table has to many records. I realized this after
trying it on a couple different tables. I wasn't able to get it to work on a
table with500+ records but I was able to get it to work on a table with 24.
The table I need it to work on is 9000+ records. Any suggestions?

:

Hi,
You are right, the first query is missing
GROUP BY a.CutLocationID
It should be:
Click to expand...
SELECT a.CutLocationID, COUNT(*) AS rank
FROM tblCutsbyLocation AS a INNER JOIN tblCutsbyLocation AS b
ON a.CutLocationID >= b.CutLocationID
GROUP BY a.CutLocationID
 
Hi,


You have indexed CutLocationID ?


You can also try:


SELECT a.CutLocationID, (SELECT COUNT(*)
FROM tblCutsbyLocation AS b
WHERE a.CutLocationID >= b.CutLocationID )

FROM tblCutsbyLocation AS a




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top