How does a primary key become a negative number?

[Ron]

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

TIA
ron

[Bas Cost Budde]

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

With Jet sure it is possible. There is even a technical reason for this:
the high order bit of the binary representation is interpreted as the
number being negative.

Autonumbers are known to make frantic jumps now and then. You cannot
rely on them climbing steadily.

Using your own incremential number is not too difficult. You must
confine all actions on such a table to routes via your code (either via
function calls or through forms that do the calculation of the new number)

Suppose you have a table Invoice, PK=invID (long). Have this in the
BeforeInsert handler of a form based on this table:

me!invID=nz(dmax("invID","Invoice"),0)+1

This will yield 1 if there is no record yet, and the next free number if
there are records.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Tim Marshall]

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number

Don't. Just don't. Never use the PK as a tracking number the user
sees. Always keep the PK/FK as numbers in the background that nobody
sees. You will always eventually run into a situation where someone
wants to apply a schema for account numbers, enter a new invoice number
system based on the direction his small toe points, whatever. If you're
using as a joining column/field data tht is being read, it becomes a
difficult process to apply such changes to previous records. If you use
a properly normalized approach, you change an account number....once.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[tombsy]

i agree, I never use the autonumbers, it always ends in tears.
"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cn**********@coranto.ucs.mun.ca...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number

Don't. Just don't. Never use the PK as a tracking number the user sees.
Always keep the PK/FK as numbers in the background that nobody sees. You
will always eventually run into a situation where someone wants to apply a
schema for account numbers, enter a new invoice number system based on the
direction his small toe points, whatever. If you're using as a joining
column/field data tht is being read, it becomes a difficult process to
apply such changes to previous records. If you use a properly normalized
approach, you change an account number....once.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Paul]

BE CAREFUL IF YOU CHOOSE TO USE me!invID=nz(dmax("invID","Invoice"),0)+1.
If 2 users try to make an invoice at the same time, they will both get the
same ref number because their record has not yet been saved. Its better to
assign the number when you leave the record. This way it will look up the
number, assign it, save it and exit all at once. You could use a
docmd.runcommand accmdsaverecord if you want, but I have seen some issues
with hard drive traffic that stops this command from occuring.

HTH
Paul

"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:cn**********@news2.solcon.nl...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is
not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

With Jet sure it is possible. There is even a technical reason for this:
the high order bit of the binary representation is interpreted as the
number being negative.

Autonumbers are known to make frantic jumps now and then. You cannot rely
on them climbing steadily.

Using your own incremential number is not too difficult. You must confine
all actions on such a table to routes via your code (either via function
calls or through forms that do the calculation of the new number)

Suppose you have a table Invoice, PK=invID (long). Have this in the
BeforeInsert handler of a form based on this table:

me!invID=nz(dmax("invID","Invoice"),0)+1

This will yield 1 if there is no record yet, and the next free number if
there are records.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Ron]

Well, now I'm even *more* confused.

The first responder tells me the incremented autonumber field shouldn't be
used as a PK so gave me a nice routine to come up with my own PK. No prob.
However, you seem to be saying I shouldn't use the PK (I'd assume even the
one I come up with using this nice routine) as a user-visible account
number, invoice number, etc. So, then, I now need 2 numbers I come up with,
one to show the user and one to be the actual PK?

I feel like one of those lil' ducks at the arcade shooting gallery that
reverses directions with each direct hit. Boing, I'm going left, boing I'm
going right. Help! Somebody stop me!

ron

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cn**********@coranto.ucs.mun.ca...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number

Don't. Just don't. Never use the PK as a tracking number the user
sees. Always keep the PK/FK as numbers in the background that nobody
sees. You will always eventually run into a situation where someone
wants to apply a schema for account numbers, enter a new invoice number
system based on the direction his small toe points, whatever. If you're
using as a joining column/field data tht is being read, it becomes a
difficult process to apply such changes to previous records. If you use
a properly normalized approach, you change an account number....once.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Ron]

Thanks Paul, that helped A LOT! Hadn't even gotten to the
"multi-user"thoughts yet, but yeah, there could be 2 to 3 users (max) trying
to save to the same file at the same time.

Thinking about it, though, I'll have a detail subform on my invoice form.
As the details are stored into their table, I need that linking InvoiceID,
so I won't be able to wait till they leave the invoice form to assign the
InvoiceID.

Anything wrong with putting a file on the BE that a routine can access
sooner to pull a number off for the next number up (for whatever file is
being added to) and then store the number+1 figure back into that field for
the next user? System can lock that file, do the math, store the new number
and unlock the file all pretty quickly. Wouldn't that solve the number+1
problem and still allow for multiple users? (Just thinking out in the open,
here)

Sorry for all the probably really basic questions.

TIA
ron

"Paul" <pz****@rogers.com> wrote in message
news:Z4********************@rogers.com...

