New database

[Guest]

My weakest point in Access is the start up.
I have 4 Floors with 30 patients per floor.
Each month we offer 3-4 activities per day.
We want to keep track who goes to what and eventually run report with totals.

I have a table with all the patients' names
What is the best way to list the activities and the relationships so I don't
have to list each activity over and over and over again for the patients?

Please be specific, and thanks.

 

[Guest]

You need three tables minimum.

The first one you already have with the Patients. Now this is very
important: Do you have a primary key assigned to that table? Could be
something like the PatientID. You need one even if it's just an autonumber
field.

Next you need a table of Activities. This table also needs a primary key.

Then you need a linking or joining table named something like
ActivitiesAttended. This table would have it's own primary key field
(autonumber is good enough); a PatientID field which is the foriegn key to
the Patient table; an ActivityID field which is the foriegn key field to the
primary key in the Activities table, and a Date field named something like
Attended for when they went to the activity. The combination of PatientID,
ActivityID, and Attended fields should probably be a unique index to keep you
from accidentally entering the same record twice.

Then you create a form based on the Activities table where you put in the
PatientID, ActivityID, and the date Attended. You could even use combo boxes
from the Patient and Activity tables to show you things like Mrs Brown is
PatientID 55.

Come to think about it, our own Roger Calrson has a sample database that
shows how this is done.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ImplementingM2MRelationship.mdb

 

[Guest]

I will give this all a try, and thank you so much for being explicit.