Designing a query to find matching records in 2 other queries.

[Maverex]

I want to design a query that checks 2 other queries for overlapping planning times. I generate recurring events in a query. It generates dates and beginning times and ending times. There is also an itemID int the records. I want to chech if that item beginning time clashes with time slots in the other records in the same query. I was thinking of putting the querie twice in one other. But how do i compare all the records to each other ?

One of those queries can be as big as 50.000 records.

I think it will not be that difficult but i do not "see" it.

[ChipR]

So for each record in one query, you want to check whether its beginning time is between the beginning and ending times of each record in the other query?

[Maverex]

Hi ChipR,

That is correct. And on top of that, the two queries are indentical. In fact it is one query and i want to check in that query if a "start time" falls between "All the start times" and "All the and times" .

It is a query with recurring events and i want to check if an employees planning time is clashing. He can not be in two places at the same time :-)

[ChipR]

You can design a query using the same input table/query twice. The query designer will automatically add AS TABLE_1 to differentiate them. Then you can query for MyTable.StartTime BETWEEN MyTable_1.StartTime AND MyTable_1.EndTime. Or something similar.

I'm concerned, though, about how you determine whether these events are on the same day of the week or month if they are recurring.

[Maverex]

In the query there are real dates generated, so one of the criteria is that the dates must then also be the same.

[ChipR]

In that case, I would join the tables on the date field, and only show records where they were equal and the times conflicted.

[Maverex]

I do not have the time to test this right now. I looks good but in the first test return too much records. I will test it very soon and get back to you. Thanks for the help so far.

[Maverex]

It is a little rough and i am still testing but the results are there. I could not use the "Between" statement in the query but "Is smaller than" and "Is larger than". but you absolutely pointed me in the right direction.
Thank you very much!

[redman88]

okay i am doing some thing similer to this but i want the records that are not in the other query. every thing i try just gets me the results for whats in both.