BE CAREFUL IF YOU CHOOSE TO USE me!invID=nz(dmax("invID","Invoice"),0)+1.
If 2 users try to make an invoice at the same time, they will both get the
same ref number because their record has not yet been saved. Its better to assign the number when you leave the record. This way it will look up the
number, assign it, save it and exit all at once. You could use a
docmd.runcommand accmdsaverecord if you want, but I have seen some issues
with hard drive traffic that stops this command from occuring.

HTH
Paul

"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:cn**********@news2.solcon.nl...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective files. I understand that sometimes a number will be missing, which is
not a
problem for my purposes I don't think, but how would it be negative? Can randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

With Jet sure it is possible. There is even a technical reason for this:
the high order bit of the binary representation is interpreted as the
number being negative.

Autonumbers are known to make frantic jumps now and then. You cannot rely on them climbing steadily.

Using your own incremential number is not too difficult. You must confine all actions on such a table to routes via your code (either via function
calls or through forms that do the calculation of the new number)

Suppose you have a table Invoice, PK=invID (long). Have this in the
BeforeInsert handler of a form based on this table:

me!invID=nz(dmax("invID","Invoice"),0)+1

This will yield 1 if there is no record yet, and the next free number if
there are records.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Trevor Best]

Ron wrote:

Well, now I'm even *more* confused.

The first responder tells me the incremented autonumber field shouldn't be
used as a PK so gave me a nice routine to come up with my own PK. No prob.
However, you seem to be saying I shouldn't use the PK (I'd assume even the
one I come up with using this nice routine) as a user-visible account
number, invoice number, etc. So, then, I now need 2 numbers I come up with,
one to show the user and one to be the actual PK?

I feel like one of those lil' ducks at the arcade shooting gallery that
reverses directions with each direct hit. Boing, I'm going left, boing I'm
going right. Help! Somebody stop me!

Roll your own number for the invoice number, have a unique index on it.
Use AutoNumber for the PK and relate all other table to that.

You can of course use your own rolled invoice number as the PK as this
is the candidate key for the record but there are drawbacks to doing
this, mostly to do with the number changing for some reason (usually
before the invoice is physically issued, then it should be frozen, set
in stone) lest you need to handle the dependant records or use cascades,
which IMHO are pretty hairy and invite major damage from a minor cock up.
--
This sig left intentionally blank

[Forrest]

> I'm deciding whether to use the PK also as an account number, invoice

number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

If you ask for random numbers you can definately get negatives - this
should be half the time. If you ask for incremented numbers, you
would have to specifically set them up to be able to make negatives (
seed = -x ).

[Bas Cost Budde]

Paul wrote:

BE CAREFUL IF YOU CHOOSE TO USE me!invID=nz(dmax("invID","Invoice"),0)+1.
If 2 users try to make an invoice at the same time, they will both get the
same ref number because their record has not yet been saved. Its better to
assign the number when you leave the record. This way it will look up the
number, assign it, save it and exit all at once. You could use a
docmd.runcommand accmdsaverecord if you want, but I have seen some issues
with hard drive traffic that stops this command from occuring.

I agree. Should have mentioned that this is for single user.

My multi user approach is to maintain a keys table, where the next
available number is stored. (There can be keys for any table prone to
this kind of inserts; my fields are tableName(text) nextFree(number))
The BeforeInsert handler of a form locks this table (so the next attempt
will fail, code should pause and retry), gets the number, increases it
and writes it back. That in itself is a short action, will not halt
other users too much (up to 20 simultaneous insert-requests) and has the
number available at the beginning of the process, which is sometimes
highly wanted.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Edward]

"Ron" <ro*******************@earthlink.com> wrote in message news:<I5******************@newsread1.news.pas.eart hlink.net>...

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

You have no control over the Autonumber field. You can reset it by
clearing out the table and compressing the database. But negative?
No. However, it is not that hard to roll your own PK, which is what I
would do in your case.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Tim Marshall]

Ron wrote:

Well, now I'm even *more* confused.

Hi Ron,

What I'm telling you applies to use of a primary key. In the
application you have described, the basic structure should include a
table for invoice header information and a second table for invoice line
items. It sounds as if you are planning to join the two tables in
queries by use of the invoice table's primary key - good - which you
are planing to make the invoice number - bad.

So I assumed you are using the primary key as a menas to link tables.

It truly is a good rule of thumb to have the means by which you link
tables together to be absolutey meaningless. That way you and your
users can happily tinker away with whatever scheme you have for
information. There are some instances when a developer might be tempted
to use actul real information as a PK and the one that comes to my mind
that is often used is an account number or component(s) of an account
number for multi-part account number set ups. How often is an account
number ever going to change?

