New to Access and teaching myself (using "The Missing Manual" on Access). I track employee status on job sites. I have an excel spreadsheet where, by hand, I would count how many people were out on a job for a given day, then log that. So, I have a spreadsheet with the first column as each day of the year (365 days/366 days) and years across the top. Each day gets a count, even if there isn't anyone out.
I went back to the original records and made a database table that has each employee and the date they arrived on site and the date the were released. I have verified the integrity of the data, and now I want to ensure that anything I do in excel is based on the data from Access. I would like to create a query that tells me, for each day of every year back to 1/1/1984, how many people were on the job that day.
I designed a query in query designer, with a parameter called "DateInQuestion" as follows:
Show: <not checked>
Criteria: (([DateInQuestion]) BETWEEN [Dispatches].[ArrivalTime] AND [Dispatches].[ReleaseDate])
and another column that has:
This is showing me the correct data for the date I enter (the query parameter). However, I would like to have a count for every day of every year since 1984 (11,323+ days). Obviously I don't want to run/create a query for each day. How do I get a query return that has a list of days in column 1 (all 11,323+) and a daily count of those employees out in column 2? I can't have any gaps in dates as I import the query data back to excel where I graph the progress for the year to date overlaid on a graph of the max out since 1984, the average out for any given day of the year and the standard deviation for any given day of the year. I've already got this chart running in excel and it helps me easily identify busy times vs slow times.