Best way?

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.

Based on your description, it sounds like you have:

Residents
Activities
Units
and
Residents-on-Units
and
Residents-Signed-Up-For_Activities.

In a relational database (e.g., Access), you'd use 5 tables to store the
data specific to each of these "entities" (things about which you want to
keep data).

Or have I misunderstood your situation?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"Dar" wrote in message
...
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.

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



"Dar" wrote:

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.

Thanks for the clear answer. I will set up as suggested and let you know how
it goes.

"Sprinks" wrote:

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



"Dar" wrote:

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.

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" wrote:

Thanks for the clear answer. I will set up as suggested and let you know how
it goes.

"Sprinks" wrote:

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



"Dar" wrote:

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.

Ken: After I took a deep breath from reading what you just said, I will go
back to my database and try to implement what you wrote. It may take me a
couple of read-throughs to understand, but I'm willing to give it a go.

I've convinced the dept. to give me their original paperwork listing the
upcoming activities for month. Also, they have individual 8 1/2 x 11 sheets
which they use to track whether resident attended. I hope to get those also.


Bottom line is I am trying to set this up so they will do the data entry
using a user-friendly form.

Now, I need to read over what you wrote and go from there.
"Ken Sheridan" wrote:

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" wrote:

Thanks for the clear answer. I will set up as suggested and let you know how
it goes.

"Sprinks" wrote:

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



"Dar" wrote:

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.

The forms should be no problem. The key is to get the logical model (i.e.
the way the reality is modelled by a set of tables, each representing an
entity type with their columns representing the attributes of each entity
type) right at the start. Get that right and the interface will fall in to
place easily.

We'll be glad to help further as you move ahead of course.

Ken Sheridan
Stafford, England

"Dar" wrote:


Ken: After I took a deep breath from reading what you just said, I will go
back to my database and try to implement what you wrote. It may take me a
couple of read-throughs to understand, but I'm willing to give it a go.

I've convinced the dept. to give me their original paperwork listing the
upcoming activities for month. Also, they have individual 8 1/2 x 11 sheets
which they use to track whether resident attended. I hope to get those also.


Bottom line is I am trying to set this up so they will do the data entry
using a user-friendly form.

Now, I need to read over what you wrote and go from there.
"Ken Sheridan" wrote:

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" wrote:

Thanks for the clear answer. I will set up as suggested and let you know how
it goes.

"Sprinks" wrote:

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



"Dar" wrote:

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.

The more I try, the more confused I get. I'm trying to import info I already
have entered and am getting all messed up. Would it be possible to send you
what I have and have you take a looksee then re-tell me what I need to do.


"Ken Sheridan" wrote:

The forms should be no problem. The key is to get the logical model (i.e.
the way the reality is modelled by a set of tables, each representing an
entity type with their columns representing the attributes of each entity
type) right at the start. Get that right and the interface will fall in to
place easily.

We'll be glad to help further as you move ahead of course.

Ken Sheridan
Stafford, England

"Dar" wrote:


Ken: After I took a deep breath from reading what you just said, I will go
back to my database and try to implement what you wrote. It may take me a
couple of read-throughs to understand, but I'm willing to give it a go.

I've convinced the dept. to give me their original paperwork listing the
upcoming activities for month. Also, they have individual 8 1/2 x 11 sheets
which they use to track whether resident attended. I hope to get those also.


Bottom line is I am trying to set this up so they will do the data entry
using a user-friendly form.

Now, I need to read over what you wrote and go from there.
"Ken Sheridan" wrote:

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" wrote:

Thanks for the clear answer. I will set up as suggested and let you know how
it goes.

"Sprinks" wrote:

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



"Dar" wrote:

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.

By all means, though I can't promise to spend a lot of time on it. I'll
certainly take a look, however. Mail me the file (Access 2003 or earlier
please) at:

kenatksheridandotorangehomedotcodotuk

Ken Sheridan
Stafford, England

"Dar" wrote:

The more I try, the more confused I get. I'm trying to import info I already
have entered and am getting all messed up. Would it be possible to send you
what I have and have you take a looksee then re-tell me what I need to do.


"Ken Sheridan" wrote:

The forms should be no problem. The key is to get the logical model (i.e.
the way the reality is modelled by a set of tables, each representing an
entity type with their columns representing the attributes of each entity
type) right at the start. Get that right and the interface will fall in to
place easily.

We'll be glad to help further as you move ahead of course.

Ken Sheridan
Stafford, England

"Dar" wrote:


Ken: After I took a deep breath from reading what you just said, I will go
back to my database and try to implement what you wrote. It may take me a
couple of read-throughs to understand, but I'm willing to give it a go.

I've convinced the dept. to give me their original paperwork listing the
upcoming activities for month. Also, they have individual 8 1/2 x 11 sheets
which they use to track whether resident attended. I hope to get those also.


Bottom line is I am trying to set this up so they will do the data entry
using a user-friendly form.

Now, I need to read over what you wrote and go from there.
"Ken Sheridan" wrote:

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" wrote:

Thanks for the clear answer. I will set up as suggested and let you know how
it goes.

"Sprinks" wrote:

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



"Dar" wrote:

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.

Ken, for some reason, Yahoo said you didn't have an Yahoo address. Is there
someway I can attach a database in this venue? The address of orangehome
should have accepted the unzipped mdb I think?

"Ken Sheridan" wrote:

By all means, though I can't promise to spend a lot of time on it. I'll
certainly take a look, however. Mail me the file (Access 2003 or earlier
please) at:

kenatksheridandotorangehomedotcodotuk

Ken Sheridan
Stafford, England

"Dar" wrote:

The more I try, the more confused I get. I'm trying to import info I already
have entered and am getting all messed up. Would it be possible to send you
what I have and have you take a looksee then re-tell me what I need to do.


"Ken Sheridan" wrote:

The forms should be no problem. The key is to get the logical model (i.e.
the way the reality is modelled by a set of tables, each representing an
entity type with their columns representing the attributes of each entity
type) right at the start. Get that right and the interface will fall in to
place easily.

We'll be glad to help further as you move ahead of course.

Ken Sheridan
Stafford, England

"Dar" wrote:


Ken: After I took a deep breath from reading what you just said, I will go
back to my database and try to implement what you wrote. It may take me a
couple of read-throughs to understand, but I'm willing to give it a go.

I've convinced the dept. to give me their original paperwork listing the
upcoming activities for month. Also, they have individual 8 1/2 x 11 sheets
which they use to track whether resident attended. I hope to get those also.


Bottom line is I am trying to set this up so they will do the data entry
using a user-friendly form.

Now, I need to read over what you wrote and go from there.
"Ken Sheridan" wrote:

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" wrote:

Thanks for the clear answer. I will set up as suggested and let you know how
it goes.

"Sprinks" wrote:

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



"Dar" wrote:

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.