cyclical numbering on three fields

[Guest]

Hello,

We need to put into an Access database all the records of assents that we
have been recording by hand writing in books, one book per year. The way to
identify each record is by the combination of three fields: Year of the book,
page number, and line number. Now, we are not going to use that as a key
anymore, however we need to type in this information since it is already like
that in our books.

Year of book: 2006 (this is going to be a default number and we are going
to change it, obviously, once a year)

Page number: _ _ _ (We need it to numbering each new record from 1 to 300,
and then when the year number change we need the page number to star over
from 1)

Line number: _ _ (We need it to numbering each new record record from 1 to
45, and when the page number change we need that the line number star over
from 1)

How could I do it ? Is there any way to do it or do we need to type in
manually and be careful of not skipping numbers?

Cheers,
Iovana

 

[Tom Ellison]

Dear Iovana:

You must make this a key. You must prevent duplicates.

How this must be implemented depends on the computer setup for which it is
to function. Do you have only a single computer that will be used to enter
everything? Will there ever EVER be more than one computer networked to do
this?

In either case, you need to have a table where the next LineNumber and
PageNumber are counted up as you describe. Actually, it could be done
simply with just a next XNumber. Let this number count from 0 up to 13,499
(300 * 45 - 1) or whatever number of lines it takes. This can quickly be
resolved into a page/line combination using modulo and integer division.
For example, LineNumber 642 would be Page 3, Line 43. Why 43? Because
LineNumber 0, the first line anywhere, would be Page 1 / Line 1. The
formulas are:

PageNumber = XNumber \ 45 + 1
LineNumber = XNumber Modulo 45 + 1

Here are some conversions for this:

XNumber PageNumber LineNumber
0 1 1
1 1 2
44 1 45
45 2 1
89 2 45
90 3 1

Using the XNumber, all you need is one value that describes all pages and
assigns them thoughout the year, a value that corresponds quickly to a page
and line number.

You can use some locking in a procedure that obtains the next XNumber for
you. This is commonly available code.

An interesting side-note. I once ran software to assign next numbers using
3 computers on a network. Each computer requested a number and inserted it
into an undexed table. I did this twice, once with locking and once
without. The computers ran as fast a possible, each one getting the current
value from the common control record and incrementing it. Each computer ran
around a VBA loop inserting 1,000,000 rows.

The test produced identical results with and without locking! This test was
done using Jet. There were no duplicates in the table when done without
locking. Some of you experts may want to test this. VELLY INTELLESTING! I
go ahead with the locking anyway in my applications, but it does appear to
be unnecessary. Not what I expected at all!

Tom Ellison

 

[Guest]

Dear Tom,

Thank you very much for your help.

Please forgive my simplicity but how could I create an XNumber field
counting from 0 up to xxx ?

And second, when I try to insert an equation, lets say Linenumber = Xnumber
mod 45 + 1, then I get an error message saying that the database engine
doesn’t recognize either the field “Xnumber†in a validation expression or
the default value in the table “Testâ€.

What I’m trying to do is to insert the equation into the default value
option within the field properties. Am I completely lost?

I will appreciate your help very much.
Cheers,
Iovana

 

[Tom Ellison]

Dear Iovana:

I would expect you would have an XNumber (or call it what you wish) column
in the table where you record assets. The formulas I suggested would
decompose the XNumber value into the page/line values you desire.

I'm not sure which way of generating sequential values for XNumber may be
best. I would assume that you would NEVER delete one, let alone re-use one.
Perhaps you would retain one that has been invalidated, ignoring that for
many purposes, but retaining it as a record as originally created. Would
this be correct.

In the form that allows you to add new lines, I would wait till the
BeforeUpdate event to do this. I would then run a query to find the largest
existing number for the proper year that is already in the table and add one
to that. To start off a new year, there would be no such largest existing
number, so I would use Nz() to substitute -1 for this, so the first number
would be 0.

Does that help?

Now, where are you trying "to insert an equation". I take it this is in a
query. Does the query include the table that has Xnumber in it? Is this
the table "Test"?

What equation are you trying to insert in the Default Value property? I
can't think of any Default Value you could use unless you write a custom
function to query and assign it. That wouldn't be a half bad approch,
however.

Are you lost? I don't know! Shall we send out a search party? : )

Maybe I'm the one who's lost! I've been feeling around, trying to find
which suggestions I might make will be well understood, not knowing your
experience. I have a feeling I'm stretching your capacities, but that's not
such a bad thing, to a limit. Right?

Tom Ellison