SQL query to count number of dates in one table between dates in other table

[Neo44]

I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.

To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014

table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014

So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in tbale 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)


Between 15/01/2014 17/01/2014 in tbale 1 there are 0 dates in table 2
Between 17/01/2014 30/01/2014 in table 1 there are 4 dates in table 2

Can anyone help?

[ItsMe]

I would start by creating a SELECT query. Join your two tables on their Key values and SELECT your date fields. Add some criteria to your SELECT query using the BETWEEN operator. With that you should be able to switch to a Totals query and employ the Count() function.

[lfpm062010]

Is it possible to restructure your Table1? If it is possible, it will make the query easier.
This is what I would recommend if the restructure is allowed.

Table1: STARTDATE ENDDATE
01/01/2014 15/01/2014
15/01/2014 17/01/2014
17/01/2014 30/01/2014

Then the query will be like. NOTE: It will not show the count with 0. If you need that, it will need to some modifying.

FIRSTQUERY:
SELECT Table2.DATES, IIF(Table2.DATES>Table1.STARTDATE AND Table2.DATES<Table1.ENDDATE, "YES", "NO") AS INRANGE, Table1.STARTDATE, Table1.ENDDATE
FROM Table2, Table1;

FINALQUERY:
SELECT Table1.STARTDATE, Table1.ENDDATE, Count(FIRSTQUERY.INRANGE) AS CountOfINRANGE
FROM Table1 INNER JOIN FIRSTQUERY ON (Table1.ENDDATE = FIRSTQUERY.ENDDATE) AND (Table1.STARTDATE = FIRSTQUERY.STARTDATE)
WHERE (((FIRSTQUERY.INRANGE)="YES"))
GROUP BY Table1.STARTDATE, Table1.ENDDATE;

[Neo44]

Thanks! Yes I definitely also need the counts with 0. And I would prefer a solution without restructering the table, but I can see that the way you propose makes it easier.

[orange]

Note: The BETWEEN operator in Access includes the end points.
If you don't restructure your table1 as suggested by lfpm062010, you will need some vba code in my opinion. The vba would be used to identify the ranges.

Good luck with your project.

[lfpm062010]

Ok, I added a ID field to the table. NOTE: for the query to work, the ID has to be in sequence and not to have any missing value (can't skip from 2 to 4 and missing 3). If that is still not going to solve your issue, then VB is the only other option like orange suggested.

Table1: ID STARTDATE
1 01/01/2014
2 15/01/2014
3 17/01/2014

Then the query will be like. NOTE: It will not show the count with 0. If you need that, it will need to some modifying.

Query2:
SELECT IIf([Table1].[ID]=DMax("ID","Table1"),Null,[Table1].[STARTDATE]) AS SDATE, IIf([Table1].[ID]=DMax("ID","Table1"),Null,DLookUp("STARTDATE","Tab le1","ID = " & [Table1].[ID]+1)) AS EDATE
FROM Table1;

FIRSTQUERY:
SELECT Query2.SDATE, Query2.EDATE, Table2.DATES, IIf(Table2.DATES>Query2.SDATE And Table2.DATES<Query2.EDATE,"YES","NO") AS INRANGE
FROM Table2, Query2;

FINALQUERY:
SELECT Query2.SDATE, Query2.EDATE, DCOUNT("INRANGE", "FIRSTQUERY", "INRANGE = 'YES' AND SDATE = #" & Query2.SDATE & "# AND EDATE = #" & Query2.EDATE & "#")
FROM Query2
WHERE (((Query2.SDATE) Is Not Null))
GROUP BY Query2.SDATE, Query2.EDATE;