Join two tables, Based on two fields in each

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Request for assistance.
I need a query that brings two tables together. One
table has a precise date/time and the other has a range of
dates/times. I need to associate the one precise date and
time with those dates and times which fall within a window
say 1 hour each way from the precise time. I have a query
that does nearly all of this. all that remains is the
formula to match the precise date/time with the many
date/times. I believe that it should be in the query as a
field. But that just a thought.

Schedule.Date_Time (One of these matches)
Conference.Date_Time (Several of these)

Idea:

If Conference.Date_Time >= (Schedule.Date_Time - 60
Minutes) And Conference.Date <= (Schedule.Date_Time + 60
minutes)

Have no idea how to rep minutes. And if this is the right
idea or close where should it be placed.

I thank all who are willing to help me.
I also thank those who have helped me in the past.

Robert
 
Hi Robert,

I believe that you should be able to do this by adding a
calculated field with the following entry that will give
you the number of hours between the two date/time values:

HourDifference: Abs((Schedule.Date_Time-
Conference.Date_Time))*24

Then, just enter <=5 for the criteria.

Post back if this doesn't work.

-Ted Allen
 
To add time to a datetime use the DateAdd function.

DateAdd("n",60,Schedule.Date_Time)
DateAdd("n",-60,Schedule.Date_Time)

That is "n" for minutes, because "m" is already used for Months

You could also use the DateDiff Function and check for a difference

WHERE Abs(DateDiff("n",Schedule.Date_Time,Conference.[Date])) <= 60

You could
 
Request for assistance.
I need a query that brings two tables together. One
table has a precise date/time and the other has a range of
dates/times. I need to associate the one precise date and
time with those dates and times which fall within a window
say 1 hour each way from the precise time. I have a query
that does nearly all of this. all that remains is the
formula to match the precise date/time with the many
date/times. I believe that it should be in the query as a
field. But that just a thought.

Schedule.Date_Time (One of these matches)
Conference.Date_Time (Several of these)

Idea:

If Conference.Date_Time >= (Schedule.Date_Time - 60
Minutes) And Conference.Date <= (Schedule.Date_Time + 60
minutes)
Click to expand...

You're close. The DateAdd() function will do this for you: in your
Query, put a criterion on COnference.Date_time of
= DateAdd("h", -1, [Schedule].[Date_Time]) AND <= DateAdd("h", 1, [Schedule].[Date_Time])
Click to expand...
 
Oops,

For some reason in my initial response I was thinking
that you wanted all within 5 hours, instead of one, not
sure how I got that in my head. Anyway, if you did use
this method the criteria would be <=1, not 5. John
Spencer and John Vinson also posted other methods that
may be better, or at least more clear to others in the
future as to what it is doing.

-Ted Allen
 
Back
Top