Well, even bean counters screw up and decide an account number has to
change from time to time. I run a maintenance system which is fed
account numbers from a larger financial system and the maintenance
system populates account fields in various tables with an actual account
number to avoid the pain of having to associate the extra account
table(s) via a "meaningless" system number in putting together SQL
statements. It always seemd to me to be a reasonable step until 2 years
ago when our university bean counters decided a coule of account codes
had to change. It was a hideous exercise trying to worm through all the
tables in our maintenance app (it does a lot of stuff and there are a
couple of hundred Oracle tables rife with account number information)
and change all these.

Having a link via a meaningless system generated number for the account
PK and linking the account table would have meant the account number
would needed to have been changed once, and once only.

So yes, I would strongly recommend you come up with two numbers to
generate - an autonumber for a PK and an autonumber or some other means
of generating a sequence for the invoice number.
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[paii, Ron]

I have a table on the BE containing the next available sequence number for
PO, Invoice ect. Use a function of get the next value, increment and write
out the next number to the BE table. Always have the function verify the
number does not exit before returning. Otherwise users could still get
duplicate values. Also check that the number sequence does not restart.

"Ron" <ro*******************@earthlink.com> wrote in message
news:Zx*****************@newsread3.news.pas.earthl ink.net...

Thanks Paul, that helped A LOT! Hadn't even gotten to the
"multi-user"thoughts yet, but yeah, there could be 2 to 3 users (max) trying to save to the same file at the same time.

Thinking about it, though, I'll have a detail subform on my invoice form.
As the details are stored into their table, I need that linking InvoiceID,
so I won't be able to wait till they leave the invoice form to assign the
InvoiceID.

Anything wrong with putting a file on the BE that a routine can access
sooner to pull a number off for the next number up (for whatever file is
being added to) and then store the number+1 figure back into that field for the next user? System can lock that file, do the math, store the new number and unlock the file all pretty quickly. Wouldn't that solve the number+1
problem and still allow for multiple users? (Just thinking out in the open, here)

Sorry for all the probably really basic questions.

TIA
ron

"Paul" <pz****@rogers.com> wrote in message
news:Z4********************@rogers.com...

BE CAREFUL IF YOU CHOOSE TO USE me!invID=nz(dmax("invID","Invoice"),0)+1.

If 2 users try to make an invoice at the same time, they will both get the same ref number because their record has not yet been saved. Its better

to

assign the number when you leave the record. This way it will look up the number, assign it, save it and exit all at once. You could use a
docmd.runcommand accmdsaverecord if you want, but I have seen some issues with hard drive traffic that stops this command from occuring.

HTH
Paul

"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:cn**********@news2.solcon.nl...

Ron wrote:
> Hi all
>
> I'm deciding whether to use the PK also as an account number, invoice
> number, transaction number, etc that the user will see for the

respective> files. I understand that sometimes a number will be missing, which is> not a
> problem for my purposes I don't think, but how would it be negative? Can> randomly created PK autonumber fields be negative? Any way for a PK,
> autonumbered, *incremented* to be negative?

With Jet sure it is possible. There is even a technical reason for this: the high order bit of the binary representation is interpreted as the
number being negative.

Autonumbers are known to make frantic jumps now and then. You cannot rely on them climbing steadily.

Using your own incremential number is not too difficult. You must confine all actions on such a table to routes via your code (either via function calls or through forms that do the calculation of the new number)

Suppose you have a table Invoice, PK=invID (long). Have this in the
BeforeInsert handler of a form based on this table:

me!invID=nz(dmax("invID","Invoice"),0)+1

This will yield 1 if there is no record yet, and the next free number if there are records.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Carlos Nunes-Ueno]

The recommendation that PKs be kept hidden from users stems from the
need for a PK to be a static value for each row in each table. If you
have a separate public identifier from the PK, then you can change
those identifiers at any time as needs be. If you're using the PK as
the identifier, then it really can't be changed without a whole mess
of updates and other pain and suffering.

So the question to ask yourself is "Will an
account/invoice/transaction number ever need to be changed?" It all
comes down to this issue. If the answer is a no, then you're okay
using a PK (either AutoNumber or roll-your-own) as an identifier. If
it's a yes, then you need to hide the PK and create another user
visible identifier. If you're not certain, the safer way to go is to
assume yes. Therefore many people consider it a best practice to hide
all PKs and create their own user visible identifiers.

Hope that helps,

Carlos

"Ron" <ro*******************@earthlink.com> wrote in message news:<j%*******************@newsread1.news.pas.ear thlink.net>...

Well, now I'm even *more* confused.

The first responder tells me the incremented autonumber field shouldn't be
used as a PK so gave me a nice routine to come up with my own PK. No prob.
However, you seem to be saying I shouldn't use the PK (I'd assume even the
one I come up with using this nice routine) as a user-visible account
number, invoice number, etc. So, then, I now need 2 numbers I come up with,
one to show the user and one to be the actual PK?

I feel like one of those lil' ducks at the arcade shooting gallery that
reverses directions with each direct hit. Boing, I'm going left, boing I'm
going right. Help! Somebody stop me!

ron