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.