Create a query field to identify records by a date range 1

[swaybright]

I am sure this has been addressed before, but I am struggling with how to ask the question properly. My basic problem is that I have multiple data streams that I am pulling together from independent sources where the only linkage is date and time. I would like to assign an ID from table 2 to each record in table 1 so I can then build the appropriate relationships. Any thoughts on how to construct the query to do this? I have included a simplified example of what I want to do. (my actual "Tbl1" has over 100,000 records)


Tbl1
ID DateTime
1 10/1/2014
2 10/2/2014
3 10/5/2014
4 10/7/2014
5 10/8/2014
6 10/9/2014
7 10/6/2014

Tbl2
ID StartDate EndDate Range
1 10/1/2014 10/3/2014 R1
2 10/5/2014 10/7/2014 R2
3 10/8/2014 10/10/2014 R3

QueryOutput

Tbl1.ID Tbl2.ID
1 1
2 1
3 2
4 2
5 3
6 3
7 2

Thanks!

 

[PHV]

You wanted this ?

SELECT Tbl1.ID, Tbl2.ID
FROM Tbl1, Tbl2
WHERE Tbl1.DateTime Between Tbl2.StartDate And Tbl2.EndDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[swaybright]

Thanks PH! This is exactly what I need! And it is much simpler than I made it out to be, I was really over-thinking this problem. (note my sheepish grin)

SW