A limitation of a reported list is that one record = 1 row, so that one shared consultation can't appear 3 times in 3 different sections.
You could use the virtual list method to create this report, or you could create 3 separate records for a consultation involving 3 consultants. Either way, you could calculate and total a "split time" which is the session time of the consultation divided by the number of consultants.
Hmmm...I'm not familiar with the virtual list method. I'll look that up. Thanks!
What if I were to create an SessionID field? Give those who are all part of the same session, the same SessionID? So, using the example, the three people would all have a SessionID of 1. Then could there be a way to tell the database that if records have the same SessionID, add up the session lengths (in this case 180) and divide by the number of sessions with the same SessionIDs (3)?
(Did any of that make sense? My coffee's starting to wear off...)
3 * 60 + 30 = 3.5 hours, not 3.25, something with your calc is wrong, to start with.
Another question: Person comes in the morning, is seen by 2 consultants, is ordered some exams, comes back in the afternoon and is seen by 2 consultants, one of which has already seen her in the morning. How do you handle that, at the input level ?
Oops sorry about that. Changed the times but forgot to change the total.
As for what you're saying, the way things are currently set up, it would be put in as 4 records on our Consultations layout which looks like:
(automatically generated record ID) Date Participant Name Consultant Name Length of Session 1 2/11/2016 John Smith Consultant 1 30 2 2/11/2016 John Smith Consultant 2 30 3 2/11/2016 John Smith Consultant 1 30 4 2/11/2016 John Smith Consultant 3 30
This information is later portaled in two other layouts, one for the participant and one for the consultant. Which look something similar to:
(automatically generated record ID) Date Consultant Name Length of Session 1 2/11/2016 Consultant 1 30 2 2/11/2016 Consultant 2 30 3 2/11/2016 Consultant 1 30 4 2/11/2016 Consultant 3 30
TOTAL SESSION TIME: 2 HOURS (should be 1 hour)
(automatically generated record ID) Date Participant Name Length of Session 1 2/11/2016 John Smith 30 2 2/11/2016 John Smith 30
TOTAL SESSION TIME: 1 HOUR
The problem I have in my original post is kind of the opposite of this (1 consultant, 2 participants), but the same problem happens just in a different portal. See how John Smith's total session time is off an hour? I thought maybe including a SessionID number for those with more than 1 consultant/participant...
Hi! I'm not sure why 2 hrs should be 1 hr in portal "Participant". The total of the portal rows is 120 (30+30+30+30). Are you trying to filter by Consultant 1 at the same time?
I'd suggest rethinking your data model. I recommend having a table called "Sessions" where you'd store the day, time, location, and purpose (and possibly outcome or recommendations) and another called "People" where you'd have people's names, contact info, etc. Then, in between the 2, you'd have "SPLinx", a join table (also called a link, merge, or bridge table) that would contain a record for every combination of session and person that actually occurred in your practice, along with a "Role" field that would identify why that particular person attended that particular session. For example, a series of "SPLinx" Records for the same session might look like this:
SPLSeq = 12345, SesSeq = 789, PerSeq =5678, Role = Participant
SPLSeq = 12346, SesSeq = 789, PerSeq =1005, Role = Therapist
SPLSeq = 12347, SesSeq = 789, PerSeq =2016, Role = Consultant
Session #789 would be for 30 minutes, and you'd only have to enter that datum once, in the "Sessions" table, but it would be visible in the "SPLinx" table because it would be linked to "Sessions" by means of the shared code number 789. Similarly, you'd have all your info on Participant John Smith under PerSeq 5678 in the "People" table and everything for your staff member Jessica Patterson under PerSeq 1005 in that same table, and they too would be visible to the "SPLinx" table by means of that shared code. And, of course, everything from "SPLinx" would be visible in the other direction as well.
Richard S. Russell wrote:
I'd suggest rethinking your data model. I recommend having a table called "Sessions" where you'd store the day, time, location, and purpose (and possibly outcome or recommendations)
and the Session (or Consultation, as OP calls it) total time. Completely agree with all the rest.
Yes! I see what you mean. I'll go ahead and try that out. Thank you all so much for the help!