Linking Tables by Date Range

Lewe22

I have a table with a date stamp on it. [tblDistLearnTrack]

Expand|Select|Wrap|Line Numbers

 unique_id      dist_created_date

1              01/06/2008

2              01/06/2008

3              05/06/2008

4              22/06/2008

And a table with date ranges on it. [tblPayMonths]

Expand|Select|Wrap|Line Numbers

 month_id       start_date     end_date      pay_month

07-APRMAY08    20/05/2008     19/06/2008    JUN-08 

07-MAYJUN08    20/06/2008     19/07/2008    JUL-08

07-JUNJUL08    20/07/2008     19/08/2008    AUG-08

What i want to do is join the two tables together and pull in the relevant pay month where the created date falls in between the date ranges.
So i am hoping to see results like this:

Expand|Select|Wrap|Line Numbers

 unique_id      dist_created_date      pay_month

1              01/06/2008             JUN-08

2              01/06/2008             JUN-08

3              05/06/2008             JUN-08

4              22/06/2008             JUL-08

Does anyone have any ideas?

Stewart Ross Inverness

One simple way to do this is to use a WHERE clause to select the relevant rows from the two otherwise-unjoined tables:

Expand|Select|Wrap|Line Numbers

 SELECT tblDistLearnTrack.unique_id, tblDistLearnTrack.dist_created_date, tblPayMonths.pay_month

FROM tblDistLearnTrack, tblPayMonths

WHERE (((tblDistLearnTrack.dist_created_date) Between [start_date] And [end_date]));

which on your test data plus a couple of rows results in:

Expand|Select|Wrap|Line Numbers

 unique_id...dist_created_date......pay_month

.............01/06/2008............JUN-08

.............01/06/2008............JUN-08

.............05/06/2008............JUN-08

.............22/06/2008............JUL-08

.............30/07/2008............AUG-08

.............01/07/2008............JUL-08

-Stewart

Lewe22

Thanks Stewart. Knew it would be easy!

Lewe22

Hang on a minute, that isn't working. It is omitting records....will post an example....

Lewe22

My apologies, it did work!

NeoPa

I would recommend using INNER JOIN within the FROM clause rather than the WHERE clause. This isn't critical, but it's clearer (for me at least) what is being done (SQL isn't easy to read at the best of times).

Expand|Select|Wrap|Line Numbers

 SELECT tDLT.unique_id,

       tDLT.dist_created_date,

       tPM.pay_month

FROM tblDistLearnTrack AS tDLT INNER JOIN

     tblPayMonths AS tPM

  ON tDLT.dist_created_date Between tPM.start_date And tPM.end_date

Linking Tables by Date Range

re: Linking Tables by Date Range

re: Linking Tables by Date Range

re: Linking Tables by Date Range

re: Linking Tables by Date Range

re: Linking Tables by Date Range

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about