Database design help.

I am trying to make a db with client notes in it. My problem is I need to be
able to track the client name changes. For example: client comes in today
name is john doe, a note (form completed) is done and saved, now 3 months
later client comes in and name is john smith. I need the first note to still
be saved as john doe and not changeable and make sure john doe and john smith
are same person, which I have done by clientid. I want to be able to see all
notes done by john doe and john smith. Sorry if this makes no sense. I know
what I want just having trouble explaining. Thanks for any help.

TblClientDataNeverChanges
ClientDataNeverChangesID
Client Data Fields That Never Change

TblClientAlias
ClientAliasID
ClientDataNeverChangesID
VisitDate
Note

Steve


"kids23bball" wrote in message
...
I am trying to make a db with client notes in it. My problem is I need to
be
able to track the client name changes. For example: client comes in
today
name is john doe, a note (form completed) is done and saved, now 3 months
later client comes in and name is john smith. I need the first note to
still
be saved as john doe and not changeable and make sure john doe and john
smith
are same person, which I have done by clientid. I want to be able to see
all
notes done by john doe and john smith. Sorry if this makes no sense. I
know
what I want just having trouble explaining. Thanks for any help.

as with any entity that requires historical data to be preserved, you need a
child table. your description is sketchy, so i'll show "sample" tables, and
let you apply the principles to your own design:

tblClients
ClientID (primary key)
other fields that describe a client AND don't change OR don't need a
history of changes

tblClientNames
NameID (primary key)
ClientID (foreign key from tblClients)
FirstName
LastName
DateEntered

tblClientNotes
NoteID (pk)
NameID (foreign key from tblClientNames)
Notes

relationships:
tblClients.ClientID 1:n tblClientNames.ClientID
tblClientNames.NameID 1:n tblClientNotes.NameID

you do *not* need to put the ClientID field in tblClientNotes, because each
note is directly linked to a specific clientname, which in turn is directly
linked to a specific client. for any given client, the newest (maximum) date
in the related ClientNames records will signify the "current" client name.

hth


"kids23bball" wrote in message
...
I am trying to make a db with client notes in it. My problem is I need to
be
able to track the client name changes. For example: client comes in
today
name is john doe, a note (form completed) is done and saved, now 3 months
later client comes in and name is john smith. I need the first note to
still
be saved as john doe and not changeable and make sure john doe and john
smith
are same person, which I have done by clientid. I want to be able to see
all
notes done by john doe and john smith. Sorry if this makes no sense. I
know
what I want just having trouble explaining. Thanks for any help.

I'm sorry, I'm not following you. Here's a better example of what I am asking.

11/8/08 Jane Doe, 101 main st, mytown, il comes in and a contact note is
typed on her and saved.
12/21/08 Jane Doe (now Jane Smith), 125 South St, yourtown, il comes back
and another note is typed and saved.

I need to make sure that when Jane's name & address changed the original
note stayed the same.

Thanks again for any help. Sorry I'm not following you.



"Steve" wrote:

TblClientDataNeverChanges
ClientDataNeverChangesID
Client Data Fields That Never Change

TblClientAlias
ClientAliasID
ClientDataNeverChangesID
VisitDate
Note

Steve


"kids23bball" wrote in message
...
I am trying to make a db with client notes in it. My problem is I need to
be
able to track the client name changes. For example: client comes in
today
name is john doe, a note (form completed) is done and saved, now 3 months
later client comes in and name is john smith. I need the first note to
still
be saved as john doe and not changeable and make sure john doe and john
smith
are same person, which I have done by clientid. I want to be able to see
all
notes done by john doe and john smith. Sorry if this makes no sense. I
know
what I want just having trouble explaining. Thanks for any help.

"Steve" wrote in message
m...
TblClientDataNeverChanges
ClientDataNeverChangesID
Client Data Fields That Never Change

