Join two tables, Based on two fields in each

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
 
T

Ted Allen

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
 
J

John Spencer (MVP)

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
 
J

John Vinson

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)

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])
 
T

Ted Allen

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top