Graph for employee availability

[Maddy]

I manage a small organisation of around 100 employees, with different
specialisations. It becomes difficult to check how many persons of
each specialisation are available at any time, based on their vacation
period. So I have made a ms-acces database as follows -

A table EMPL with details of employees.
A table SPEC with the various specialisations.
The EMPL table has a specialisation field linked to the SPEC table.
Finally a VACATION table with the following fields- EMPLOYEEID (linked
to EMPL), StartDate and EndDate (of vacations). So if Mr.Smith is
going out for 10 days in Jan and 15 days in Mar, The entries would be
-
EMPLID STARTDATE ENDDATE
2 03 Jan 07 13 Jan 07
7 15 Apr 07 15 May 07
2 15 Mar 07 30 Mar 07

where Mr.Smith is employee 2 and EMPLID 7 is some other employee. I
hope I have been able to make myself understood.

What I need is a report which can be grouped by specialisation,
showing me graphically which employees of each specialisation will be
available during the requested period.
So if I ask for the Jan to Jun period, I will get a graph showing
Mr.Smiths specialisation as the group heading and a horizontal bar for
Mr.Smith showing green for the period he is available and red in the
vacation period in Jan and mar. Ditto for all employees with the same
specialisation, one below the other. So one glance will show me which
guys will be available during any period.

Whew!

I hope somebody reads this entire message.

Anyways, what I need is guidance as to how do I do this. Which type of
graph can I use. Can I use the VACATION table data directly to
generate this kind of a graph?

Any advice is welcome, includiong a change in the way of visualising
or if there is some other tool better suited for this kind of thing.

Thanks
Maddy

[Steve]

A pivot chart will do this. The key is recognizing that an employee is
either at work or not at work on any date. So you need to create a dataset
containing each date in a selected time period and for each date determine
if the employee was at work or not. Whether he was at work or not is a
true/false condition so for an employee you plot the date versus the
true/false condition.

The above is a simplified suggestion but it takes some good design and
considerable work to arrive at a pivot chart showing employees and whether
they were at work or not for the days a selected time period. I have done a
similar thing in a reservation database to show rooms availability in a
selected time period.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

"Maddy" <mn******@gmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...

>I manage a small organisation of around 100 employees, with different
specialisations. It becomes difficult to check how many persons of
each specialisation are available at any time, based on their vacation
period. So I have made a ms-acces database as follows -

A table EMPL with details of employees.
A table SPEC with the various specialisations.
The EMPL table has a specialisation field linked to the SPEC table.
Finally a VACATION table with the following fields- EMPLOYEEID (linked
to EMPL), StartDate and EndDate (of vacations). So if Mr.Smith is
going out for 10 days in Jan and 15 days in Mar, The entries would be
-
EMPLID STARTDATE ENDDATE
2 03 Jan 07 13 Jan 07
7 15 Apr 07 15 May 07
2 15 Mar 07 30 Mar 07

where Mr.Smith is employee 2 and EMPLID 7 is some other employee. I
hope I have been able to make myself understood.

What I need is a report which can be grouped by specialisation,
showing me graphically which employees of each specialisation will be
available during the requested period.
So if I ask for the Jan to Jun period, I will get a graph showing
Mr.Smiths specialisation as the group heading and a horizontal bar for
Mr.Smith showing green for the period he is available and red in the
vacation period in Jan and mar. Ditto for all employees with the same
specialisation, one below the other. So one glance will show me which
guys will be available during any period.

Whew!

I hope somebody reads this entire message.

Anyways, what I need is guidance as to how do I do this. Which type of
graph can I use. Can I use the VACATION table data directly to
generate this kind of a graph?

Any advice is welcome, includiong a change in the way of visualising
or if there is some other tool better suited for this kind of thing.

Thanks
Maddy

[John Marshall, MVP]

I'm glad you are now offering this help for free.

John... Visio MVP

"Steve" <so***@private.emailaddresswrote in message
news:F2*******************@newsread3.news.pas.eart hlink.net...

>
The above is a simplified suggestion but it takes some good design and
considerable work to arrive at a pivot chart showing employees and whether
they were at work or not for the days a selected time period. I have done
a similar thing in a reservation database to show rooms availability in a
selected time period.

PC Datasheet