J
jpgoossen via AccessMonster.com
Hi,
I have two tables. One with employees, which include a start date and - if
already determined!) an end date of employment. For each week that people
work - which is for the whole period that they are under contract - they are
supposed to fill in hour sheets. These are then entered by a data entry
person into a database. In that database we have the table EMPLOYEE with
amongst other the fields Name, Startdate and Enddate.
In the table HOURS we save all the hours that people work, in weekly records.
The Important fields in this table are Employee_ID (link to a normalised
Employee Name/Address/Place table), LocationID (limnked to a table that
contains all possible worklocations) and WorkweekID (linked to a normalised
table with all the weeks between now and 2017, on weeknumber and year, and
also showing the start and end dates of each week).
Now, each employee is supposed to fill in an hour sheet every week, like I
stated. But seeing the large number of sheets, it is not always clear whther
all sheets are in, or if one particular employee of one particular place of
employment.
The table structure is below
Table Fields Origin/Join Point Description
EMPLOYEE
EMP_ID Unique ID
NAME Name
INITIAL Initials
Other fields
LOCATION
LOC_ID Unique ID
LOCATION Location
Other fields
WORKPLACE
WP_ID Unique ID
WP_EMP From EMP_ID }Unique combo
WP_LOC From LOC_ID }
STARTDATE Start date contract
ENDDATE End date contract (may be left empty!)
Other fields
WEEKNUMBER
WEEK_ID Unique ID
WEEKNUMBER Weeknumber from 1 to 52/53
WEEKYEAR Year for that specific week,
since there are same weeknumbers
in each year
WEEK_FROM Start date week
WEEK_TIL End date week
HOURS (The actual hour sheet table!)
HOUR_ID Unique ID
HOUR_WP From WP_ID } Unique combo
HOUR_WEEK From WEEK_ID }
Other fields
What I want to accomplish, is set up a report that shows any MISSING hour
sheets (at least, not typed in), sorted on either weeknumber, location or
employee or just a total list of all missing hour sheets.
I think it requires matching employees, combined with their contract dates,
agains the existence of the combination weeknumber/employeeID in the table of
Hoursheets. Or better, the missing of any records for their employment period.
Of course, it would be useless to search any further than just the date of
this week, since no one knows about future employment.
I can't seem to get my head around this and keep running in circles on how
to accomplish this. And on how to give the users an option to select periods
or locations or employees by themselves. Any ideas of tips are very welcome.
The whole move over from written work sheets to actually storing these in a
database is new. So we already have a number of work sheets to enter as soon
as we get this working.
The (written) sheets will be filled in by the employees and signed by them
and their supervisors. From there, the sheets will be taken by whoever is
present at the site (either coworkers or by internal mail) to the main office.
IN the main office the sheets will then be entered into the database. This
will be done by some data entry specialist who does not know who those
employees are and will thus not notice if there are too little or too many
written work sheets. This because the number of employees is quite changeable,
from week to week.
For the consultants or managers of those employees managers, it is important
to be able to see whose work sheets are missing. Or, what might be possible
too, from which location the work sheets have not come in. Add to this that
all those employee contract dates overlap and have no fixed start or end
dates (apart from the 3 month period that can be prolonged twice along) and
you will see that I am looking at a difficult task. At least, difficult for
me, being quite a noob in Access.
In the end I have to be able to select missing work sheets selected on
employees (and thus by reading their contract dates from the table, not a
fixed date), on any week or number of weeks that I select, on location et
cetera.
I placed a version of this question in the Forms area, but Reporting seems
tob e much more appropriate. Anyone who can help me out?
TIA,
Patrick
I have two tables. One with employees, which include a start date and - if
already determined!) an end date of employment. For each week that people
work - which is for the whole period that they are under contract - they are
supposed to fill in hour sheets. These are then entered by a data entry
person into a database. In that database we have the table EMPLOYEE with
amongst other the fields Name, Startdate and Enddate.
In the table HOURS we save all the hours that people work, in weekly records.
The Important fields in this table are Employee_ID (link to a normalised
Employee Name/Address/Place table), LocationID (limnked to a table that
contains all possible worklocations) and WorkweekID (linked to a normalised
table with all the weeks between now and 2017, on weeknumber and year, and
also showing the start and end dates of each week).
Now, each employee is supposed to fill in an hour sheet every week, like I
stated. But seeing the large number of sheets, it is not always clear whther
all sheets are in, or if one particular employee of one particular place of
employment.
The table structure is below
Table Fields Origin/Join Point Description
EMPLOYEE
EMP_ID Unique ID
NAME Name
INITIAL Initials
Other fields
LOCATION
LOC_ID Unique ID
LOCATION Location
Other fields
WORKPLACE
WP_ID Unique ID
WP_EMP From EMP_ID }Unique combo
WP_LOC From LOC_ID }
STARTDATE Start date contract
ENDDATE End date contract (may be left empty!)
Other fields
WEEKNUMBER
WEEK_ID Unique ID
WEEKNUMBER Weeknumber from 1 to 52/53
WEEKYEAR Year for that specific week,
since there are same weeknumbers
in each year
WEEK_FROM Start date week
WEEK_TIL End date week
HOURS (The actual hour sheet table!)
HOUR_ID Unique ID
HOUR_WP From WP_ID } Unique combo
HOUR_WEEK From WEEK_ID }
Other fields
What I want to accomplish, is set up a report that shows any MISSING hour
sheets (at least, not typed in), sorted on either weeknumber, location or
employee or just a total list of all missing hour sheets.
I think it requires matching employees, combined with their contract dates,
agains the existence of the combination weeknumber/employeeID in the table of
Hoursheets. Or better, the missing of any records for their employment period.
Of course, it would be useless to search any further than just the date of
this week, since no one knows about future employment.
I can't seem to get my head around this and keep running in circles on how
to accomplish this. And on how to give the users an option to select periods
or locations or employees by themselves. Any ideas of tips are very welcome.
The whole move over from written work sheets to actually storing these in a
database is new. So we already have a number of work sheets to enter as soon
as we get this working.
The (written) sheets will be filled in by the employees and signed by them
and their supervisors. From there, the sheets will be taken by whoever is
present at the site (either coworkers or by internal mail) to the main office.
IN the main office the sheets will then be entered into the database. This
will be done by some data entry specialist who does not know who those
employees are and will thus not notice if there are too little or too many
written work sheets. This because the number of employees is quite changeable,
from week to week.
For the consultants or managers of those employees managers, it is important
to be able to see whose work sheets are missing. Or, what might be possible
too, from which location the work sheets have not come in. Add to this that
all those employee contract dates overlap and have no fixed start or end
dates (apart from the 3 month period that can be prolonged twice along) and
you will see that I am looking at a difficult task. At least, difficult for
me, being quite a noob in Access.
In the end I have to be able to select missing work sheets selected on
employees (and thus by reading their contract dates from the table, not a
fixed date), on any week or number of weeks that I select, on location et
cetera.
I placed a version of this question in the Forms area, but Reporting seems
tob e much more appropriate. Anyone who can help me out?
TIA,
Patrick