Table Setup Suggestions

[Marella]

I would love all of your comments/suggestions for the following scenario.

You have several clients that will have several different episodes. For each
episode, you will be collecting various information about the client. The
episodes are identified by the referral date. Each day until the episode is
closed you will have various general notes to add about this client. You need
the notes to be date specific, because you will be reporting on clients at
the end of the day that had notes entered for that date.

Example:
Client - John Doe
Referral Date - 01/01/07
Notes - 01/01/07-Client referred
- 01/02/07-Requested additional information to be faxed
- 01/03/07-Follow Up call needed.
- 01/04/07-Client Admitted

Client - John Doe
Referral Date - 02/01/07
Notes - 02/01/07-Client Referred

I hope you get the picture.

How would you set up tables for this and relationships?

 

[Smartin]

I would love all of your comments/suggestions for the following scenario.

You have several clients that will have several different episodes. For each
episode, you will be collecting various information about the client. The
episodes are identified by the referral date. Each day until the episode is
closed you will have various general notes to add about this client. You need
the notes to be date specific, because you will be reporting on clients at
the end of the day that had notes entered for that date.

Example:
Client - John Doe
Referral Date - 01/01/07
Notes - 01/01/07-Client referred
- 01/02/07-Requested additional information to be faxed
- 01/03/07-Follow Up call needed.
- 01/04/07-Client Admitted

Client - John Doe
Referral Date - 02/01/07
Notes - 02/01/07-Client Referred

I hope you get the picture.

How would you set up tables for this and relationships?

Hmm, this seems familiar for some reason.

So, clients have episodes, and episodes have notes.

That should give you some ideas about the tables and their
relationships. If entity "A" has [zero or many] of entity "B", what do
you suppose the relationship is?

As to what guzinta tables, think: client is defined by client name,
episode is defined by referral date and client, note is defined by note
date and episode.

Helps?

 

[KARL DEWEY]

How about this ---

Client –--
ClientID – autonumber – primary key
LName – text
FName - text
Referral – Datetime
Close – Datetime
Remarks – memo

Notes ---
NoteID – autonumber – primary key
ClientID – number - integer – foreign key
NoteDate – Datetime
Note – text
Remarks – memo

NoteList ---
ListID – autonumber primary key
Note – text

Set a one-to-many relationship from Client to Notes on ClientID.

Use NoteList as a pick list in your entry form.

The data you provided shows the same client needing help two times. The
first closed out on 01/04/07. You need to set criteria for Close date null
for active clients.

 

[Marella]

Thank you! That is perfect! I am a little unsure of what you mean for the
NoteList though.

Thanks again,
Marella