Results 1 to 3 of 3
  1. #1
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38

    group fields, count totals, and display group name and value of 0 if there are no records

    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:


    1. The cumulative STUDYSTATUS group count. (The total number of patients currently in each STUDYSTATUS group.)
    2. 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!

  2. #2
    clindner.iv is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    8
    Hi

    You need to format each field in the Query like this:
    Example:

    Consent: Nz([fieldName],0)

    the Nz will evaluate if there is a null value in the field you specify. The 0 will replace the null value(if one is found) with a 0. You can put anything there 0 or 1 or 2 or ....

  3. #3
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    Thanks. I was hoping to use the Switch function or something similar so I could have one field displaying each study status group rather than a field for each groupings (consent, refuse, etc.), but this works just fine. Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Display a group count in the page header.
    By khughes46 in forum Reports
    Replies: 5
    Last Post: 10-03-2014, 07:34 AM
  2. GROUP by yet SELECT fields not within GROUP BY
    By johnseito in forum Access
    Replies: 25
    Last Post: 11-03-2013, 10:20 PM
  3. Replies: 4
    Last Post: 07-24-2013, 02:34 PM
  4. Display and print group records in one page only?
    By wittybrent in forum Reports
    Replies: 1
    Last Post: 05-11-2013, 09:25 AM
  5. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums