DB Normalization

[S Jackson]

Can I change the primary keys in my tables after data has been added?

Because of ignorance and the lack of any kind of training (grrr. . . ), I
have used a field with REAL data in it as a primary key in my main table.

TIA
S. Jackson

 

[Douglas J. Steele]

Are you asking can you change what field(s) are the primary key in the
table, or can you change the value of a primary key?

In either case, the answer is yes, with some caveats.

If you're going to change what field(s) make up the primary key and you've
got relationships set up between tables, you'll have to change the foreign
key fields in all of the related tables. Add the new fields, and use update
queries to populate them. Once that's complete, you can delete the existing
relationship(s), then the existing PK, create the new PK then create the new
relationship(s).

If you're trying to change the value of a primary key, it can be done, but
it's usually a sign of poor design, since a PK is supposed to be immutable.

Oh yeah, make a backup copy before you try making the changes!

Good luck.

 

[PC Datasheet]

Open your table in design view and add a new field named RealData (or whatever
you want) and make it the same datatype as your existing primary key. Create an
update query that includes your existing primary key and the RealData field.
Where it says Update To under the RealData field, enter the following:
[NameOfYourPrimaryKeyField]

Run the query and the RealData field will become a duplicate of your primary key
field. Open your table in design view again and delete the primary key field. If
you have any relationships on the primary key field, you will have to delete the
relationships first.

Now add a new field to your table to be used for the primary key. Name the field
something like CustomerID and make it autonumber data type. Click on the key
icon on the toolbar to tell Access that your new field will be the primary key.
Close design view and open the table. Access will automatically populate the new
field with numbers starting at 1 and ending at the number of records in the
table.

 

[Pavel Romashkin]

I think that you can follow the suggestion of PC Datasheet by
duplicating the PK into a NewDataField and then not do anything else.
Leave the PK as it is (perhaps, reduce its length if its alphabetic and
existing values will allow it), but stop using it for real data. If it
is numeric, change it to autonumber and you are done. If it is not
numeric, use code to generate new unique PKs. You will not need to touch
the relationships at all, and will not need to modify related tables.

Pavel

 

[S Jackson]

Thanks everyone for trying to help:

Pavel / PC Datasheet: I "think" I am getting it, but am not sure I know
what the heck I am doing (gee big surprise there, eh?).

The PK field in the main table currently is a text field [DHSNo] containing
data like this: yy-xxxx-k. Now, before everyone freaks out about using one
field for multi-values or using dashes, understand that this field contains
a legal cause number assigned by a court of law. That is the how the Cause
number looks, period. It does not represent multi-values or a combination
of values. It is one value. I now know using a "-" is frowned on, but
that is the way the Cause Number looks, so if anyone has a better solution,
let me know.

My other tables contain the same PK field [DHSNo] and have a one-to-one
relationship with the main table.

So . .. with that said, I think what I want to do is add another field
(autonumber field) to my primary table? Make it PK? Add a PK autonumber
field to my other tables that are related?

As you can tell, I do not have a very good grasp on the relationship
business. Please let me know if I am on the right track here.

S. Jackson

 

[PC Datasheet]

Shelly,

You are definitely moving in the right direction!

Comments ----
1. You are now making the Cause# a data item; that's the correct thing to do!
There's nothing wrong with having the dashes in the number. Telephone numbers
have dashes! One thing you might consider is to add a CauseYear field. Although
you can get the year from the Cause#, a CauseYear field wil just make dealing
with your data easier.

2. You say you have other tables related to the main table in a one-to-one
relationship. Although there are times when you might do this, the vast majority
of the time tables are related one-to-many. You might reexamine the design of
your tables and evaluate why you have one-to-one relationships and evaluate if
you truly have a good table design overall.

3. Yes, add another field to your main table, make it autonumber and the PK.

4. DO NOT add a new field to your other tables!! What you need to do is
exchange the value in the [DHSNo] field for the equivalent autonumber in the
main table and make that field number data type - long integer.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks everyone for trying to help:

Pavel / PC Datasheet: I "think" I am getting it, but am not sure I know
what the heck I am doing (gee big surprise there, eh?).

The PK field in the main table currently is a text field [DHSNo] containing
data like this: yy-xxxx-k. Now, before everyone freaks out about using one
field for multi-values or using dashes, understand that this field contains
a legal cause number assigned by a court of law. That is the how the Cause
number looks, period. It does not represent multi-values or a combination
of values. It is one value. I now know using a "-" is frowned on, but
that is the way the Cause Number looks, so if anyone has a better solution,
let me know.

My other tables contain the same PK field [DHSNo] and have a one-to-one
relationship with the main table.

So . .. with that said, I think what I want to do is add another field
(autonumber field) to my primary table? Make it PK? Add a PK autonumber
field to my other tables that are related?

As you can tell, I do not have a very good grasp on the relationship
business. Please let me know if I am on the right track here.

S. Jackson

