Hello. I have an Access database which tracks if patients agree to participate in research. The numeric field STUDYSTATUS indicates each patient's response. For example, 101 is "written consent" and 102 is "verbal consent", 200 is "refused, no reason", 201 is "refused, too ill", etc. STUDYSTATUS can be grouped into larger categories: 100-199 is "consent", 200-299 is "refused", 300-399 is "lost", etc. There is also a date field, SSDATE, indicating the date the patient responded.
I need to create a report which shows:
- The cumulative STUDYSTATUS group count. (The total number of patients currently in each STUDYSTATUS group.)
- The monthly or weekly STUDYSTATUS group count. (The number of patients in each STUDYSTATUS group with an SSDATE falling within a specified time period. The user enters the desired date range into a form which provides the dates for the query).
If there are no patients in a STUDYSTATUS group I need the group to appear with a count of zero. I was able to write the necessary queries using the Switch function to group STUDYSTATUS, but STUDYSTATUS groups with a count of zero do not appear.
I need the report will look like this.
Study Status Group Monthly Cumulative
Consent 0 20
Refused 12 19
Lost 0 0
Ineligible 3 7
Thanks for all suggestions!