I'd add one more table to those suggested by Spinks, An ActivitiesSchedule
table with columns ActivityID (referencing the primary key of Activities) and
ActivityDate. Together these form the composite primary key of the table.
The ActivityID and ActivityDate columns in ResidentActivities therefore
become a composite foreign key referencing the primary key of
ActivitiesSchedule.
The addition of the ActivitiesSchedule table means that activities can be
scheduled prior to assigning any residents to each activity. It also helps
ensure the integrity of the data in that, by enforcing referential integrity
between ActivitiesSchedule and ResidentActivities
You might also consider excluding the Attended column from the
ResidentActivities table so that this simply records those activities
attended by a resident rather than those to which a resident is assigned;
there are pros and cons of both approaches, and which you opt for will depend
on your modus operandi.
As far as your required tasks are concerned, to list every activity is simply:
SELECT ActivityName, ActivityDate
FROM Activitiies INNER JOIN ActivitiesSchedule
ON Activitiies.ActivityID = ActivitiesSchedule.ActivityID
ORDER BY ActivityDate;
You'll note that the addition of the ActivitiesSchedule table makes this
possible even if no residents have yet been assigned to an activity.
To list residents per Unit:
SELECT FName & " " & LName AS FullName,
UnitName
FROM Residents,Units
WHERE Residents.UnitID = Units.UnitID
ORDER BY UnitName, LName, FName;
You can base a report on this query if you wish and group it by UnitName,
but if you do so omit the ORDER BY clause from the query and use the report's
internal sorting and grouping mechanism.
To track who attended each activity, if you exclude the Attended column from
the ResidentActivities table:
SELECT AcitivityName, ActivitiesSchedule.ActivityDate
FName & " " & LName AS FullName
FROM Activities, ActivitiesSchedule, ResidentActivities, Residents
WHERE Activities.ActivityID = ActivitiesSchedule.ActivityID
AND ActivitiesSchedule.ActivityID = ResidentActivities.ActivityID
AND ActivitiesSchedule.ActivityDate = ResidentActivities.ActivityDate
AND ResidentActivities.ResidentID = Residents.ResidentID;
If you've retained the Attended column then extend the WHERE clause to
restrict the result set to those attended:
SELECT AcitivityName, ActivitiesSchedule.ActivityDate
FName & " " & LName AS FullName
FROM Activities, ActivitiesSchedule, ResidentActivities, Residents
WHERE Activities.ActivityID = ActivitiesSchedule.ActivityID
AND ActivitiesSchedule.ActivityID = ResidentActivities.ActivityID
AND ActivitiesSchedule.ActivityDate = ResidentActivities.ActivityDate
AND ResidentActivities.ResidentID = Residents.ResidentID
AND Attended = TRUE;
If you wish to group the report by Unit add the Units table to the query:
SELECT AcitivityName, ActivitiesSchedule.ActivityDate
FName & " " & LName AS FullName, UnitName
FROM Activities, ActivitiesSchedule, ResidentActivities, Residents, Units
WHERE Activities.ActivityID = ActivitiesSchedule.ActivityID
AND ActivitiesSchedule.ActivityID = ResidentActivities.ActivityID
AND ActivitiesSchedule.ActivityDate = ResidentActivities.ActivityDate
AND ResidentActivities.ResidentID = Residents.ResidentID,
AND Residents.UnitID = Units.UnitID
AND Attended = TRUE;
You can of course design the query through the visual interface rather than
writing it in SQL, in which case you'd probably JOIN the tables rather than
using join criteria in the WHERE clause as I've done above. The only
criterion you'd need to enter is the TRUE for the Attended column if you've
retained that column.
Group the report first by unit if you want a break down per unit and then by
resident, and order by AcivityDate, ascending or descending as preferred.
Ordering is doen by adding a group level without a header or footer. Put the
unit name in the unit group header if you are grouping by unit, and the
resident's name in the resident group header. Put the activity name and date
in the detail section. In the resident group footer add an unbound text box
with a ControlSource of:
=Count(*)
to count the activities attended per resident.
You'll most likely want to restrict the report to a specified date range so
you can include start and end date parameters in the query so that you'll be
prompted for these. You can add these in query design view if you wish, but
the SQL would look something like this:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT AcitivityName, ActivitiesSchedule.ActivityDate
FName & " " & LName AS FullName, UnitName
FROM Activities, ActivitiesSchedule, ResidentActivities, Residents, Units
WHERE Activities.ActivityID = ActivitiesSchedule.ActivityID
AND ActivitiesSchedule.ActivityID = ResidentActivities.ActivityID
AND ActivitiesSchedule.ActivityDate = ResidentActivities.ActivityDate
AND ResidentActivities.ResidentID = Residents.ResidentID,
AND Residents.UnitID = Units.UnitID
AND Attended = TRUE
AND ActivityDate BETWEEN [Enter start date:]
AND [Enter end date:];
Note that the parameters are declared. This is prudent with date/time data
types as otherwise a date entered as a parameter value in short date format
might be interpreted as an arithmetical expression and give the wrong
results. The parameters can also be entered as criteria and declared in
query design view rather than SQL.
Finally, relying on the implementation of Boolean (Yes/No) values as -1 and
0 is not recommended. You should always use the Boolean values of TRUE or
FALSE. You can do this to conditionally sum a set of Boolean values to give
a count of the TRUE (or FALSE) values by using the IIF function to return a 1
or 0, e.g. SUM(IIF(Attended],1.0)) will in effect count the TRUE values
regardless of the implementation.
Ken Sheridan
Stafford, England
Dar said:
Thanks for the clear answer. I will set up as suggested and let you know how
it goes.
:
Dar,
Sounds like you need four tables. You do not need separate tables for
Activities--it is merely a table to store the codes for each activity.
Units
---------------
UnitID AutoNumber (Primary Key)
UnitName Text
...any other unit-specific fields
Residents
----------------
ResidentID AutoNumber (PK)
Unit ID Integer (Foreign Key to Units)
FName Text
LName Text
...other resident-specific fields
Activities
------------------
ActivityID AutoNumber (PK)
ActivityName Text
ResidentActivities
------------------------
RAID AutoNumber (PK)
ResidentID Integer (FK to Residents)
ActivityDate Date/Time
ActivityID Integer (FK to Activities)
Attended Yes/No
Base your report(s) on a query that includes the UnitID so that you can
group the report by unit. Group also on the resident.
Since True = -1, the total number of activities attended by the resident
requires summing the absolute value of the Attended field:
=Sum(Abs([Attended]))
Hope that helps.
Sprinks
:
I work for nursing home. Every day of month they schedule an activity for
the residents. Sometimes two on same day. There are 5 Units involved with
approx. 35-40 residents per Unit. I need to create an application to
1. List every activity
2. List residents on Unit
3. Track who attends which activity (final report will show name, activity,
date and total activities attended per resident.)
Right now I have tables for each unit listing activities; I have a master
table listing all residents with unique identifier.
I'm not sure of the best way to proceed from here.
Help would be appreciated.