Research Study Mailing/Participation Summary Table - Multiple Fields Multiple Dates

[Kay]

I need to produce a summary report of participants in a research
study. We first send out an initial/introduction letter telling the
person about the study and that we will be calling them in two weeks
to see if they will participate. We then call them and they either
agree, refuse, or they are unable to be contacted (phone disconnected
maybe). If they agree to participate, we mail a packet with the
materials they need to fill out. Then will also need to note when the
packet of materials is returned completed. All of these things may be
happening in different months.

Fields that are included:
[ParticipationContactStatus] - a combo box - choices: Agreed, Refused,
Unable to be Contacted
[ParticipationContactStatusDate]- date field - update whenever change
Participation Status
[InitialLetterMailed] - a checkbox - check when letter is mailed
[InitialLetterMailedDate] - date field - update when letter is mailed
[PacketMailed] - a checkbox - check when packet of materials is mailed
[PacketMailedDate] - date field - update when packet of materials is
mailed
[PacketReceived] - a checkbox - check when packet of materials is
returned
[PacketRecdDate] - date field - update when packet of materials is
returned

My report summary chart needs to look like this:

A.
B. C.
D. E. F.
Initial Letter Agreed
to Refused to Unable to
Packet Packet
Mailed Participate
Participate Contact Mailed Received
1. Jan-07 10
4 1
0 3 0
2. Feb-07 20
17 5
2 15 10
3, Mar-07 15
16 1
0 10 5
....
Totals 45
37 7
2 28 15

I dont know how to word my question right. My reports now are coming
out with multiple columns of dates depending on the variables and I
don't want that. I need to know how to associate different date
fields with different status fields but for them all to be grouped by
month.

Examples to illustrate:
1. Sally's initial letter is mailed in Jan (A1 plus 1). We contact
her in Jan and she agrees to participate (B1 plus 1). Her packet is
mailed in Feb (E2 plus 1). She returns her packet in March (F3 plus
1).

2. Bob' initial letter is mailed in Jan (A1 plus 1). We reach him
in Feb and he agrees to participate (B2 plus 1). His packet is mailed
in Feb (E2 plus 1). His packet is received in Feb (F2 plus 1).


So if anyone is looking for something to do on this lovely Saturday,
heres your chance to help me think! Just FYI...I'm not that great at
language but will give it a shot if I need too. I've been trying to
do it with queries and the build expression box. Also, Im still in
the beginning phase so if any one thinks I should change how my form
is set up then thats fine.

 

[John Nurick]

Hi Kay,

I'm not certain, because I don't fully understand what you're trying to
achieve, but I think that the data structure you are using is making the
reporting much more complicated.

In a situation like this I'd be inclined to use at least three tables:

Persons
PersonID - an arbitrary key field such as an autonumber
FirstName
LastName
Address
Phone
other information about the person


EventTypes
EventType - primary key
EventName - e.g. "Initial letter sent", "Follow-up call",
etc.
maybe other fields such as Description


Events
PersonID - foreign key linking the Event to a Person
EventType - foreign key
(The primary key of Events consists of PersonID
and EventType)
EventDate
Outcome - e.g. "Agreed", "Refused", "Could not contact",
"Returned", etc.
maybe other fields such as Comment.

With a structure like this it's quite simple to use a crosstab query to
get the sort of report you want.

I need to produce a summary report of participants in a research
study. We first send out an initial/introduction letter telling the
person about the study and that we will be calling them in two weeks
to see if they will participate. We then call them and they either
agree, refuse, or they are unable to be contacted (phone disconnected
maybe). If they agree to participate, we mail a packet with the
materials they need to fill out. Then will also need to note when the
packet of materials is returned completed. All of these things may be
happening in different months.

Fields that are included:
[ParticipationContactStatus] - a combo box - choices: Agreed, Refused,
Unable to be Contacted
[ParticipationContactStatusDate]- date field - update whenever change
Participation Status
[InitialLetterMailed] - a checkbox - check when letter is mailed
[InitialLetterMailedDate] - date field - update when letter is mailed
[PacketMailed] - a checkbox - check when packet of materials is mailed
[PacketMailedDate] - date field - update when packet of materials is
mailed
[PacketReceived] - a checkbox - check when packet of materials is
returned
[PacketRecdDate] - date field - update when packet of materials is
returned

My report summary chart needs to look like this:

A.
B. C.
D. E. F.
Initial Letter Agreed
to Refused to Unable to
Packet Packet
Mailed Participate
Participate Contact Mailed Received
1. Jan-07 10
4 1
0 3 0
2. Feb-07 20
17 5
2 15 10
3, Mar-07 15
16 1
0 10 5
...
Totals 45
37 7
2 28 15

I dont know how to word my question right. My reports now are coming
out with multiple columns of dates depending on the variables and I
don't want that. I need to know how to associate different date
fields with different status fields but for them all to be grouped by
month.

Examples to illustrate:
1. Sally's initial letter is mailed in Jan (A1 plus 1). We contact
her in Jan and she agrees to participate (B1 plus 1). Her packet is
mailed in Feb (E2 plus 1). She returns her packet in March (F3 plus
1).

2. Bob' initial letter is mailed in Jan (A1 plus 1). We reach him
in Feb and he agrees to participate (B2 plus 1). His packet is mailed
in Feb (E2 plus 1). His packet is received in Feb (F2 plus 1).


So if anyone is looking for something to do on this lovely Saturday,
heres your chance to help me think! Just FYI...I'm not that great at
language but will give it a shot if I need too. I've been trying to
do it with queries and the build expression box. Also, Im still in
the beginning phase so if any one thinks I should change how my form
is set up then thats fine.

 

[Kay]

Ok...sounds good. I'm trying to think about how to implement this
though.

I'm confused on how the Event Name in the EventType field will
coincide with the EventOutcome in the Events Table. Can you explain
how this would work in a form?

Thank you,

Kerri

 

[John Nurick]

For data entry, you'd use a form bound to the Persons table, with a
continuous subform bound to the Events table (with the form and subform
linked on PersonID so the subform would only show the events associated
with the Person currently displayed on the main form.

The [Orders Subform] on the Orders form in the Northwind sample database
is set up this way.

In the subform you'd have a combobox bound to the EventType field. The
RowSource of the combobox would be a query on the EventTypes table, e.g.
SELECT EventType, EventName
FROM EventTypes
ORDER BY EventName
;

This gives you two columns in the combobox, the first with the EventType
and the second with the EventName. Set the Column Widths property of the
combobox to something like this:
0 in; 1.4 in
to hide the first column and just display the EventNames. The ProductID
combobox on the Northwind [Orders Subform] is set up this way except for
having 3 columns of which the first is hidden.

To get the "report" view you want, you'd use a quite different form (or
indeed a report), based on a crosstab query that assembles the data into
the columns you need.