A
Arvi Laanemets
Hi
I´m trying to design an app for working time calculation. Previously it was
made in an Excel table, design of which probably originated from it's paper
ancestors - data about one department for one month in one single workbook.
The new app generally works, and I can use it as a source database for
various other Access or Excel applications. But there is a problem - at top
they want a report in old accustomed style.
In my new app, working time info is collected into table TimeTable: EntryID,
Date, Department, TabN, EntryType, Hours
The field EntryType can have values, which indicate day-time working time,
evening working time, night working time, working time on holiday or
state-holiday, owerwork, various free days or holidays, etc. The field Hours
contains working hours or remains empty - depending on EntryType. The
Primary Key is EntryID.
The field TabN contains an employee code from table Employees: TabN,
Department, ..., From, To
where From and To contains start and end dates for entry. The Primary Key is
composed as TabN+From
Now from those tables I need a report for selected department and month,
with columns (Nr), TabN, 01, 02, ..., 31, (+ various summary columns).
For every TabN, there will be 4 different values (rows) for columns 01
through 31:
1st
- the number of working hours, when the employee had EntryType
indicating day-time working time, or working time on holiday/State holiday.
- some pre-defined 2-character string, indicating that emplyee was ill,
or was on holiday, or on a business trip, or didn't arrive at work (a
different string for every EntryType except all kinds of inside working time
entries and for overtime)
2nd
- the number of working hours, when the employee had EntryType
indicating evening working time
3rd
- the number of working hours, when the employee had EntryType
indicating night working time
4th
- the number of working hours, when the employee had EntryType
indicating overtime.
Which is best way to proceed with this? Starting with composing a query,
which has for every day in month a row for every employee, enlisted for this
day in this department - with 31*4+x calculated columns? Or is there some
better way?
We have >500 employees at moment, and probably the database must be able to
contain data over several years in future. The number of employees in
biggest departments is >50 at moment.
Thanks in advance for any good ideas!
I´m trying to design an app for working time calculation. Previously it was
made in an Excel table, design of which probably originated from it's paper
ancestors - data about one department for one month in one single workbook.
The new app generally works, and I can use it as a source database for
various other Access or Excel applications. But there is a problem - at top
they want a report in old accustomed style.
In my new app, working time info is collected into table TimeTable: EntryID,
Date, Department, TabN, EntryType, Hours
The field EntryType can have values, which indicate day-time working time,
evening working time, night working time, working time on holiday or
state-holiday, owerwork, various free days or holidays, etc. The field Hours
contains working hours or remains empty - depending on EntryType. The
Primary Key is EntryID.
The field TabN contains an employee code from table Employees: TabN,
Department, ..., From, To
where From and To contains start and end dates for entry. The Primary Key is
composed as TabN+From
Now from those tables I need a report for selected department and month,
with columns (Nr), TabN, 01, 02, ..., 31, (+ various summary columns).
For every TabN, there will be 4 different values (rows) for columns 01
through 31:
1st
- the number of working hours, when the employee had EntryType
indicating day-time working time, or working time on holiday/State holiday.
- some pre-defined 2-character string, indicating that emplyee was ill,
or was on holiday, or on a business trip, or didn't arrive at work (a
different string for every EntryType except all kinds of inside working time
entries and for overtime)
2nd
- the number of working hours, when the employee had EntryType
indicating evening working time
3rd
- the number of working hours, when the employee had EntryType
indicating night working time
4th
- the number of working hours, when the employee had EntryType
indicating overtime.
Which is best way to proceed with this? Starting with composing a query,
which has for every day in month a row for every employee, enlisted for this
day in this department - with 31*4+x calculated columns? Or is there some
better way?
We have >500 employees at moment, and probably the database must be able to
contain data over several years in future. The number of employees in
biggest departments is >50 at moment.
Thanks in advance for any good ideas!