Thanks for the reply. I was afraid when I typed this message last night that I had not explained myself very well. I was trying to keep it short. Sorry about that. I don't have a table for Week. The two tables I am trying to connect are Day Sheet (calendarish) and Coaches. I also have an Appointments table used to populate the Day Sheet. The Day Sheet has many different layouts depending on who is looking at it and for what purpose. There is a daily view. On this view the grid is 13 across and 13 down. At the top of each column is the name of one of my teachers. Each row represents an hour from 9am to 9pm. Each square of the grid would contain a students name that is booked at that hour with that teacher. I can look at that layout and see the entire day of bookings and know who is teaching whom at what times. That works great. I have a second layout that is a weekly view for each individual teacher to see their week at a glance. It is 7 across (days of the week) and 13 down (hours of the day) and is filtered by whatever teacher is looking at it. That works great also. We also have coaches that come in from time to time. They are not teachers and are usually only in 1 - 3 days at a time. So I have a separate view for them. So the coaches table contains the coaches name, some other info needed for bookkeeping purposes and the two date fields for when they are booked next. The two problems are this. I could have two coaches booked at the same time so on that calendar I would want a dropdown menu to choose which coaches appointments to look at. I don't want the menu to list every coach that is in the coaches table. I only want it to show who is booked in for the week showing on the layout at that time. Easy enough to do if the First Date In and Last Date In fall in the same week. Our week starts on Sat and ends on Friday. So if a coach is booked in on a Friday and Saturday, they would be listed in two separate weeks. So the solution of First Date In ≤ Date7 AND Last Date In Date1 will only work if for the 1st week of the split and not the second week of the split. It would need to be an OR statement and not and AND. And as far as I know that is not an option. It would be nice to have the option of the AND or OR when multiple criteria is need for the relationship. So anyway, if you are anyone else can think of another solution that would be great. Thanks again.
I am trying to create a value list that can be used in a dropdown menu on a calendar layout. I have a Coaches table. Each recored contains the name and info of a coach. In the record there are two fields for dates titled First Date In and Last Date In. These dates indicate when the coach is booked to be in the school next. The calendar layout is a weekly calendar showing 7 days at a time. You can scroll thought the weeks with two arrow buttons (left and right). There are calculation fields (date1, date2, date3...date7) that calculate the dates for each day of that week based on the week of the year. As you click through the weeks I want to be able to see which coaches are booked for that week. But what I really need to a drop down menu that would contain only the coaches that are book for that week and based on that selection, the calendar would filter and show only the appointments for that coach. I can make all that happen based on only 1 date (either the First Date In or the Last Date In) but I don't know how to do it based on either/or. Because here is the rub. If a coach is booked say on a Friday, Sat and Sunday, his First Date In (Friday) and Last Date (Sunday) in are in different weeks. So they will show up in the value list on the week for the Friday date but not the week of the Sat/Sun dates. I know you can add multiple criteria for the relationship but they are always joined with an AND and I need to to be an OR. Any suggestions?