reporting on non existing records

  • Thread starter Thread starter jpgoossen via AccessMonster.com
  • Start date Start date
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.

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.

TIA,

Patrick
 
Just an initial thought here..

Why not create a query with a two week span backwards and forwards where you
set the criteria to the date being between the date 14 days back and
forewards. Include the fields you need to check if someone has filled in
his/hers hours and present this as a sort of pop up form. You have the
weeknumbers and dates defined so that could be your staring point.
 
Maurice,

I think I know what you mean, but am not sure that will work for me. Perhaps
it will help if I explain a little more what the circumstances will be.

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.

That is why I think that just starting with fixed periods of two weeks one
way or the other is not going to do it for me.

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.

As soon as I figure out how to at least select on the Non existence of a
combination of two fields in a record, combined with a date range, the others
are basically just variations in grouping and sorting.

Thanks in advance,

Patrick
 
Maurice, anyone,

I tried to elaborate a bit more on my problem...

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
HOUR_ID Unique ID
HOUR_WP From WP_ID }Unique combo
HOUR_WEEK From WEEK_ID }
Other fields


So, what I am trying to do is check if there are MISSING records in the table
HOURS for any unique HOUR_WP/HOUR_WEEK combination for any week (Weeknumber)
that fall before next week (there will be no records for dates in the future),
but that fall WITHIN the contract period for that particular employee.
So, if there is no record for the combo HOUR_WP/HOUR_WEEK, while there is a
contract for that WP_ID in that week, I should get a hit.
AM I making myself clear? Am I being overly com,plicated? Is there an
easy/easier way to do this?
Thanks in advance,

Patrick
 
Back
Top