TblClientAlias
ClientAliasID
ClientDataNeverChangesID
VisitDate
Note

Steve

This is total nonsense. Do you actually read the requests before spewing
your nonsense?

John... Visio MVP

You are also calling Tina a jerk; she gave the same answer.


"John... Visio MVP" wrote in message
...
"Steve" wrote in message
m...
TblClientDataNeverChanges
ClientDataNeverChangesID
Client Data Fields That Never Change

TblClientAlias
ClientAliasID
ClientDataNeverChangesID
VisitDate
Note

Steve

This is total nonsense. Do you actually read the requests before spewing
your nonsense?

John... Visio MVP

"Steve" wrote in message
m...
You are also calling Tina a jerk; she gave the same answer.


Ummm ... no, no she did not.

Tina,

Thank you very much for your response. It has helped tremendous. However,
I am still struggling. I know I should know this but I need an explanation
of the foreign keys.

"ClientID (foreign key from tblClients)" and also

the 1:n in this line

tblClients.ClientID 1:n tblClientNames.ClientID

Thanks again

"tina" wrote:

as with any entity that requires historical data to be preserved, you need a
child table. your description is sketchy, so i'll show "sample" tables, and
let you apply the principles to your own design:

tblClients
ClientID (primary key)
other fields that describe a client AND don't change OR don't need a
history of changes

tblClientNames
NameID (primary key)
ClientID (foreign key from tblClients)
FirstName
LastName
DateEntered

tblClientNotes
NoteID (pk)
NameID (foreign key from tblClientNames)
Notes

relationships:
tblClients.ClientID 1:n tblClientNames.ClientID
tblClientNames.NameID 1:n tblClientNotes.NameID

you do *not* need to put the ClientID field in tblClientNotes, because each
note is directly linked to a specific clientname, which in turn is directly
linked to a specific client. for any given client, the newest (maximum) date
in the related ClientNames records will signify the "current" client name.

hth


"kids23bball" wrote in message
...
I am trying to make a db with client notes in it. My problem is I need to
be
able to track the client name changes. For example: client comes in
today
name is john doe, a note (form completed) is done and saved, now 3 months
later client comes in and name is john smith. I need the first note to
still
be saved as john doe and not changeable and make sure john doe and john
smith
are same person, which I have done by clientid. I want to be able to see
all
notes done by john doe and john smith. Sorry if this makes no sense. I
know
what I want just having trouble explaining. Thanks for any help.

Hello again, Sue,

First to answer your question about my original response. You need to find a
way (one or more fields) to identify in the database that Jane Doe and Jane
Smith are the same person. These fields go in TblClientDataNeverChanges. Now
from your example, you need to change TblClientAlias to:

TblClientAlias
ClientAliasID
ClientDataNeverChangesID
FirstName
LastName
Address
City
State
PostalCode
VisitDate
Note

On the first visit, you enter the data that identifies the person by other
than name and address (in the fields previously mentioned) in
TblClientDataNeverChanges and then enter Jane Doe, 101 main st, mytown along
with the first note in TblClientAlias. You can do this with a main form
based on TblClientDataNeverChanges and a subform based on TblClientAlias. On
the second visit, you navigate in the main form via the field(s) that
identify the person by other than name and address and you should see Jane
Doe in the subform. You then go to a new record in the subform and enter
Jane Smith, 125 South St, yourtown along with the second note in the
subform. The key to making this work is the field(s) you use to identify the
person by other than name and address in TblClientDataNeverChanges.

Primary Key and Foreign Key
In my suggested tables, ClientDataNeverChangesID is the primary key in
TblClientDataNeverChanges and
ClientDataNeverChangesID in TblClientAlias is the foreign key that relates
records in TblClientAlias to a specific record in TblClientDataNeverChanges.
In the form/subform, ClientDataNeverChangesID will be automatically entered
by Access in TblClientDataNeverChanges when you enter a new record in the
main form and ClientDataNeverChangesID will be automatically entered by
Access in TblClientAlias when you enter a new record in the subform.

