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

Help Coding Unique Numbers for Primary Keys

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
re******@pcdatasheet.com
www.pcdatasheet.com

"Megan" <me**************@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...
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

Nov 13 '05 #2

P: n/a
RE/
I don't want the Hearings and Rulings to have the same
number....would like to code it somehow so that all my Hearings CaseIDs are
"odd" and all my Rulings CaseIDs are "even."


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
Nov 13 '05 #3

P: n/a
On Tue, 10 Aug 2004 23:25:33 GMT, "(Pete Cresswell)" <x@y.z> wrote:
RE/
I don't want the Hearings and Rulings to have the same
number....would like to code it somehow so that all my Hearings CaseIDs are
"odd" and all my Rulings CaseIDs are "even."


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


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

Nov 13 '05 #4

P: n/a
In message <5c**************************@posting.google.com >, Megan
<me**************@hotmail.com> writes


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


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.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.