Creating a table and relationship for counseling notes question

[Mike]

Hi,
I'm trying to figure out how to do something. I'm creating a database to
track our counselor's activites with clients (I work for a non-profit
organization). I'm creating one table with Client information (ID field -
primary key, Name, address, counselor assigned, reason for meeting) and one
table for a Client ID field, date/time field, and Counselor notes field(memo
field). And I have created a relationship between them with the ID field.
Does that sound right?

Here's where I'm stumped. What I would like is to create a unique record
for each client, and when the counselor meets with the client that they would
be able to enter the reason for the meeting, time spent field, the date, and
a set of notes pertaining to that particular meeting. And then what you
would see for each client, is each unique meeting in the database for each
client.

What I have now, allows the counselor to append notes in the counseling
notes memo field, and that will work, but if I can figure out how to do the
above, I think it would look better and be more easy to find information for
the counselor.

Any advice for me? I'm working through the Access 2003 Bible book, but
haven't found out how to do this particular item.

Thanks,

Mike

 

[John W. Vinson]

Hi,
I'm trying to figure out how to do something. I'm creating a database to
track our counselor's activites with clients (I work for a non-profit
organization). I'm creating one table with Client information (ID field -
primary key, Name, address, counselor assigned, reason for meeting) and one
table for a Client ID field, date/time field, and Counselor notes field(memo
field). And I have created a relationship between them with the ID field.
Does that sound right?

Don't use the reserved word Name as a fieldname; and break up the client's
name into FirstName, LastName, perhaps middlename, title and suffix (Dr. Homer
L. Simpson Jr. frex).

If a client might see more than one counselor over time, you probably should
also have a table of Counselors (ID, lastname, firstname, contact information
perhaps), and store the counselorID in the notes table, rather than a
counselor assigned text field in the client information. Reason for meeting is
iffy too - that suggests that there would only be one meeting!

Here's where I'm stumped. What I would like is to create a unique record
for each client, and when the counselor meets with the client that they would
be able to enter the reason for the meeting, time spent field, the date, and
a set of notes pertaining to that particular meeting. And then what you
would see for each client, is each unique meeting in the database for each
client.

I'd use several tables:

Clients
ClientID
LastName
FirstName
MiddleName
Title
Suffix
<other biographical or contact information as appropriate>

Counselors
CounselorID
LastName
FirstName
MiddleName
Title
Suffix
<other biographical information as appropriate>

Or you might want to have a single People table related one-to-one to a
Clients table, with PersonID as a primary key and other fields pertaining to
the person *as a client*, and similarly a Counselors table with just fields
pertaining to that person's activity as a counselor (date started,
specialties, etc.)

Meetings
MeetingID
MeetingDateTime
Duration <integer minutes, NOT a date/time>
CounselorID
ClientID
MeetingReason

Notes
MeetingID <at which meeting were these notes generated>
Note <Memo>

What I have now, allows the counselor to append notes in the counseling
notes memo field, and that will work, but if I can figure out how to do the
above, I think it would look better and be more easy to find information for
the counselor.

DON'T append multiple notes (and dates and other info!!) into a single memo
field. Use a multirecord Notes table instead.


John W. Vinson [MVP]

 

[Mike]

John,

This makes sense. I will try it.

Thanks so much.

Mike