Jump to content

Relationship based on a range of dates


Sam G

This topic is 1730 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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?

Link to comment
Share on other sites

Try defining the relationship as:

Coaches::First Date In ≤ Weeks::Date7
AND
Coaches::Last Date In ≥ Weeks::Date1

---
P.S. Unrelated to your current problem, but I would reconsider the described structure. It seems you should have a separate table of Bookings and let the Coaches table have a single unique entry for each coach. Also, a table of weeks is redundant (unless you have some specific data to record about each week). If your calendar shows 7 days at a time, I would use a table with 7 records and populate their dates dynamically according to the week's start date selected in a global field.

Link to comment
Share on other sites

  • Newbies

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.

 

Link to comment
Share on other sites

I am afraid I don't fully follow your description (you are describing your layout, but not the underlying structure). In any case, if you have two tables with a date range each, the relationship I described earlier will make any two records whose ranges overlap related.

Link to comment
Share on other sites

This topic is 1730 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.