A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.
How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?
Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.
9  2763 
"MLH" <CR**@NorthStat e.netschreef in bericht news:8d******** *************** *********@4ax.c om...
>A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.
How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?
Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.
Here is some code to get 'some' missing numbers in a table called TabNumbers.
Dim db as Database, rst as Recordset
Dim i As Integer, lngNr As Long
Dim strmsg As String
Set db = CurrentDb
Set rst = db.OpenRecordse t("Select Number from TabNumbers Order by Number")
rst.MoveFirst
i = 0
lngNr = rst!Number - 1
Do Until rst.EOF
If rst!Number <lngNr + 1 Then 'missing number
i = i + 1
strmsg = strmsg & "Number < " & lngNr + 1 & " is missing & vbNewLine
End If
lngNr = rst!Number
rst.MoveNext
If i = 5 Then GoTo ShowString 'change this if you need more results
Loop
ShowString:
rst.Close
set rst=Nothing
MsgBox Trim(strmsg), vbInformation, "1st 5 missing numbers..."
You must be able to adapt this to your needs.
Arno R
Hi, Mike.
How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?
Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range
Use a quick query to count the number of missing records for you, and let
the query prompt you for the range. Try:
PARAMETERS LowRange Long, HighRange Long;
SELECT HighRange - LowRange + 1 - COUNT(*) AS NumRecsMissing
INTO tblCountOfMissi ngRecs
FROM MyTable;
. . . where LowRange is the lowest number in the range for the AutoNumber
column, HighRange is the highest number in the range for the AutoNumber
column, and MyTable is the name of the table you want to count the missing
records from. The number will be stored in the new table,
tblCountOfMissi ngRecs.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"MLH" <CR**@NorthStat e.netwrote in message
news:8d******** *************** *********@4ax.c om...
>A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.
How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?
Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.
Rather than answer, since you got two answers; I'd ask a question in
return -- why?
With autonumbers, you can't go back and fill them in to re-use the numbers;
and it has been discussed many times that the only (repeat _only_) valid use
for AutoNumbers is for use internal to your database (joins, and
relationships).
You _can_ not count on them being consecutive, and you shouldn't display
them to the users -- you'll give "traditiona l, old-time,
green-eyeshade-and-sleeve-garter accountants" a heart attack because they'll
EXPECT them to be sequential and consecutive even if you caution otherwise.
Larry Linson
Microsoft Access MVP
"MLH" <CR**@NorthStat e.netwrote in message
news:8d******** *************** *********@4ax.c om...
>A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.
How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?
Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.
Hi MLH, here's a complete function I created to find missing numbers and it
kicks the results out to notepad. You give it the starting number to search,
and it looks for missing integers until it gets to the end of the table.
------------------
Public Function MissingInvoices ()
Dim i As Long, rs As Recordset, db As Database, listinv As String, endnum As
Long, strsql As String, numerrors As Long
If Not IsNumeric(Forms !mainmenu!SubMe nu.Form!txtStar tNum) Then
MsgBox "Please enter a valid number for the starting invoice search
number.", vbCritical
Forms!mainmenu! SubMenu.Form!tx tStartNum.SetFo cus
End If
Set db = CurrentDb()
Call SetMessage("Ope ning recordset...")
strsql = "SELECT Invoices.Invoic eNo From Invoices ORDER BY
Invoices.Invoic eNo;"
Set rs = db.OpenRecordse t(strsql, DB_OPEN_SNAPSHO T)
numerrors = 0
DoCmd.Hourglass -1
Open GetLocalPath() & "MissingLog.txt " For Output As #1 'create DbLoc.txt
Write #1, "Missing Invoice Sequence Numbers LOG - " & Format(Date,
Forms!frmOpt.Fo rm!DateStyle)
Write #1,
If Not rs.BOF Then
rs.MoveFirst
i = Forms!mainmenu! SubMenu.Form!tx tStartNum
rs.MoveLast
endnum = rs!InvoiceNo
Do Until i = endnum
i = i + 1
Call SetMessage("Che cking for Invoice#" & i)
rs.FindFirst "InvoiceNo = " & i
'*** Sometime update this rountine to move sequentially through, and
not do the findfirst
'would be much much faster with large invoice db's
'***
If rs.NoMatch Then
numerrors = numerrors + 1
Write #1, i
End If
Loop
End If
rs.Close
db.Close
Call SetMessage("Off ")
Close #1
DoCmd.Hourglass 0
If numerrors 0 Then
Dim filena As String, Returnvalue
filena = GetLocalPath() & "MissingLog.txt "
'MsgBox filena
'Returnvalue = Shell("Notepad. EXE " & Chr(34) & filena & Chr(34), 1) '
Run Microsoft Excel.
'RetVal = Shell("WinHlp32 .EXE " & filena, 1)
'AppActivate Returnvalue ' Activate Microsoft
ShellExecute 0, "open", Chr(34) & filena & Chr(34), 0, 0, SW_SHOWNORMAL
End If
End Function
On May 8, 6:50 am, MLH <C...@NorthStat e.netwrote:
A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.
How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?
Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.
Here's an alternate method that uses subqueries: http://groups.google.com/group/micro...4a5fee9ff13437
Adapting it to obtain the missing ranges directly from your table
should be straightforward , but a separate table allows you to add a
record numbered 1001 to get the final missing range, if present. To
get the total number of deleted records, subtract the values from both
sides of ' to ', add 1 and sum that expression.
James A. Fortune CD********@Fort uneJames.com
All good points, Larry. I chose to use autonumber in
a mailing list for identifying mailers that were returned
non-deliverable. I don't use them for invoice numbers.
There is no particular relevance to them at all except
for the fact that they serve as quick 'n dirty identifiers.
And, most importantly, they're short.
When a postcard mailer comes back (and when you
send out 14000, many do) they are often covered with
a yellow USPS label with information on it - including a
bar-code for re-routing back to the originator. Often, it
covers up the target address - even the name of the
company. Placing a short 5-digit number on the card
(in several locations) makes it likely that my processor
(me - and on a good day, maybe the wife & kids) will
pick an identifier off really quickly, assisting in deletion
of the obsolete record. As for the choice between marking
for obsolecence or actually deleting the record, well it
makes no real difference to me when a record disappears
completely. If its no good - its no good. Until someone
suggests a beneficial use for housing data on defunct
businesses and bad addresses to me, I'll probably con-
tinue to delete the records. I mean, I wouldn't even add
them to my mailing list if I knew they were bad in the
first place. It cost's me four grand every time I send out
a mailing. Benjamin Franklin said 39-cents saved is 39
cents earned, although that probably happened after
his shocking experience with the kite and all.
On May 8, 7:50 am, MLH <C...@NorthStat e.netwrote:
A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.
How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?
Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.
Whats the objective in finding the 'missing' numbers? It looks like
you are misusing an autonumber field. Refer to Devs 10 commandments.
Think of it, David, as an exercise. What's the point of walking a mile
on a treadmill when you don't get anywhere might be a fair question.
I could easily wanna do the same thing in an incrementing long integer
field because my boss says "I wanna know which damned records
were deleted and who the hell deleted them"
>
Whats the objective in finding the 'missing' numbers? It looks like
you are misusing an autonumber field. Refer to Devs 10 commandments.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Remy Blank |
last post by:
Ok, here we go.
I added the possibility for tests using the unittest.py framework
to be skipped. Basically, I added two methods to TestCase:
TestCase.skip(msg): skips unconditionally
TestCase.skipIf(expr, msg): skips if expr is true
These can be called either in setUp() or in the test methods. I also
added reporting of skipped tests to TestResult, _TextTestResult and
|
by: nick |
last post by:
i use sscanf()to get the words in a line, it will skip all the space
automatically, if i want to know how many spaces were skipped and get
the words in a line, what can i do?
thanks!
|
by: Martin Joergensen |
last post by:
Hi,
I have some files which has the following content:
0 0 0 0 0 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 0 0 0 0 0
|
by: DrewKull |
last post by:
Ok ... so I'm looking at a query where im trying to show a bunch of
columns counted up based on criteria ...
So far i've been able to count all the rows up and show them ... and
also count up one column based on the criteria (value = yes) ... but
not both at the same time ...
When i try it with all of them, all of them are either the same number
or no numbers at all ...
|
by: heckstein |
last post by:
I am working in Access 2002 and trying to create a report from our company's learming management system. I am not a DBA and most of my SQL knowledge has been self taught through trial and error. I have created an access query to track the number of training hours for a training group. The query is working except for one piece of data and I hoping someone can help me. There is a field titled enrollment status, which presents an alpha character of...
| | |
by: bearophileHUGS |
last post by:
itertools.count docs say:
Does not currently support python long integers.
Note, count() does not check for overflow and will return negative
numbers after exceeding sys.maxint. This behavior may change in the
future.
But it seems it doesn't support negative numbers too:
|
by: wangerman |
last post by:
#include <stdio.h>
int main()
{
float a={.02, .05, .055, .01};
int i;
for (i=0; i<=300; i++)
if (a>0.4 && a<0.6) count++
printf("There are %d numbers between .4 and .6\n", some code???);
return 0;
|
by: Maxim |
last post by:
I use a PDO-MYSQL and executing a query like 'LOAD DATA INFILE ...'.
If I execute this query with mysql_query() I can use mysql_info() to
get info like
"String format: Records: 42 Deleted: 0 Skipped: 5 Warnings: 0."
How can I get this string using PDO-MYSQL get skipped count?
|
by: zufie |
last post by:
I have to create a QA report regarding callers calling into a phone
hotline.
The report consists of many checkboxes such as:
Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
