CODEWITHSUNDEEP
databasems-accessjoin
magicmike
magicmike

Reputation: 5

Microsoft Access Joining two tables with Date Ranges

I have two tables with date ranges that I would like to join. Table 1 looks like this:

Table 1:
----------------------------------------------------------------------------------
|    ID     |    Place    |     Description     |    Date 1    |     Date 2      |
----------------------------------------------------------------------------------
|         1 | Place 1     | Description 1       |     1/1/1988 |      12/31/1993 |
----------------------------------------------------------------------------------
|         2 | Place 1     | Description 2       |     1/1/1994 |      12/31/1994 |
----------------------------------------------------------------------------------
|         3 | Place 1     | Description 3       |     1/1/1995 |        2/8/2018 |
----------------------------------------------------------------------------------

Table 2 looks like this:

Table 2:
---------------------------------------------------------------------------------
|    ID     |     Equipment      |    Place    |    Date 1    |     Date 2      |
---------------------------------------------------------------------------------
|         1 | Item 1             | Place 1     |     1/1/1990 |      12/31/1995 |
---------------------------------------------------------------------------------
|         2 | Item 2             | Place 1     |     1/1/1996 |        2/8/2018 |
---------------------------------------------------------------------------------

I would like the results to look like this:

Results:
------------------------------------------------------------------------------------------
|     Equipment      |    Place    |    Description     |    Date 1    |     Date 2      |
------------------------------------------------------------------------------------------
| Item 1             | Place 1     | Description 1      |     1/1/1990 |      12/31/1993 |
------------------------------------------------------------------------------------------
| Item 1             | Place 1     | Description 2      |     1/1/1994 |      12/31/1994 |
------------------------------------------------------------------------------------------
| Item 1             | Place 1     | Description 3      |     1/1/1995 |      12/31/1995 |
------------------------------------------------------------------------------------------
| Item 2             | Place 1     | Description 3      |     1/1/1996 |        2/8/2018 |
------------------------------------------------------------------------------------------

In the above example, I am essentially attempting to add the "Description" to Table 2. However, the "Description" from Table 1 does not have the same date range as the record set in Table 2.

I am struggling to come up with a query, or even a set of queries to generate the desired results. Any push is the right direction is most gratefully appreciated.

Upvotes: 0

Views: 1038

Answers (1)

DanielG
DanielG

Reputation: 1675

You're going to need a query that primarily pulls from table 2, and then does a two-way join with multiple conditions to table 1. The first join would be on place, and the second join needs to also join on date ranges:

Rough SQL would be (disclaimer: not tested):

SELECT 
    T2.[Description], T2.[Place], T1.[Description], etc..
FROM
    T2
INNER JOIN T1 ON 
    T1.[Place] = T2.[Place]
AND 
    (
       T2.[Date 1] BETWEEN T1.[Date 1] AND T1.[Date 2]
       AND
       T2.[Date 2] BETWEEN T1.[Date 1] AND T1.[Date 2]
    )

Upvotes: 2

Related Questions