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

Nov 13 '05 #1

Subscribe Reply

1656

PC Datasheet

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******@pcdata sheet.com
www.pcdatasheet.com

"Megan" <me************ **@hotmail.com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

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

(Pete Cresswell)

RE/

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."

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

David Schofield

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....wou ld 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.

--
PeteCresswel l

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

Bernard Peek

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

Similar topics

2627

unique keys

by: reneeccwest | last post by:

Hello, I plan to create a table with 3 unique keys. Combination of three fields has to be unique for each row in a table that are vendor ID (char 8), vendor name (char 40), and vendor office (5).

Microsoft SQL Server

1794

Primary Key: I am trying to understand--please help if you can.

by: Mavis Tilden | last post by:

Hi all, So I've been reading the newsgroups, and reading a few books trying to learn SQL and SQL Server 2000. The books tell me I need a Primary Key, and that every table should have one. I know (I think) that a Primary Key is a special field that uniquely identifies each record or row within a table. My question is this: If I have a field or column whose values are all (and will be) different in every row, is that what a Primary Key...

Microsoft SQL Server

1480

Help

by: Pir | last post by:

I have a created a table 'data' with two columns: id, name. +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | name | varchar(50) | | PRI | | |...

MySQL Database

2100

primary =/=> unique?

by: Bob Stearns | last post by:

I was under the impression that the primary key had to be a unique index. Since I usually create my primary indices before my primary keys, in order to get the indices in the same schema as their tables, it is possible , by error, to create such an index without the unique attribute. DB2 UDB 8.1.5 Linux uses such an index for the primary key anyway, thus losing the unique property of the primary key. Is this a bug or a feature, i.e. a...

DB2 Database

1592

manually creating unique IDs

by: Darrel | last post by:

I have a table that stores the content for a web site I'm making. Each one has a unique ID so I can grab it via the querystring. Since it's being done via a query string, I didn't want to use default GUIDs, since they're just really verbose. So, I'm just making my own. What's the best way to check for uniqueness on insert? My thinking was that I'd query the ID field, set it to an array, and then loop through all of the variables. ie:

ASP.NET

2605

form field auto numbering with unique value

by: Chris | last post by:

Before I started to create table, etc to track unique form field record number assigments I thought I'd check to see if there is now a better way to do this in .NET. I have a parent form (table) and children form (table). Relationship equals one to many. I'd like to auto number the fields accordingly and traditionaly I assign a unique number based on a table value that I retrieve + 1. i.e. Parent record field value = 1 Children record...

ASP.NET

1579

Weird Unique ID

by: Saso Zagoranski | last post by:

Hi, I'm making a simple application which will store different items (in a SQL server 2000 - MSDE database). Each item has a unique ID in this form: / / / an example: AZ12345/1234/1/A

C# / C Sharp

1709

adding unique keys

by: HandersonVA | last post by:

Would anyone please instruct how to prevent the duplicate record by setting the unique keys on the ms sql server? i've been checking the duplicate record as front-end and i found out if there is an internet delay or some other reasons, it has a chance to store the duplicated data into the database. so i realized it has to be done on the back-end side. for example, if i have three columns (office code, office id, office section) as a...

Microsoft SQL Server

1887

unique numbers for table

by: jasonrholland | last post by:

i have to build a table within access that receives it's information from an ASP web page and generates the primary key, with numbers and letters, from the data submitted. a numer would be like this 06TTAG1202, but a little more complicated . the 06 would be from the current year at time of submission, the TTAG would remain constant, the 12 would come from the information in the county feild (counties x,y, and z would receive the...

Microsoft Access / VBA

9585

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10338

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9161

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7622

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6856

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5525

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5658

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4301

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2997

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General