Query to count number of entries active on a certain date

[MuguNR]

I am using Access to monitor bird nesting activity. I would like to determine how many nests were active on each day in a 3-4 month window and determine which day the maximum number of nests were active simultaneously.

My table (tblNest_Details) has the following columns:
Nest_Number


Date_Found
Date_Inactive

My goal to have results like this so I can find the date with the most or make a graph of activity over time:

6/1/12 10 active
6/3/12 12 active
6/15/12 20 active
6/27/12 24 active
6/29/12 17 active
7/5/12 8 active

The nests all have different found and inactive dates. Some nest become inactive before others are even found. I am currently working with a data set of approximately 100 individual nests.

Is it possible to contruct a query with the results I desire using just the Date_found and date_inactive columns?

Since I also have a separate table (tblNest_monitoring) that lists the day each nest was checked (while it was active between the Date_found and Date_inactive dates), I considered doing a query similiar to what one might do to determine daily student attendance. However the nest are not checked every day and different nests are checked on different days, so it is not quite like a student attendance record where each day would have an entry. I do not want to add entries for days nests were NOT checked because eventually I will be tracking over 800 nests in this database.

Thank you for your assistance.

[June7]

Have you tried to build aggregate (GROUP BY) queries with the data?

[MuguNR]

No. I've done aggregate queries before, but I am not sure how to set one up in this situation using the staggered date ranges.

[June7]

What do staggered date ranges have to do with counting data for each day? Your example looks like a simple aggregation by date.

Could do a count of nests with observation data between a given date range but would daily aggregate be meaningful?

[MuguNR]

It probably IS simple, but apparently not for me! I know how to do the query using just one of the date columns (such as using only the date_found column to find out which nests were found in a certain month).

However, I want the query to look at the days IN BETWEEN [date_found] and [date_inactive] for each nest, then tell me how many of the nests were active on each day of the 3-4 month period.

If what I described is what you were referring to when you asked if a daily aggregate would be meaningful, then yes, it would be, but I don't know how to write my query.

[June7]

Consider: It is easy to count the number of observations by date. Can also count how many nests were active during a given date range but counting total active nests for each day will not be easy. I expect subquery will be involved. Review http://allenbrowne.com/subquery-01.html

It would be best if I could test with data. You want to provide db? Follow instructions at bottom of my post.

[MuguNR]

I'v attached the data for you to look at. Thank you so much!

[June7]

Can't actually do a count for every day because there is no set of records in the db that has a field with a date value for each day. Wanting a count by a set interval (hourly, daily, every 10 minutes) is a fairly common topic. Review:
https://www.accessforums.net/access/...tes-16504.html
https://www.accessforums.net/queries...rly-15766.html
https://www.accessforums.net/queries...not-22194.html

This is the best I can get. Couldn't make subquery work so went with domain aggregate function.

SELECT tblNest_Details.Date_Found, DCount("ID","tblNest_Details","Date_Found<=#" & [Date_Found] & "# AND Nz([Inactive_Date],Date())>#" & [Date_Found] & "#") AS Daily
FROM tblNest_Details
GROUP BY tblNest_Details.Date_Found, DCount("ID","tblNest_Details","Date_Found<=#" & [Date_Found] & "# AND Nz([Inactive_Date],Date())>#" & [Date_Found] & "#");

[MuguNR]

Thanks for your help! I'll try out what you did and check out the links provided as well.