Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    Why not sub-reports side by side?
    In reading through parts of this forum, I've seen sub-reports side by side.

  2. #17
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8
    Quote Originally Posted by JohnLouisWood View Post
    Why not sub-reports side by side?
    In reading through parts of this forum, I've seen sub-reports side by side.

    Thank you. I will definitely play with that and see what I can come up with

  3. #18
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You should really have stored the Case manager ID (Unique code) against the Roster rather than the case managers name.
    I can't see how you can do this in one query, I think it would have to be sub reports.

    A native cross tab will have gaps by the very nature of how they work:
    First Name Middle Name Last Name Chris Christy Greg Jana Joe
    Dolly
    Parton Dolly Parton
    James
    Bond James Bond
    John
    Smith John Smith
    Micky
    Mouse Micky Mouse
    Willie
    Nelson Willie Nelson

    The only other route I can think of would be a make table query followed by an update to add the client details to each Case manager field, but even that isn't straight forward as you have a variable number of clients so would need to add the maximum rows first then loop around it all to fill out the gaps.
    Messy.

    I would rethink your desired output.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #19
    vikgarden is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2022
    Posts
    8
    Quote Originally Posted by Minty View Post
    You should really have stored the Case manager ID (Unique code) against the Roster rather than the case managers name.
    I can't see how you can do this in one query, I think it would have to be sub reports.

    A native cross tab will have gaps by the very nature of how they work:
    First Name Middle Name Last Name Chris Christy Greg Jana Joe
    Dolly
    Parton Dolly Parton
    James
    Bond James Bond
    John
    Smith John Smith
    Micky
    Mouse Micky Mouse
    Willie
    Nelson Willie Nelson

    The only other route I can think of would be a make table query followed by an update to add the client details to each Case manager field, but even that isn't straight forward as you have a variable number of clients so would need to add the maximum rows first then loop around it all to fill out the gaps.
    Messy.

    I would rethink your desired output.

    Thank you so much!!! I was hoping for an easy solution. I'll play around with it some more.

  5. #20
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    To Micron: Why not sub-reports from individual queries?
    I have seen sub-reports side by side in other threads that I have read.
    Also probably want to make your report landscape instead of portrait

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMO, it's a hack for an improperly designed db. Subform/subreports are for displaying the many side of one to many relationships, not for fixing design issues. OP seems intent on following your advice, which is fine by me. Gain/lose a case manager and then what? Modify the design to suit? Never a great idea.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #22
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    To Vikgarden: From my first actual doing just a couple of minutes ago, create your Master/Blank Report, and then drag the sub-report(s) onto the detail part of the report.

  8. #23
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    I would also 'recommend' a 2nd report - 14 inch paper to also include sub-reports of (a) those with a discharge date, and (b) unassigned clients. I would imagine this 2nd report would be for your personal use. Make sure to include totals, for each case manager, and the total of active clients. Does your total from the case managers equal the total of active clients???
    What do you know about Joe Smith in room 23? He has been here for 7 years without a case manager. Whoops.
    Your 2nd report should be a good check that everything is okay - that things add up, and the few times that something tries to slip thru the cracks, you are the GENIUS that discovers and corrects the problem/error.

Page 2 of 2 FirstFirst 12
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