Help Coding Unique Numbers for Primary Keys

[Megan]

Hi-

I need some help/ advise on how to code unique numbers for the primary
keys of my 2 tables. I inherited a database that covers information
about Hearings and Rulings. Information about the Hearings and Rulings
were stored in 1 generic table called Case. I split it into 2 tables,
Hearings and Rulings. The problem is that the primary key, CaseID, is
an autonumber. I don't want the Hearings and Rulings to have the same
number.

For example:

CaseID = 22 = Ruling
CaseID = 23 = Ruling
CaseID = 24 = Hearing
CaseID = 25 = Ruling

After I split the tables, the last CaseID autonumber for the Rulings
was 25. The last record in my Hearings table has CaseID = 24. So my
next record in my Hearings table will haved a CaseID of 25, but the
last/ most recent record entered in tnr Rulings table has a CaseID =
25.

I would like to code it somehow so that all my Hearings CaseIDs are
"odd" and all my Rulings CaseIDs are "even."

Maybe something like CaseID + 1 for my Hearings and CaseID + 2 for my
Rulings. But I don't know where to put this info...in my form
somewhere when it loads or on current event...

Does anybody have any suggestions on the best way to percede?

Any help would be truly appreciated!

Thanks,

Megan

[PC Datasheet]

Re: Help Coding Unique Numbers for Primary Keys

Megan,

Hearings and rulings come out of Cases. Your tables need to look like:

TblJudge
JudgeID
JudgeFName
JudgeLName
etc

TblCase
CaseID
DocketNum
PlaintiffFName
PlaintiffLName
DefendentFName
DefendentLName
etc

TblHearing
HearingID
CaseID
HearingDate
JudgeID
etc

TblRuling
RulingID
CaseID
RulingDate
JudgeID
etc

You then need a form with two subforms to enter your data, The main form is
based on TblCase and the two subforms are based on TblHearing and TblRuling.

If you need help setting this up, contact me at my email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Megan" <meganrobertson 22@hotmail.com> wrote in message
news:5c14c12b.0 408091509.15cc5 f57@posting.goo gle.com...[color=blue]
> Hi-
>
> I need some help/ advise on how to code unique numbers for the primary
> keys of my 2 tables. I inherited a database that covers information
> about Hearings and Rulings. Information about the Hearings and Rulings
> were stored in 1 generic table called Case. I split it into 2 tables,
> Hearings and Rulings. The problem is that the primary key, CaseID, is
> an autonumber. I don't want the Hearings and Rulings to have the same
> number.
>
> For example:
>
> CaseID = 22 = Ruling
> CaseID = 23 = Ruling
> CaseID = 24 = Hearing
> CaseID = 25 = Ruling
>
> After I split the tables, the last CaseID autonumber for the Rulings
> was 25. The last record in my Hearings table has CaseID = 24. So my
> next record in my Hearings table will haved a CaseID of 25, but the
> last/ most recent record entered in tnr Rulings table has a CaseID =
> 25.
>
> I would like to code it somehow so that all my Hearings CaseIDs are
> "odd" and all my Rulings CaseIDs are "even."
>
> Maybe something like CaseID + 1 for my Hearings and CaseID + 2 for my
> Rulings. But I don't know where to put this info...in my form
> somewhere when it loads or on current event...
>
> Does anybody have any suggestions on the best way to percede?
>
> Any help would be truly appreciated!
>
> Thanks,
>
> Megan[/color]

[(Pete Cresswell)]

Re: Help Coding Unique Numbers for Primary Keys

RE/[color=blue]
> I don't want the Hearings and Rulings to have the same
>number....woul d like to code it somehow so that all my Hearings CaseIDs are
>"odd" and all my Rulings CaseIDs are "even."[/color]

Not that it's anybody elses' business, but why?

My kneejerk reaction is that something's wrong if a PK's value matters beyond
being unique within the table.

--
PeteCresswell

[David Schofield]

Re: Help Coding Unique Numbers for Primary Keys

On Tue, 10 Aug 2004 23:25:33 GMT, "(Pete Cresswell)" <x@y.z> wrote:
[color=blue]
>RE/[color=green]
>> I don't want the Hearings and Rulings to have the same
>>number....wou ld like to code it somehow so that all my Hearings CaseIDs are
>>"odd" and all my Rulings CaseIDs are "even."[/color]
>
>Not that it's anybody elses' business, but why?
>
>My kneejerk reaction is that something's wrong if a PK's value matters beyond
>being unique within the table.
>
>--
>PeteCresswel l[/color]

Hi
Well, I often do this to support what is in effect a data dictionary
so you can look something up by name or id and find what it is. You
can also do translations more easily if you have a single table for
names of things rather than scattering names in individual tables, and
automatically generate descriptions of things which don't have names
(in terms of the names of the table and the relevant key field names
and values).

However this leads to problems if users edit the tables directly which
I'm afraid some of them want to! I have vowed to use guids for all
such records but never got round to it.
David Schofield

[Bernard Peek]

Re: Help Coding Unique Numbers for Primary Keys

In message <5c14c12b.04080 91509.15cc5f57@ posting.google. com>, Megan
<meganrobertson 22@hotmail.com> writes

[color=blue]
>
>I would like to code it somehow so that all my Hearings CaseIDs are
>"odd" and all my Rulings CaseIDs are "even."[/color]

I've got two solutions for you. The first is technically complex but
shows how this problem would have been handled back in the old days. The
second is simpler but may be more difficult to sell to the business.

First Method
==========:

Some of the techniques used in databases that don't support autonumbers
will work. I haven't used these for years.

1) Create a new table, call it IDCounters, with two fields:

Table IDCounters
CaseTableName, string, primary key
MaxID, long integer

2) Alter your existing tables to make the ID fields into long-integers.
Makes sure they are still the primary key.

3) Create a record for each of the tables that you need ID's for. Set
the value of MaxID to the highest number that currently exists in the
table, presumably one of these will be even and the other odd.

4) When you create a new record in either of the case tables you need to
follow these steps.

a) Read the current value for MaxID from IDCounters

b) Add two to it

c) Write this new value as the ID in your case table

d) Update the relevant record in IDCounters

If this process is interrupted between c) and d) then you could possibly
have a situation where a new record has been created but the value of
MaxID has not been updated. The next time you try to add a record the
system will attempt to create a record with a duplicate primary key and
record creation will fail. You need to be able to handle that situation
in your code,.

Second Method
=============

Instead of altering the way the ID fields are created in the database,
alter the way they are published.

If you know for sure that the system will never need to handle more than
100,000 cases then add 100,000 to the autonumber for one set of cases
and perhaps add 200,000 to the other set. That way the two sets will be
easily distinguishable .



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.