Why not sub-reports side by side?
In reading through parts of this forum, I've seen sub-reports side by side.
Why not sub-reports side by side?
In reading through parts of this forum, I've seen sub-reports side by side.
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 ↓↓
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.
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
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.
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.
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.