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.

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.

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

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

Perfect! Absolutely brilliant. Thank you so much for your time - it's much appreciated.

Best regards,

Michael

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.

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.