CODEWITHSUNDEEP
ms-accessjoin
Castellum
Castellum

Reputation: 21

Joining Tables based on Date Ranges

I have an MS Access question

I have been struggling with a query containing a join with date ranges. Found a couple off posts but don't get it to work

I have two tables. One containing some data and a Due Date.

Table A 
Field 1, Field 2, Field 3, Due Date

The other table contains information about a period.

Table B
Periodname, Field X/Y/Z, StartDate, EndDate

I try to make a query with this output

Field 1, Field 2, Due Date, Periodname

Hope you can help.

Upvotes: 2

Views: 4019

Answers (5)

Castellum
Castellum

Reputation: 21

Done. Combining your answers!! Thanks

SELECT Left([Q_Epics].[Component/s],3) AS Proj_Nr, Mid([Q_Epics].[Component/s],6,35) AS Project, Q_Epics.[Epic Key] AS [Epic Key], Q_Epics.Summary AS Summary, Q_Epics.Labels AS Labels, Q_Epics.[Due Date] AS [Due Date], [Sprints].[Sprint] AS Sprint

FROM Q_Epics

LEFT JOIN Sprints ON (Q_Epics.[Due Date] >= [Sprints].[StartSprint]) AND (Q_Epics.[Due Date] <= [Sprints].[EindeSprint])

WHERE (((Q_Epics.EpicStatus)<>"Closed"))

ORDER BY Q_Epics.[Due Date];

Upvotes: 0

Castellum
Castellum

Reputation: 21

In this version the error is: You try tu run a query where the expressin 'Sprints.Sprint' is not part of the static function

SELECT Left([Q_Epics].[Component/s],3) AS Proj_Nr, Mid([Q_Epics].[Component/s],6,35) AS Project, Q_Epics.[Epic Key], Q_Epics.Summary, Q_Epics.Labels, Q_Epics.[Due Date], Sprints.Sprint

FROM Q_Epics

Inner JOIN Sprints ON Q_Epics.[Due Date] >= [Sprints].[StartSprint] AND Q_Epics.[Due Date] <= [Sprints].[EindeSprint]

GROUP BY Left([Q_Epics].[Component/s],3), Mid([Q_Epics].[Component/s],6,35), Q_Epics.[Epic Key], Q_Epics.Summary, Q_Epics.Labels, Q_Epics.[Due Date], Q_Epics.EpicStatus

HAVING (((Q_Epics.EpicStatus)<>"Closed"))

ORDER BY Q_Epics.[Due Date];

Upvotes: 0

Castellum
Castellum

Reputation: 21

This is what I created so far But I get a error on the "Between" command. "You used the operator 'Between' without 'And'

SELECT Left([Q_Epics].[Component/s],3) AS Proj_Nr, Mid([Q_Epics].[Component/s],6,35) AS Project, Q_Epics.[Epic Key], Q_Epics.Summary, Q_Epics.Labels, Q_Epics.[Due Date], Sprints.Sprint AS Sprint

FROM Q_Epics

LEFT JOIN Sprints ON Q_Epics.[Due Date] BETWEEN [Sprints].[StartSprint] AND [Sprints].[EindeSprint]

GROUP BY Left([Q_Epics].[Component/s],3), Mid([Q_Epics].[Component/s],6,35), Q_Epics.[Epic Key], Q_Epics.Summary, Q_Epics.Labels, Q_Epics.[Due Date], Q_Epics.EpicStatus

HAVING (((Q_Epics.EpicStatus)<>"Closed"))

ORDER BY Q_Epics.[Due Date];

Upvotes: 0

E Mett
E Mett

Reputation: 2302

In MsAccess you can actually write a join statement using < and >, although you cannot show it in the query editor.

SELECT [Field 1], [Field 2], [Field 3], [Due Date], PeriodName
FROM A INNER JOIN B ON A.[Due Date] >= B.StartDate AND A.DueDate <= B.EndDate

Upvotes: 1

Gustav
Gustav

Reputation: 55841

You can "join" by a where clause:

Select
    Field1, 
    Field2, 
    DueDate, 
    Periodname
From
    TableA,
    TableB
Where
    DueDate Between StartDate And EndDate

Upvotes: 0

Related Questions