Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8

    Need a report on one page for case managers with list of clients.



    Mikaila Iris Gary Katie Amie Heather Audrey

    So, there are 100 clients. Each assigned one of the top case managers. I know how to group, but how do I get it to show up like this? Case managers acrosss, with whoever is assigned to them below them. In a table is ideal, but I don't care about lines, just that it shows up like this. Whenever I try to create this, it has case manager with list of names below, then next case manager below that with list of clients and so on. It takes up 4 pages like that. I want it side by side on one page. (I can do the formatting to get it on one page, I just need to know how to get it looking like this.

    I have actually attached what I want the report to look like (made up names and then numbers because I couldn't think of any more names, LOL), including a "count-if" on the bottom that I would like for the report to do.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,547
    Hi Can you upload your database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8
    Quote Originally Posted by mike60smart View Post
    Hi Can you upload your database?

    I am not able to do that. I work in Healthcare and it is all patient information.

    I have a main Roster table where all of the information is located and a qry for just current patients (which excludes those with a discharge date)

    I have a couple of reports for caseloads. One starts a new page for each case manager to give a list of each of their caseloads. And another I think I did on a label type template which is all on one page, just alpa by client in three rows. I know, it is difficult to envision without seeing.

    But the attachment is what is really all that is needed. It will be based off of a simple query, pulling just the last name, first name and casemanager. I just need to group it like in the attachment.

    I created this database over 15 years ago and am in it every day. I have made lots of tweaks and new reports and stuff over the years. One of my other programs wants a report in this format, if at all possible.

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,547
    Hi
    Can you upload a zipped copy of the database with no data?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #5
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8
    Quote Originally Posted by mike60smart View Post
    Hi
    Can you upload a zipped copy of the database with no data?
    Here it is. Hope it works. I deleted over 300 entries. I kept 5 and changed names and socials/DOB etc and removed all personal information.

    On the switchboard, I have a caseload report and a case manager list. I want similar to the caseload report but in columns on one page.

    I am just leaving the office and will be back in on Monday morning. So, I won't be able to get right back to you until then.

    Thank you so much for looking at this for me. The database is pretty involved, LOL.
    Attached Files Attached Files

  6. #6
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8
    Oh, and it would be based off of qrycurrent.

    Quote Originally Posted by vikgarden View Post
    Here it is. Hope it works. I deleted over 300 entries. I kept 5 and changed names and socials/DOB etc and removed all personal information.

    On the switchboard, I have a caseload report and a case manager list. I want similar to the caseload report but in columns on one page.

    I am just leaving the office and will be back in on Monday morning. So, I won't be able to get right back to you until then.

    Thank you so much for looking at this for me. The database is pretty involved, LOL.

  7. #7
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    What happens if you make 5 sub-reports - 1 for each case manager and their clients. And 'attach' each sub-report side by side to a report, either in the detail section, or the footer section.
    'Obviously' each sub-report would have limited width, like 1.5 inches

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,547
    Well Hello Excel

    You have created 1 table to record a process.

    You need a table for Case Managers

    Each Case Manager will be responsible for a Number of Clients.

    So you would have a Main Form based on Case Managers with a Subform based on CaseManagerClients

    All of your table need an Autonumber PK LongInteger.

    All of your tables are linked on fields that are ShortText Data Types.

    You should not have spaces in Field names and you should not use Characters in field names
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    I'm sorry, but the most recent post/reply does not seem to address the original problem - how to create a single page report with 5 columns, headed by the case manager, and then showing all of their clients.

  10. #10
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8
    Quote Originally Posted by JohnLouisWood View Post
    What happens if you make 5 sub-reports - 1 for each case manager and their clients. And 'attach' each sub-report side by side to a report, either in the detail section, or the footer section.
    'Obviously' each sub-report would have limited width, like 1.5 inches

    I will see if I can figure out how to do that. I don't think I have ever done Sub-reports. I do have one report that is one page per case manager with a list of their clients.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,651
    Chances are that if you only have one table (I didn't look) you're always going to have issues of one sort or another. Having 5 subreports is not the way to go (not sure how you arrived at 5 when you show 7 case managers in the pdf). Perhaps a crosstab query would work for you, but you'd have to define the fields in the query property sheet, otherwise it would raise an error when you have no records for any one manager.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,547
    Hi

    If you do not address normalising the database then you are wasting your time.

    You also have Repeating Groups in your Roster table.

    AXIS fields should be records in a related table
    Attached Thumbnails Attached Thumbnails Repeating Group.png  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    I think you will first need to make 5 queries - each where you include the linked fields of (a) case manager and (b) client's last name, or since some clients have the same last name, include (c) first name and (d) name_w_fi which is a calculated field --> [Client_Last_Name] & ", " & Left([Client_First_Name],1)
    Each query needs to be criteried with the individual case manager. i.e. make the first query, make sure it works --> then copy it 4 times, and change the case manager name in each of your new queries.
    Then make a (sub)report for each query.
    Create a 'Blank' Master Report, and then insert each of the sub-reports next to each other.
    I myself are just starting to make sub-reports, so I'm not sure of all the details/intricacies.

  14. #14
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    Agreed the number should be 7 not 5.

  15. #15
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8
    Quote Originally Posted by Micron View Post
    Chances are that if you only have one table (I didn't look) you're always going to have issues of one sort or another. Having 5 subreports is not the way to go (not sure how you arrived at 5 when you show 7 case managers in the pdf). Perhaps a crosstab query would work for you, but you'd have to define the fields in the query property sheet, otherwise it would raise an error when you have no records for any one manager.
    Someone said 5 because the "practice" database has five. Basically, if you look at the database, on the Form that opens, there is a button that says "case load report". I would like to create a report that looks like the excel version I created. So, instead of each staff person having their own page, it is listed all on one page like the excel version I attached.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-29-2018, 11:25 AM
  2. Many to Many, many services with many managers
    By Haz in forum Database Design
    Replies: 3
    Last Post: 11-04-2017, 04:56 PM
  3. Replies: 2
    Last Post: 02-04-2016, 08:13 AM
  4. Query list of clients in date range
    By scorpiogray in forum Queries
    Replies: 2
    Last Post: 05-20-2013, 01:34 PM
  5. Replies: 9
    Last Post: 02-12-2013, 03:14 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