By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Whats missing from range of licenses?

P: n/a
I have a park permit tracking database where I was thinking of having
the user enter all the park permits individually, however realized
that in a day, a park could host many daily users. So if 100 daily
permits were issued, the data entry would consist of 100 entries with
only the permit number changing. If I changed the database to collect
beginning and ending permit number would I be able to find the missing
permit numbers? Say an example of entries where permit 1-40, 41-50,
52-55, 57-63 would I be able to find out that permit 51 and 56 were
missing short of creating a table with every possible permit number???
That just seems tedious.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Instead, I would probably add a command button like "Issue a permit".
Once pressed, a new record is created with automatically incremented
permit number. If it needs to be changed, you could show it on your form
for editing. Also, if someone with an annual pass comes in, just scan
the pass' barcode and it would log itself in the database. Would this work?

Pavel

Annette Massie wrote:

I have a park permit tracking database where I was thinking of having
the user enter all the park permits individually, however realized
that in a day, a park could host many daily users. So if 100 daily
permits were issued, the data entry would consist of 100 entries with
only the permit number changing. If I changed the database to collect
beginning and ending permit number would I be able to find the missing
permit numbers? Say an example of entries where permit 1-40, 41-50,
52-55, 57-63 would I be able to find out that permit 51 and 56 were
missing short of creating a table with every possible permit number???
That just seems tedious.

Nov 12 '05 #2

P: n/a
The county park system has many individual parks and many park workers
who have their own books of permits. To have the program increment
won't really work because their could be any range of permits sold in
any given day at any location by any worker.
Nov 12 '05 #3

P: n/a
Well it's case of brute force and ignorance but if you're not dealing with
large numbers of permits then I'd be inclined to set up a FOR...NEXT loop
that starts at the lower permit number, works its way up to the highest
permit number, and checks each one against the table to see if it exists
(using a dynamic SQL statement). Something like:

For intCount = LowestPermit to HighestPermit
strSQL = "SELECT Count(*) As CountPermit FROM tbPermit WHERE PermitNo =
" & intCount
rsPermit.Open
strSQL,CurrentProject.Connection,adOpenForwardOnly ,adLockReadOnly,adCmdText
if rsPermit!CountPermit = 0 Then StoreThisNumberSomewhere() 'obviously
you'd have to code this function
rsPermit.Close
Next intCount

I'm willing to bet that some maths genius has come up with a more elegant
solution (I think you'd be hard pressed to find a less elegant solution!)
but as long as your permits stay below the hundred thousand level I can't
see too many problems occuring.

"Annette Massie" <an******@co.saint-croix.wi.us> wrote in message
news:c6**************************@posting.google.c om...
I have a park permit tracking database where I was thinking of having
the user enter all the park permits individually, however realized
that in a day, a park could host many daily users. So if 100 daily
permits were issued, the data entry would consist of 100 entries with
only the permit number changing. If I changed the database to collect
beginning and ending permit number would I be able to find the missing
permit numbers? Say an example of entries where permit 1-40, 41-50,
52-55, 57-63 would I be able to find out that permit 51 and 56 were
missing short of creating a table with every possible permit number???
That just seems tedious.

Nov 12 '05 #4

P: n/a
I guess in this case in order it is important for me to know how is the
data entry handled in the first place. Is it a back end with multiple
FEs connectsd, or do they send permit numbers in to a central location?
Recording ranges does not appeal to me. It does not optimize anything
much as opposed to recodring the numbers, and recording the ranges
definitely makes it more involved when it comes to data analyses.

Pavel

Annette Massie wrote:

The county park system has many individual parks and many park workers
who have their own books of permits. To have the program increment
won't really work because their could be any range of permits sold in
any given day at any location by any worker.

Nov 12 '05 #5

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Somebody else had a similar "brute-force" solution - here's another.
It's final result is a string of comma-delimited numbers representing
the missing permit numbers. You may wish to do something else w/
these numbers - you'll have to change the code to suit your purposes.

The Replace() function is not available in Acc97 and earlier versions.
There is a similar function in the Neat Code example database
available on the Microsoft download website (watch out for line-wrap):

http://support.microsoft.com/default.aspx?scid=kb;en
us;177972&Product=acc97

You'll have to change the SQL_PERMITS string to match your table/field
names.

You should put in error traps.

=== air code ===

Function MissingPermits() As String
' return a comma-delimited string of
' the missing permit numbers, on, or after,
' the indicated date

const SQL_PERMITS = "SELECT PermitNo " & _
"FROM Permits " & _
"WHERE PermitDate >= |1 " & _
"ORDER BY PermitNo"

dim db as dao.database
dim rs as dao.recordset
dim strSQL as string
dim strMissing as string
dim lngPrevious as long

' Put in the indicated date
' Using Acc2002
' If Replace function needed in Acc97 see
' ReplaceStr() in ntcode97.exe on MS download site.
strsql = Replace(SQL_COUNT,"|1","#1/1/2003#")

set db = currentdb
set rs = db.openrecordset(SQL_PERMITS)

' initialize - start w/ the 1st available no.
' assumes this no. is a "good" starting
' no. for the indicated date.
if not rs.eof then
lngPrevious = rs!PermitNo
rs.movenext
end if

do while not rs.eof
' iterate thru other records
' finding all missing permit nos.

if rs!PermitNo - 1 <> lngPrevious then
strMissing = strMissing & _
FillNumbers(lngPrevious, rs!PermitNo) & ","
Endif

lngPrevious = rs!PermitNo

rs.MoveNext

loop

' Get rid of trailing comma
if len(strMissing)>0 then
strMissing = Left$(strMissing,len(strMissing-1))
end if

MissingPermits = strMissing

End Sub

Function FillNumbers(lngStart as long, lngEnd As long) as string
' return a comma-delimited string of sequential numbers
' between lngStart and lngEnd range, excluding lngStart & lngEnd

dim i as long
dim strResults as string

for i = lngStart + 1 to lngEnd - 1
strResults = strResults & i & ","
next

' drop trailing comma
if len(strResults)>0 then
strResults = left$(strResults, len(strResults)-1)
end if

FillNumbers = strResults

End Function

== end air code ==
MGFoster:::mgf
Oakland, CA (USA)

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

iQA/AwUBP57drYechKqOuFEgEQJneQCgnXIvgmei8k5sc5tOFaw1P2 TM47wAoPAx
op7c+boR7M47MCVFPV2SrjX9
=pXRS
-----END PGP SIGNATURE-----

Annette Massie wrote:
The county park system has many individual parks and many park workers
who have their own books of permits. To have the program increment
won't really work because their could be any range of permits sold in
any given day at any location by any worker.


Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.