Creating a recordset counting records within two date fields

[PaulA]

Hi, All,

I have a query that produces a recordset with each record having two date fields, a sort of start date and end date.

I am interested in creating a query or VBA that would produce a list of dates within a date range with each date having a count of all records in the original recordset where that date is within the record's start and end dates. Is this making any sense?

I'm not much of a coder although I know my way around VBA to a certain extent. I assumed this would take a coding solution as opposed to a query, but I'll consider anything that works.

Any assistance or a place to start would be appreciated.

Thanks.

 

[CJ_London]

should be able to do it in a query but not clear on what you are asking - please provide some example data from your original recordset, where your date range comes from and the expected output from the sample data.

Best I can suggest at the moment is a group by query - something like

SELECT myDate, count(myDate) AS DateCount
FROM myTable
WHERE myDate between [Enter first date] and [enter last date]
GROUP BY myDate

 

[Ranman256]

Select name,count(field) from table between [startdate] and [endDate]

 

[arnelgp]

SELECT DISTINCT T1.[start date], T1.[end date], DCOUNT("*", "yourTableName", "[start date] = #" & Format(T1.[start date], "mm/dd/yyyy") & "# And [end date] = #" & Format(T1.[end date], "mm/dd/yyyy") & "#")) As CountInRange FROM yourTableName AS T1;

 

[PaulA]

Thanks, all, for your responses. Here is a small sample of the original data:

ID Start Date End Date
282 7/22/2015 9/23/2015
286 7/22/2015 9/16/2015
242 7/23/2015 9/23/2015
253 7/25/2015 9/9/2015
235 7/30/2015 10/7/2015
236 7/30/2015 2/10/2016
265 7/30/2015 9/23/2015

What I would want is to know for any given date how may records that date was within the start and end date of that record so we would end up with output like

Date Count
8/14/15 7
9/12/15 3

We would want to produce sequential dates for a date range and then the counts for those dates.

Hope this helps.

 

[CJ_London]

so to clarify, you have a table with date ranges as provided and you have another table with a number of dates in which you want to count against?

if so, something like

 SELECT tblDates.reqDate, Count(datefrom) AS CountOfDates
FROM tblPeriods, tblDates 
 WHERE tblDates.reqDate Between [datefrom] And [dateto]
GROUP BY tblDates.reqDate