Database design help.

Thanks for the reply. I will do some more reading. Thanks again.

"tina" wrote:

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.

"Steve" wrote in message
m...

Wow, Dr Frankenstien meets MS Access.

you're very welcome


"sue gray" wrote in message
...
Thanks for the reply. I will do some more reading. Thanks again.

"tina" wrote:

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.