Averages

[Nixx1401]

I created a leave database and I am trying to create some queries to display
the average number of staff on vacation daily.

peak day and the number of persons on vacation leave (which day in the month do you have the most persons on vacation leave and the total number of persons)

minimum day and number of persons on vacation leave (day which have the least number of persons on vacation leave and the total number of persons)

The form which records the leave has attributes of



Name, Registration Number PK,
subform: entry date, start date, end date, num of days taken, type of leave
Any suggestions will help

[NTC]

ok so you have a dilemma;

it will be easy to query by a single day i.e. number of persons on vacation leave

you can query the date 4/15/11 easily in query design
StartDate criteria >=4/15/11
EndDate criteria <=4/15/11

and that will return the list of employees out on that date.

but there is no feasible way to automate that across 365 days and then sum. So you can't generically query 'what day are the most people out' ? ...because you only record the start/end dates. you must specify the date. or should I say - a human can intuitively see this relationship but a database needs more explicit linking or blunt force to automatically run 365 queries in a row.

so if someone is out 3/1 to 3/15 - - - if you record each date in a 'vacation table' i.e.
ID 3/1
ID 3/2
ID 3/3

then you can run Aggregate queries that would group & count/avg/etc by all dates....

hope this helps.