Creating Client Tracking DB, need advice

[Mike]

Folks,

I'm new to Access 2003 database design, but I've been asked to create a DB
for tracking our clients. I'm starting out quite simple, and hope to modify
and expand as I learn more. What I have done is create a single table
capturing all of the information I currently have been asked to capture, such
as Contact Information, Demographics, Industry, and a place for notes. I
figured out how to create some drop down lists for some of the categories,
but I have a couple of questions about what I am doing before I roll this out
to users. I'm starting with a access DB (MDB), but I do have SQL installed
and would prefer it be a Access Project (ADP) eventually.

One field we want to capture is notes by the counselor about their
meetings/discussion with the client. I created a notes field, and made it a
memo field, but I don't think that is an unlimited space to add information.
If I read the Access 2003 Bible, I think a memo field is limited to 65000
characters. Is there a way to make that an "unlimited" field?

I would like to create a switchboard interface for the users that has 3
options for now - 1) Add a record, 2) Edit a record, 3) search for a record.
Any advice on how to do this?

I have the Access 2003 Bible book, and other resources you could point me to
would be appreciated. I have done a little internet searching and need to do
more, but if you have advice, let me know.

Thanks for reading.

Mike

 

[Allen Browne]

Mike, it's fairly unusual to need more than 8 pages worth of information in
a single record in a relational database.

Is there any possibility you could break this into a separate related table,
so that one client can have many notes entered about them over the years? So
one employee can add a few pages about the client in one note today, and
someone else can add more pages in another note tomorrow. In this way, each
note is only 64k in size, but you can have a practically unlimited number of
notes about each client.

 

[Mike]

Allen,

That makes sense to me, and exactly what I need. If I understand correctly,
I would create a separate table, in the same DB, just for the notes? Then
link the two tables together?

Thanks for your help!

Mike

 

[Allen Browne]

That makes sense to me, and exactly what I need. If I understand
correctly,
I would create a separate table, in the same DB, just for the notes?
Then
link the two tables together?

Yes, that's it.

The new table would have fields like this:
NoteID AutoNumber primary key
ClientID Number relates to key of your client table
NoteDate Date/Time defaults to = Now()
ClientNote Memo content of this note.