Between Dates Joins

[Sprawl]

Wierd Join needed...

Here's my problem. it's been bothering for a bit...


I have 2 tables, one with a date, and the 2nd table with 2 dates. I need to only pull the records from the 2nd table where the date in the first table come between them.

TABLE 1
DATE
JOB#
EMPLOYEE#

TABLE2
JOB#
EMPLOYEE#
STARTDATE
ENDDATE

The query should take every line from TABLE1 and ONLY the lines from TABLE 2 that qualify.

 

[kidrobot]

In the first table how are these dates populated? Give me an example of the 3 differ dates.

 

[kidrobot]

btw do you do MMA or wrestling? Sprawl is a MMA brand and a wrestling move. J/w because I do MMA/BJJ and wrestled in HS.

 

[WayneRyan]

sprawl,

Select Table1.[Date],   <-- Date is reserved for use by Access
       Table1.[JOB#],
       Table1.[EMPLOYEE#],
       Table2.[JOB#],
       Table2.[EMPLOYEE#],
       Table2.[STARTDATE],
       Table2.[ENDDATE]
From   Table1 Left Join Table2 On
       Table1.[JOB#] = Table2.[JOB#] And
       Table1.[EMPLOYEE#] = Table2.[EMPLOYEE#]
Where  Table1.Date Between Table2.[STARTDATE] And Table2.[ENDDATE] And
       Table2.[JOB#] Is Not Null

Wayne

 

[Sprawl]

Ok, The data is populated by a 3rd party application and the data is non writable in the query side.

However, you will see in the tables

Table 1
Date -------- JOBID ------- EMP#
01/30/2007 --TOR455-01 -- 1000

Table 2
STARTDATE --- End Date ----- JOBID ----------- EMP# --- Recruiter
12/30/2006 ---- 02/20/2007 ---- TOR455-01 ------ 1000 --- BOB
03/31/2007 ---- 02/20/2008 ---- TOR455-01 ------ 1000 ---- DOUG

And the query must be able to pull the correct recruiter applicable to the line.




And no, sprawl is just a nickname I earned playing Goalie in ice hockey. usually the position i end up. Sprawled out on my but

 

[WayneRyan]

sprawl,

Forget the ending --> And Table2.[JOB#] Is Not Null

Wayne