michaeldaly Posted January 18, 2011 Share Posted January 18, 2011 My staff work to appointments and I am creating a database for these appointments. I have created a value list which contains the names of the staff members who are working during the hours of any given appointment but I would really like to be able to create a value list which narrows it down to those who are actually available - that is to say, those who are working and not already occupied in another appointment. I think I can get this result by writing a script, but is there a more straightforward way by creating a new relationship? I've been struggling with this for months on end and would be eternally grateful for any input! Thank you in advance,Michael PS Sorry - I tried to attach my database but I'm apparently not permitted to upload that kind of file. I have attached a screenshot of the current relationship in the hope that it helps make things clearer. Link to comment Share on other sites More sharing options...
comment Posted January 18, 2011 Share Posted January 18, 2011 Please explains what tables you have and what are the relationships between them. I am also puzzled by the 'day' field in the Staff table. What exactly goes into this field? And what are shift_a and shift_b, time_a and time_b? --- BTW, you can attach your file if you zip it first. 1 Link to comment Share on other sites More sharing options...
michaeldaly Posted January 18, 2011 Author Share Posted January 18, 2011 Please explains what tables you have and what are the relationships between them. I am also puzzled by the 'day' field in the Staff table. What exactly goes into this field? And what are shift_a and shift_b, time_a and time_b? --- BTW, you can attach your file if you zip it first. Ahh, many apologies - I've now attached a zip file. There are two tables - "staff" and "appointments". The staff table contains the hours (or shift) worked by each person on any given day. In the appointments table, the time_a and time_b are the start and finish times of the appointments. Thank you for looking, Michael trial.fp7.zip Link to comment Share on other sites More sharing options...
comment Posted January 18, 2011 Share Posted January 18, 2011 So your "staff" table is actually a StaffAvailability table - and you are missing a Staff table (where each staff member has one unique record). See if this helps: AvailableStaff.zip 1 Link to comment Share on other sites More sharing options...
michaeldaly Posted January 19, 2011 Author Share Posted January 19, 2011 Perfect! Absolutely brilliant. Thank you so much for your time - it's much appreciated. Best regards, Michael Link to comment Share on other sites More sharing options...
comment Posted January 19, 2011 Share Posted January 19, 2011 P.S. As with any relationship that depends on the results of another relationship, you might expect refresh issues. I would add a Refresh Window [Flush cached join results] script, triggered upon entering the field. 1 Link to comment Share on other sites More sharing options...
michaeldaly Posted January 19, 2011 Author Share Posted January 19, 2011 P.S. As with any relationship that depends on the results of another relationship, you might expect refresh issues. I would add a Refresh Window [Flush cached join results] script, triggered upon entering the field. Once again, many thanks indeed. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4838 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now