Relating a Time to a Range of Time

[Brett W]

Hey everyone!

Had a question I was hoping someone could help me with. I have a database that holds person info and their appointment dates & times. I am trying to create a relation that will allow me to see who has a appointment within the area of an hour both before and after. So I created a Self-Joining relation with the date as one join, and the time from one to a time calc range, i.e. 3:00...5:00. This isn't working because the calc is a text, and not a value. Does anyone have any ideas?

Much Appreciated.

[Raybaudi]

Hi

you need a multipredicate relationship:

g_date = date

g_timeStart ≤ time

g_timeEnd ≥ time

where those 3 fields ( g_date, g_timeStart, g_timeEnd ) are global fields that you have to enter.

[Brett W]

Thanks for the quick response. That is what I thought, but didn't know if the greater than lesser then would CXL each other out.

Thanks,

Brett

[Fitch]

Daniele's method is good. Another approach that might work is to create 2 calc fields, one that is your time - 3600 and another that is your time + 3600. Now make a relationship from the original time field that is < one and > the other. Does that make sense? I.e., her method sets up globals as the parents and the time as the child; mine uses these calcs as the parents.

Edited January 16, 2007 by Guest

[Raybaudi]

her

Hi Fitch

[color:red]his :

[Brett W]

Thanks guys, this info is great!

I have another question relating to the portal issue. So my goal is to have a sum of all the appointments in a time range, as described above. I have the portal set up so it is relating all the appointments scheduled for that range, that part is done. Here is the new road block!

I am doing this all to try and calculate how many offices are being used at during a time period. So I was planning to simple add the rows together and get a number there, but I am running into the problem of the same people having different appointments in that range. Would this require an additional relationship to make one of those Records NULL, let say, or do you think this can be done with a calculation?