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.

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

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.

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

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

sprawl,

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

Wayne