I think that you can follow the suggestion of PC Datasheet by
duplicating the PK into a NewDataField and then not do anything else.
Leave the PK as it is (perhaps, reduce its length if its alphabetic and
existing values will allow it), but stop using it for real data. If it
is numeric, change it to autonumber and you are done. If it is not
numeric, use code to generate new unique PKs. You will not need to touch
the relationships at all, and will not need to modify related tables.

Pavel

 

[S. Jackson]

Hey PC! Thanks so much for the encouragement.

1. And I like your idea of adding a Cause Year.

2. Well, this is tuff. This is the hardest part about building db's -
The initial planning on where to store all the crap (sorry developing a bad
'tude). As you may have guessed, this is to manage law (admin) cases. I
designed the following tables:

tblCaseInfo
tblRecords
tblHearings
tblTasks
tblStatus
tblDiscRec
tblDiscSent

All of the above have a one/one relationship with tblCaseInfo except
tblTasks. So are you saying I should lump the other tables into
tblCaseInfo? I designed it like I did above because there are just so many
fields! A lot to put in one table. And I thought this would be a logical
way to split it up and more manageable. My Master form has a main page
(tblCaseInfo) and then I created tabs/pages and inserted subforms for the
other tables - its purty! Anyway, what say you? Should I have stuck
to just the two tables: tblCaseInfo and tblTasks? btw, there are other
tables in the db, but its way tmi

3. So to normalize what I already have, I should simply create a new field
in tblCaseInfo and change the PK designation to it? If I do this, I have
to delete all the relationships don't i?

4. How do I do that? (doh!) DHSNo is a text field so how do I exchange
data (pluz pardon my ignorance - I am embarrassed that I hafta ask)

A BIG THANKYOU for all of your help so far. This db will be utilized
throughout the entire agency across the state and I sure don't want to look
more of a dummy than I already am! (Of course, you would think they would
have a real IT person do this - not a legal assistant for pete's sake! -
sorry ranting)

S. Jackson

Shelly,
-
You are definitely moving in the right direction!

Comments ----
1. You are now making the Cause# a data item; that's the correct thing to do!
There's nothing wrong with having the dashes in the number. Telephone numbers
have dashes! One thing you might consider is to add a CauseYear field. Although
you can get the year from the Cause#, a CauseYear field wil just make dealing
with your data easier.

2. You say you have other tables related to the main table in a one-to-one
relationship. Although there are times when you might do this, the vast majority
of the time tables are related one-to-many. You might reexamine the design of
your tables and evaluate why you have one-to-one relationships and evaluate if
you truly have a good table design overall.

3. Yes, add another field to your main table, make it autonumber and the PK.

4. DO NOT add a new field to your other tables!! What you need to do is
exchange the value in the [DHSNo] field for the equivalent autonumber in the
main table and make that field number data type - long integer.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks everyone for trying to help:

Pavel / PC Datasheet: I "think" I am getting it, but am not sure I know
what the heck I am doing (gee big surprise there, eh?).

The PK field in the main table currently is a text field [DHSNo] containing
data like this: yy-xxxx-k. Now, before everyone freaks out about using one
field for multi-values or using dashes, understand that this field contains
a legal cause number assigned by a court of law. That is the how the Cause
number looks, period. It does not represent multi-values or a combination
of values. It is one value. I now know using a "-" is frowned on, but
that is the way the Cause Number looks, so if anyone has a better solution,
let me know.

My other tables contain the same PK field [DHSNo] and have a one-to-one
relationship with the main table.

So . .. with that said, I think what I want to do is add another field
(autonumber field) to my primary table? Make it PK? Add a PK autonumber
field to my other tables that are related?

As you can tell, I do not have a very good grasp on the relationship
business. Please let me know if I am on the right track here.

S. Jackson

I think that you can follow the suggestion of PC Datasheet by
duplicating the PK into a NewDataField and then not do anything else.
Leave the PK as it is (perhaps, reduce its length if its alphabetic and
existing values will allow it), but stop using it for real data. If it
is numeric, change it to autonumber and you are done. If it is not
numeric, use code to generate new unique PKs. You will not need to touch
the relationships at all, and will not need to modify related tables.

Pavel

S Jackson wrote:

Can I change the primary keys in my tables after data has been added?

Because of ignorance and the lack of any kind of training (grrr. .

.. ),
I

have used a field with REAL data in it as a primary key in my main table.

TIA
S. Jackson

 

[PC Datasheet]

Shelly,

Here's how to analyze your tables --

Divide your database data into topics. You have done that to a degree. Topics
you have:
Records
Hearings
Tasks
Status
DiscRec
DiscSent
(DiscRec and DiscSent seem to be about a Disc. Is Disc one topic)

Next look at the fields you have in TblCaseInfo. Can they be divided into other
topics? If so, include those topics in the above list and color code the fields
by topic.

Preliminarily, TblCaseInfo should now look like:
TblCaseInfo
CaseInfoID
DHSNo
<No other fields for the moment>

Start with the Records topic and analyze all the topics
1. What data do you want in the database about records
2. Do ALL the fields you associate with Records pertain ONLY to records. Those
fields that do not belong in a different topic.
3. For any Cause# that will ever be in your database even if just one in a
thousand, will there be more than one record. If Yes, you need a separate table
for Records. If No, the record fields belong in TblCaseInfo. Note that the
number of data items in the database has no impact on whether you need a
TblRecords!

If you truly need a TblRecords, it should look like:
TblRecords
RecordID (PK)
CaseInfoID (foreign key)
<Other fields>

Repeat this analysis for each topic.

Regarding your question #4 ----
1. Create the new autonumber field CaseInfoID in TblCaseInfo
2. Add a new field named CaseInfoID to TblRecords and make it number - long
integer
3. Create a query that includes TblCaseInfo and TblRecords
4. In the query window, join the DHSNo fields in both tables. Delete any other
joins.
5. Include in the query only CaseInfoID from TblCaseInfo and the new field from
TblRecords
6. Go up to the toolbar and click on the icon for changing the type of query
and change the query to an update query.
7. Down in the new field where it says "Update To", enter [CaseInfoID] (the
square brackets are needed)
8. Run the query and the new field will become aduplicate of CaseInfoID
9. Delete the query
10. Open TblRecords in design view
11. Delete the DHSNo field. The new field, CaseInfo, is now your foreign key.

Steve
PC Datasheet

Hey PC! Thanks so much for the encouragement.

1. And I like your idea of adding a Cause Year.

2. Well, this is tuff. This is the hardest part about building db's -
The initial planning on where to store all the crap (sorry developing a bad
'tude). As you may have guessed, this is to manage law (admin) cases. I
designed the following tables:

tblCaseInfo
tblRecords
tblHearings
tblTasks
tblStatus
tblDiscRec
tblDiscSent

All of the above have a one/one relationship with tblCaseInfo except
tblTasks. So are you saying I should lump the other tables into
tblCaseInfo? I designed it like I did above because there are just so many
fields! A lot to put in one table. And I thought this would be a logical
way to split it up and more manageable. My Master form has a main page
(tblCaseInfo) and then I created tabs/pages and inserted subforms for the
other tables - its purty! Anyway, what say you? Should I have stuck
to just the two tables: tblCaseInfo and tblTasks? btw, there are other
tables in the db, but its way tmi

3. So to normalize what I already have, I should simply create a new field
in tblCaseInfo and change the PK designation to it? If I do this, I have
to delete all the relationships don't i?

4. How do I do that? (doh!) DHSNo is a text field so how do I exchange
data (pluz pardon my ignorance - I am embarrassed that I hafta ask)

A BIG THANKYOU for all of your help so far. This db will be utilized
throughout the entire agency across the state and I sure don't want to look
more of a dummy than I already am! (Of course, you would think they would
have a real IT person do this - not a legal assistant for pete's sake! -
sorry ranting)

S. Jackson

Shelly,
-
You are definitely moving in the right direction!

Comments ----
1. You are now making the Cause# a data item; that's the correct thing to do!
There's nothing wrong with having the dashes in the number. Telephone numbers
have dashes! One thing you might consider is to add a CauseYear field. Although
you can get the year from the Cause#, a CauseYear field wil just make dealing
with your data easier.

2. You say you have other tables related to the main table in a one-to-one
relationship. Although there are times when you might do this, the vast majority
of the time tables are related one-to-many. You might reexamine the design of
your tables and evaluate why you have one-to-one relationships and evaluate if
you truly have a good table design overall.

3. Yes, add another field to your main table, make it autonumber and the PK.

4. DO NOT add a new field to your other tables!! What you need to do is
exchange the value in the [DHSNo] field for the equivalent autonumber in the
main table and make that field number data type - long integer.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks everyone for trying to help:

Pavel / PC Datasheet: I "think" I am getting it, but am not sure I know
what the heck I am doing (gee big surprise there, eh?).

The PK field in the main table currently is a text field [DHSNo] containing
data like this: yy-xxxx-k. Now, before everyone freaks out about using one
field for multi-values or using dashes, understand that this field contains
a legal cause number assigned by a court of law. That is the how the Cause
number looks, period. It does not represent multi-values or a combination
of values. It is one value. I now know using a "-" is frowned on, but
that is the way the Cause Number looks, so if anyone has a better solution,
let me know.

My other tables contain the same PK field [DHSNo] and have a one-to-one
relationship with the main table.

So . .. with that said, I think what I want to do is add another field
(autonumber field) to my primary table? Make it PK? Add a PK autonumber
field to my other tables that are related?

As you can tell, I do not have a very good grasp on the relationship
business. Please let me know if I am on the right track here.

S. Jackson


I think that you can follow the suggestion of PC Datasheet by
duplicating the PK into a NewDataField and then not do anything else.
Leave the PK as it is (perhaps, reduce its length if its alphabetic and
existing values will allow it), but stop using it for real data. If it
is numeric, change it to autonumber and you are done. If it is not
numeric, use code to generate new unique PKs. You will not need to touch
the relationships at all, and will not need to modify related tables.

Pavel

S Jackson wrote:

Can I change the primary keys in my tables after data has been added?

Because of ignorance and the lack of any kind of training (grrr. . . ),
I
have used a field with REAL data in it as a primary key in my main
table.

TIA
S. Jackson

 

[John Vinson]

Can I change the primary keys in my tables after data has been added?

Because of ignorance and the lack of any kind of training (grrr. . . ), I
have used a field with REAL data in it as a primary key in my main table.

TIA
S. Jackson

If it's a valid primary key - i.e. unique, stable, and short - I see
no reason whatsoever to NOT use it. "Surrogate keys" or "Artificial
keys" such as Autonumbers have their place, and there are some folks
who always use them as a primary key, but there is no rule requiring
you to do so!

If you want to add an autonumber PK (say because your current PK isn't
as stable as you'ld like or because you may need to allow duplicates
of the data it contains), I'd create a new table with an autonumber
field added to the fields you now have, and run an Append query to
migrate your data into it. You'll also need to add a Long Integer
foreign key to every table that your table is linked to, and run
Update queries (linking on your current key) to fill it. A real chore
but doable!

 

[John Vinson]

The PK field in the main table currently is a text field [DHSNo] containing
data like this: yy-xxxx-k. Now, before everyone freaks out about using one
field for multi-values or using dashes, understand that this field contains
a legal cause number assigned by a court of law.

That said - I must disagree with PC Datasheet. It sounds like a
perfectly good natural key to me! It's unique; it's stable; it's
short. I'd just keep it as the PK. There would be very little
advantage to storing a copy of it redundantly!

 

[rkc]

The PK field in the main table currently is a text field [DHSNo] containing
data like this: yy-xxxx-k. Now, before everyone freaks out about using one
field for multi-values or using dashes, understand that this field contains
a legal cause number assigned by a court of law. That is the how the Cause
number looks, period. It does not represent multi-values or a combination
of values. It is one value. I now know using a "-" is frowned on, but
that is the way the Cause Number looks, so if anyone has a better solution,
let me know.

My other tables contain the same PK field [DHSNo] and have a one-to-one
relationship with the main table.

So . .. with that said, I think what I want to do is add another field
(autonumber field) to my primary table? Make it PK? Add a PK autonumber
field to my other tables that are related?

As you can tell, I do not have a very good grasp on the relationship
business. Please let me know if I am on the right track here.

Is all this because there is a chance the DHSNo may not be unique?
That's the only real reason not to use it as a primary key.
That would also make it useless as a foreign key.

 

[S. Jackson]

Few more questions, please (I do not truly understand the PK and Foreign key
thing and the entire relationship concept. I have read and read and I just
don't get it - duh.)

4. In the query window, join the DHSNo fields in both tables. Delete any other
joins.

DHSNo fields are already joined when I added the tables to the query because
of the pre-existing relationship.

7. Down in the new field where it says "Update To", enter [CaseInfoID] (the
square brackets are needed)

Actually, I had to type [tblCaseInfo]![CaseInfoID] (for those of you who are
following this link)

11. Delete the DHSNo field. The new field

, CaseInfo, is now your foreign key.

When I delete the DHSNo field, I get a message saying I can't w/o deleting

the relationships. So, do I go and delete the relationship?

Also, when do I go back to tblCaseInfo and change the PK from DHSNo to the
new field CaseInfoID? After all of the above?
Do I need to create a PK in my other tables, i.e. tblRecords? Right now they
have a PK, but it is the DHSNo field which instruct me to delete above.

Thanks for your patience!
S. Jackson

Shelly,

Here's how to analyze your tables --

Divide your database data into topics. You have done that to a degree. Topics
you have:
Records
Hearings
Tasks
Status
DiscRec
DiscSent
(DiscRec and DiscSent seem to be about a Disc. Is Disc one topic)

Next look at the fields you have in TblCaseInfo. Can they be divided into other
topics? If so, include those topics in the above list and color code the fields
by topic.

Preliminarily, TblCaseInfo should now look like:
TblCaseInfo
CaseInfoID
DHSNo
<No other fields for the moment>

Start with the Records topic and analyze all the topics
1. What data do you want in the database about records
2. Do ALL the fields you associate with Records pertain ONLY to records. Those
fields that do not belong in a different topic.
3. For any Cause# that will ever be in your database even if just one in a
thousand, will there be more than one record. If Yes, you need a separate table
for Records. If No, the record fields belong in TblCaseInfo. Note that the
number of data items in the database has no impact on whether you need a
TblRecords!

If you truly need a TblRecords, it should look like:
TblRecords
RecordID (PK)
CaseInfoID (foreign key)
<Other fields>

Repeat this analysis for each topic.

Regarding your question #4 ----
1. Create the new autonumber field CaseInfoID in TblCaseInfo
2. Add a new field named CaseInfoID to TblRecords and make it number - long
integer
3. Create a query that includes TblCaseInfo and TblRecords
4. In the query window, join the DHSNo fields in both tables. Delete any other
joins.
5. Include in the query only CaseInfoID from TblCaseInfo and the new field from
TblRecords
6. Go up to the toolbar and click on the icon for changing the type of query
and change the query to an update query.
7. Down in the new field where it says "Update To", enter [CaseInfoID] (the
square brackets are needed)
8. Run the query and the new field will become aduplicate of CaseInfoID
9. Delete the query
10. Open TblRecords in design view
11. Delete the DHSNo field. The new field, CaseInfo, is now your foreign key.

Steve
PC Datasheet

Hey PC! Thanks so much for the encouragement.

1. And I like your idea of adding a Cause Year.

2. Well, this is tuff. This is the hardest part about building db's -
The initial planning on where to store all the crap (sorry developing a bad
'tude). As you may have guessed, this is to manage law (admin) cases. I
designed the following tables:

tblCaseInfo
tblRecords
tblHearings
tblTasks
tblStatus
tblDiscRec
tblDiscSent

All of the above have a one/one relationship with tblCaseInfo except
tblTasks. So are you saying I should lump the other tables into
tblCaseInfo? I designed it like I did above because there are just so many
fields! A lot to put in one table. And I thought this would be a logical
way to split it up and more manageable. My Master form has a main page
(tblCaseInfo) and then I created tabs/pages and inserted subforms for the
other tables - its purty! Anyway, what say you? Should I have stuck
to just the two tables: tblCaseInfo and tblTasks? btw, there are other
tables in the db, but its way tmi

3. So to normalize what I already have, I should simply create a new field
in tblCaseInfo and change the PK designation to it? If I do this, I have
to delete all the relationships don't i?

4. How do I do that? (doh!) DHSNo is a text field so how do I exchange
data (pluz pardon my ignorance - I am embarrassed that I hafta ask)

A BIG THANKYOU for all of your help so far. This db will be utilized
throughout the entire agency across the state and I sure don't want to look
more of a dummy than I already am! (Of course, you would think they would
have a real IT person do this - not a legal assistant for pete's sake! -
sorry ranting)

S. Jackson

Shelly,
-
You are definitely moving in the right direction!

Comments ----
1. You are now making the Cause# a data item; that's the correct

thing to
do!

There's nothing wrong with having the dashes in the number. Telephone numbers
have dashes! One thing you might consider is to add a CauseYear field. Although
you can get the year from the Cause#, a CauseYear field wil just make dealing
with your data easier.

2. You say you have other tables related to the main table in a one-to-one
relationship. Although there are times when you might do this, the

vast
majority

of the time tables are related one-to-many. You might reexamine the

design
of

your tables and evaluate why you have one-to-one relationships and evaluate if
you truly have a good table design overall.

3. Yes, add another field to your main table, make it autonumber and

the
PK.

4. DO NOT add a new field to your other tables!! What you need to do is
exchange the value in the [DHSNo] field for the equivalent autonumber

in
the

main table and make that field number data type - long integer.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Thanks everyone for trying to help:

Pavel / PC Datasheet: I "think" I am getting it, but am not sure I know
what the heck I am doing (gee big surprise there, eh?).

The PK field in the main table currently is a text field [DHSNo] containing
data like this: yy-xxxx-k. Now, before everyone freaks out about

using
one

field for multi-values or using dashes, understand that this field contains
a legal cause number assigned by a court of law. That is the how

the
Cause

number looks, period. It does not represent multi-values or a combination
of values. It is one value. I now know using a "-" is frowned on, but
that is the way the Cause Number looks, so if anyone has a better solution,
let me know.

My other tables contain the same PK field [DHSNo] and have a one-to-one
relationship with the main table.

So . .. with that said, I think what I want to do is add another field
(autonumber field) to my primary table? Make it PK? Add a PK autonumber
field to my other tables that are related?

As you can tell, I do not have a very good grasp on the relationship
business. Please let me know if I am on the right track here.

S. Jackson


I think that you can follow the suggestion of PC Datasheet by
duplicating the PK into a NewDataField and then not do anything else.
Leave the PK as it is (perhaps, reduce its length if its

alphabetic
and

existing values will allow it), but stop using it for real data. If it
is numeric, change it to autonumber and you are done. If it is not
numeric, use code to generate new unique PKs. You will not need to touch
the relationships at all, and will not need to modify related tables.

Pavel

S Jackson wrote:

Can I change the primary keys in my tables after data has been added?

Because of ignorance and the lack of any kind of training (grrr.

..
. ),

I
have used a field with REAL data in it as a primary key in my main
table.

TIA
S. Jackson

 

[PC Datasheet]

Did you analyze your tables including TblCaseInfo before doing all this?

#4 Yes, that's OK!

#7 Way to go in knowing to do that!

#11 Yes, delete the relationship between DHSNo in both tables. Your new
relationship will be between CaseInfoID in both tables.

<Also, when do I go back to tblCaseInfo and change the PK from DHSNo to the new
field CaseInfoID? After all of the above?>

I don't understand your question. You should have added CaseInFoId to
TblCaseInfo and made it the PK before doing all this. In TblRecords your primary
key should be RecordID, foreign key should be CaseInfoID and your relationship
between the two tables should have been dran between CaseInfoID in both tables.

Yes, you need to repeat this process for all your tables. Again though, did you
analyze your tables before beginning all this?

Steve

Few more questions, please (I do not truly understand the PK and Foreign key
thing and the entire relationship concept. I have read and read and I just
don't get it - duh.)

4. In the query window, join the DHSNo fields in both tables. Delete any other
joins.

DHSNo fields are already joined when I added the tables to the query because
of the pre-existing relationship.

7. Down in the new field where it says "Update To", enter [CaseInfoID] (the
square brackets are needed)

Actually, I had to type [tblCaseInfo]![CaseInfoID] (for those of you who are
following this link)

11. Delete the DHSNo field. The new field

, CaseInfo, is now your foreign key.

When I delete the DHSNo field, I get a message saying I can't w/o deleting

the relationships. So, do I go and delete the relationship?

Also, when do I go back to tblCaseInfo and change the PK from DHSNo to the
new field CaseInfoID? After all of the above?
Do I need to create a PK in my other tables, i.e. tblRecords? Right now they
have a PK, but it is the DHSNo field which instruct me to delete above.

Thanks for your patience!
S. Jackson

Shelly,

Here's how to analyze your tables --

Divide your database data into topics. You have done that to a degree. Topics
you have:
Records
Hearings
Tasks
Status
DiscRec
DiscSent
(DiscRec and DiscSent seem to be about a Disc. Is Disc one topic)

Next look at the fields you have in TblCaseInfo. Can they be divided into other
topics? If so, include those topics in the above list and color code the fields
by topic.

Preliminarily, TblCaseInfo should now look like:
TblCaseInfo
CaseInfoID
DHSNo
<No other fields for the moment>

Start with the Records topic and analyze all the topics
1. What data do you want in the database about records
2. Do ALL the fields you associate with Records pertain ONLY to records. Those
fields that do not belong in a different topic.
3. For any Cause# that will ever be in your database even if just one in a
thousand, will there be more than one record. If Yes, you need a separate table
for Records. If No, the record fields belong in TblCaseInfo. Note that the
number of data items in the database has no impact on whether you need a
TblRecords!

If you truly need a TblRecords, it should look like:
TblRecords
RecordID (PK)
CaseInfoID (foreign key)
<Other fields>

Repeat this analysis for each topic.

Regarding your question #4 ----
1. Create the new autonumber field CaseInfoID in TblCaseInfo
2. Add a new field named CaseInfoID to TblRecords and make it number - long
integer
3. Create a query that includes TblCaseInfo and TblRecords
4. In the query window, join the DHSNo fields in both tables. Delete any other
joins.
5. Include in the query only CaseInfoID from TblCaseInfo and the new field from
TblRecords
6. Go up to the toolbar and click on the icon for changing the type of query
and change the query to an update query.
7. Down in the new field where it says "Update To", enter [CaseInfoID] (the
square brackets are needed)
8. Run the query and the new field will become aduplicate of CaseInfoID
9. Delete the query
10. Open TblRecords in design view
11. Delete the DHSNo field. The new field, CaseInfo, is now your foreign key.

Steve
PC Datasheet

Hey PC! Thanks so much for the encouragement.

1. And I like your idea of adding a Cause Year.

2. Well, this is tuff. This is the hardest part about building db's -
The initial planning on where to store all the crap (sorry developing a bad
'tude). As you may have guessed, this is to manage law (admin) cases. I
designed the following tables:

tblCaseInfo
tblRecords
tblHearings
tblTasks
tblStatus
tblDiscRec
tblDiscSent

All of the above have a one/one relationship with tblCaseInfo except
tblTasks. So are you saying I should lump the other tables into
tblCaseInfo? I designed it like I did above because there are just so many
fields! A lot to put in one table. And I thought this would be a logical
way to split it up and more manageable. My Master form has a main page
(tblCaseInfo) and then I created tabs/pages and inserted subforms for the
other tables - its purty! Anyway, what say you? Should I have stuck
to just the two tables: tblCaseInfo and tblTasks? btw, there are other
tables in the db, but its way tmi

3. So to normalize what I already have, I should simply create a new field
in tblCaseInfo and change the PK designation to it? If I do this, I have
to delete all the relationships don't i?

4. How do I do that? (doh!) DHSNo is a text field so how do I exchange
data (pluz pardon my ignorance - I am embarrassed that I hafta ask)

A BIG THANKYOU for all of your help so far. This db will be utilized
throughout the entire agency across the state and I sure don't want to look
more of a dummy than I already am! (Of course, you would think they would
have a real IT person do this - not a legal assistant for pete's sake! -
sorry ranting)

S. Jackson



Shelly,
-
You are definitely moving in the right direction!

Comments ----
1. You are now making the Cause# a data item; that's the correct thing to
do!
There's nothing wrong with having the dashes in the number. Telephone
numbers
have dashes! One thing you might consider is to add a CauseYear field.
Although
you can get the year from the Cause#, a CauseYear field wil just make
dealing
with your data easier.

2. You say you have other tables related to the main table in a
one-to-one
relationship. Although there are times when you might do this, the vast
majority
of the time tables are related one-to-many. You might reexamine the design
of
your tables and evaluate why you have one-to-one relationships and
evaluate if
you truly have a good table design overall.

3. Yes, add another field to your main table, make it autonumber and the
PK.

4. DO NOT add a new field to your other tables!! What you need to do is
exchange the value in the [DHSNo] field for the equivalent autonumber in
the
main table and make that field number data type - long integer.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Thanks everyone for trying to help:

Pavel / PC Datasheet: I "think" I am getting it, but am not sure I know
what the heck I am doing (gee big surprise there, eh?).

The PK field in the main table currently is a text field [DHSNo]
containing
data like this: yy-xxxx-k. Now, before everyone freaks out about using
one
field for multi-values or using dashes, understand that this field
contains
a legal cause number assigned by a court of law. That is the how the
Cause
number looks, period. It does not represent multi-values or a
combination
of values. It is one value. I now know using a "-" is frowned on, but
that is the way the Cause Number looks, so if anyone has a better
solution,
let me know.

My other tables contain the same PK field [DHSNo] and have a one-to-one
relationship with the main table.

So . .. with that said, I think what I want to do is add another field
(autonumber field) to my primary table? Make it PK? Add a PK
autonumber
field to my other tables that are related?

As you can tell, I do not have a very good grasp on the relationship
business. Please let me know if I am on the right track here.

S. Jackson


I think that you can follow the suggestion of PC Datasheet by
duplicating the PK into a NewDataField and then not do anything else.
Leave the PK as it is (perhaps, reduce its length if its alphabetic
and
existing values will allow it), but stop using it for real data. If it
is numeric, change it to autonumber and you are done. If it is not
numeric, use code to generate new unique PKs. You will not need to
touch
the relationships at all, and will not need to modify related tables.

Pavel

S Jackson wrote:

Can I change the primary keys in my tables after data has been
added?

Because of ignorance and the lack of any kind of training (grrr. .
. ),
I
have used a field with REAL data in it as a primary key in my main
table.

TIA
S. Jackson

 

[S. Jackson]

Well, I am certainly relieved to hear that it may not be necessary to change
the PK. The DHSNo is a unique field and there can be no duplicates (what a
nightmare that would be for the court clerk). The reason I went down this
bunny trail in the first place is that I guess I misunderstood some of the
posts below in response to my earlier post below entitled: Data
Normalization Dispute. Specifically, poster "Rolls" who says: " The
reason for not making the case number the PK is that this field HAS MEANING!
The instant that a key field has meaning becomes the instant that it should
NOT be a primary key." So do I assume that this is some sort of ongoing
debate between professionals?

I haven't made any changes to my original db at this point. I was only
fooling with copies. From the little bit that I did, I quickly realized
that changing the PK at this point would be a nightmare and I do NOT want to
do it if it is not necessary.

Also, PC kept asking me if I analyzed the tables. What specifically am I
looking for? PC stated that I should do an analysis because of the
one-to-one relationships between a lot of the tables. But if I lump all the
tables together, I get a gigantic table with many, many fields. This was
the reason I split the data up into logical groups -Record requests,
Discovery Sent, Disovery Received, Hearing, Status, Tasks.

Further comments are welcome. I am learning so much here. Thank you all
for your patience.

S. Jackson

 

[Cheryl Fischer]

Hi Shelly,

The instant that a key field has meaning becomes the instant that it should
NOT be a primary key." So do I assume that this is some sort of ongoing
debate between professionals?

Not really much of a debate. "Intelligent keys" - key fields which contain
more than one fact - are frowned upon, and most developers will subscribe to
the theory of "one fact = one field". But, the fact of life when designing
databases used for managing legal cases is that you do not control the
creation of a Cause Number. So, while the court may use three facts:

04 = Year case filed
0002 = Second case filed
K = Region, Department, etc.

to build Cause Number 04-0002-K, by the time this Cause Number reaches your
database, it is ONE fact. However, do keep this concept in mind for any
future development projects.

But if I lump all the
tables together, I get a gigantic table with many, many fields. This was
the reason I split the data up into logical groups -Record requests,
Discovery Sent, Disovery Received, Hearing, Status, Tasks.

It sounds as if you have a reasonable one-to-many design here. It is safe
to assume that there will be many instances of Requests for Discovery, Dates
on which Discovery materials are sent, Dates on which Discovery materials
are received, Hearings, etc., for each individual case.




--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

 

[S. Jackson]

Thank you Cheryl. What is the issue here?

Issue One: Do not combine more than one fact into a single field. - Yes, I
agree.

Issue Two: Can you use a field that contains REAL data as a PK? Or do you
create a meaningless autonumber field to be used as the PK?

It is my understanding from the statement:
meant that I should create a meaningless field (field that does not contain
REAL data) to be used as the PK - always.

As for the tables: Currently, Each Case has one record in the Discovery
tables. Each Case has only one record in the Hearings table - there is only
one hearing. Each case has one record in the Status table - only one status
of a individual case, etc. The only one-to-many relationship existing
currently is that each Case has many tasks.

However, you have given me something to think about regarding the design of
the Records table and Discovery tables. Maybe a better design is for each
Case to have many records in the the Discovery tables (I still want to break
it by Disc Rec'd and Disc Sent, or should I make it a field in a single
table?) - each record detailing what kind of disc (RFI, RFD, etc), who sent
it and dates, etc. This would mean creating a combo box listing the kinds
of discovery received. hummm. . . This way it would accomodate multiple
instances of supplemental discovery which I have not allowed for. I wonder
how hard this change is going to be to make now, however, after our office
has been using the new db and it contains a lot of data.

Also there are instances when we make more than one records request for a
single case. Currently, I enter something in the comment field of the
Records table that notes this. A better design would be to have many
requests in the Records table for each case.

Yikes. . . I think I'm actually getting it!

Thanks again. I am going to go try some of these table design changes out -
wish me luck. Any further comments are welcome!
Shelly

 

[Rick Brandt]

Thank you Cheryl. What is the issue here?

Issue One: Do not combine more than one fact into a single field. - Yes, I
agree.

Issue Two: Can you use a field that contains REAL data as a PK? Or do you
create a meaningless autonumber field to be used as the PK?

It is my understanding from the statement:

meant that I should create a meaningless field (field that does not contain
REAL data) to be used as the PK - always.

That's overstated and is a matter of opinion and personal preference rather than
a recognized "rule".

There is nothing inherently wrong with a PK consisting of real data, but there
are complications that arise when a PK value needs to change and this is nearly
always a possibility if the PK holds meaningful data.

Some would say "No problem, just cascade updates". Well, not all DBMS support
cascade updates. UDB on the IBM ISeries does not and SQL Server has only had
this in the last rev or two. In those situations you have to use Triggers or
application code to cascade the changes and many developers would rather just
avoid the problem by using a surrogate key.

 

[Cheryl Fischer]

Issue Two: Can you use a field that contains REAL data as a PK? Or do
you

create a meaningless autonumber field to be used as the PK?

Sure, you can use real data as a PK - as long as that data will be unique.
Emphasis on "unique". If there is any possibility at all that a duplicate
Cause Number could hit your database (say, two different cases, from two
different jurisdictions, each jurisdiction using a YY-9999-A scheme), you
will have a problem. In your environment, the risk of lack of uniqueness
may be small, but ...

This is why I earlier recommended an Autonumber as the PK. For case
management systems, I have used Autonumber as the PK and also set a unique
index on Cause Number and Jurisdiction.

Rick Brandt's comment regarding the possibility of a change in value of the
PK is also an important consideration!

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

 

[PC Datasheet]

<<Also, PC kept asking me if I analyzed the tables. What specifically am I
looking for?>>

My third post to you in this thread started out with "Here's how to analyze your
tables --"

Read my lips!

"PC"

 

[Rolls]

There appears to be a conceptual problem (or two) here.

Your "entity" tables must contain PK fields. Relationship tables refer to
those keys as FK fields. tblPeople contains records, each with a PK to
denote one unique person, each. As long as that person exists in the
database, that PK field should never change. The PK field typically is an
Autonumber field, is automatically assigned, and is not seen by the user.
It's used to link tables PK <-> FK. The fact that you have multiple joined
tables is due to the fact that you have multiple entities and relationships
in your model. There is not a 1 : 1 relationship between fields that are
not within the same table.

If you're thinking in terms of changing key values, this probably indicates
that your model isn't correct; you haven't normalized your tables.

In terms of using an Autonumber PK instead of a "case number" (which may be
unique, too) the autonumber is an integer which takes less space and runs
faster than a text field used as a key. If the text field has meaning, and
the meaning changes, you've just destroyed referential integrity and have to
rebuild keys and relink fields, which is unnecessary with good design. In
your DB the "case number" (field) is as attribute of the "case" (record)
which the Autonumber key field denotes as "unique".

Stop trying to complicate something that is simple.