By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

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

P: 5
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.
Sep 17 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
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?
Sep 17 '10 #2

P: 5
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 :-)
Sep 17 '10 #3

Expert 100+
P: 1,287
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.
Sep 17 '10 #4

P: 5
In the query there are real dates generated, so one of the criteria is that the dates must then also be the same.
Sep 17 '10 #5

Expert 100+
P: 1,287
In that case, I would join the tables on the date field, and only show records where they were equal and the times conflicted.
Sep 17 '10 #6

P: 5
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.
Sep 17 '10 #7

P: 5
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!
Sep 18 '10 #8

P: 1
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.
Sep 24 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.