Steve

"sue gray" wrote in message
...
Tina,

Thank you very much for your response. It has helped tremendous.
However,
I am still struggling. I know I should know this but I need an
explanation
of the foreign keys.

"ClientID (foreign key from tblClients)" and also

the 1:n in this line

tblClients.ClientID 1:n tblClientNames.ClientID

Thanks again

"tina" wrote:

as with any entity that requires historical data to be preserved, you
need a
child table. your description is sketchy, so i'll show "sample" tables,
and
let you apply the principles to your own design:

tblClients
ClientID (primary key)
other fields that describe a client AND don't change OR don't need a
history of changes

tblClientNames
NameID (primary key)
ClientID (foreign key from tblClients)
FirstName
LastName
DateEntered

tblClientNotes
NoteID (pk)
NameID (foreign key from tblClientNames)
Notes

relationships:
tblClients.ClientID 1:n tblClientNames.ClientID
tblClientNames.NameID 1:n tblClientNotes.NameID

you do *not* need to put the ClientID field in tblClientNotes, because
each
note is directly linked to a specific clientname, which in turn is
directly
linked to a specific client. for any given client, the newest (maximum)
date
in the related ClientNames records will signify the "current" client
name.

hth


"kids23bball" wrote in message
...
I am trying to make a db with client notes in it. My problem is I need
to
be
able to track the client name changes. For example: client comes in
today
name is john doe, a note (form completed) is done and saved, now 3
months
later client comes in and name is john smith. I need the first note to
still
be saved as john doe and not changeable and make sure john doe and john
smith
are same person, which I have done by clientid. I want to be able to
see
all
notes done by john doe and john smith. Sorry if this makes no sense.
I
know
what I want just having trouble explaining. Thanks for any help.

you need to read up on the principles of relational design. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

tblClients.ClientID 1:n tblClientNames.ClientID

the above is a "shorthand" way of describing the table relationships:
ClientID is the primary key of tblClients, and it is the "one" side of a
one-to-many relationship with its' matching foreign key field ClientID in
tblClientNames.

hth


"sue gray" wrote in message
...
Tina,

Thank you very much for your response. It has helped tremendous.
However,
I am still struggling. I know I should know this but I need an
explanation
of the foreign keys.

"ClientID (foreign key from tblClients)" and also

the 1:n in this line

tblClients.ClientID 1:n tblClientNames.ClientID

Thanks again

"tina" wrote:

as with any entity that requires historical data to be preserved, you
need a
child table. your description is sketchy, so i'll show "sample" tables,
and
let you apply the principles to your own design:

tblClients
ClientID (primary key)
other fields that describe a client AND don't change OR don't need a
history of changes

tblClientNames
NameID (primary key)
ClientID (foreign key from tblClients)
FirstName
LastName
DateEntered

tblClientNotes
NoteID (pk)
NameID (foreign key from tblClientNames)
Notes

relationships:
tblClients.ClientID 1:n tblClientNames.ClientID
tblClientNames.NameID 1:n tblClientNotes.NameID

you do *not* need to put the ClientID field in tblClientNotes, because
each
note is directly linked to a specific clientname, which in turn is
directly
linked to a specific client. for any given client, the newest (maximum)
date
in the related ClientNames records will signify the "current" client
name.

hth


"kids23bball" wrote in message
...
I am trying to make a db with client notes in it. My problem is I
need to
be
able to track the client name changes. For example: client comes in
today
name is john doe, a note (form completed) is done and saved, now 3
months
later client comes in and name is john smith. I need the first note
to
still
be saved as john doe and not changeable and make sure john doe and
john
smith
are same person, which I have done by clientid. I want to be able to
see
all
notes done by john doe and john smith. Sorry if this makes no sense.
I
know
what I want just having trouble explaining. Thanks for any help.