**Puzzled**-employee absent report?

[crimmelcp]

To all:
I have a table with employee time entered.
could have from 10 to several hundred employees.
each employee has at least 1 record for each day that they work.
no records for the days that they are absent.
how can I print a report showing each employee and the days that they missed?
I do have a table built for each year that shows each workday and holiday.
the year folder has only days no employee numbers.
I cant do an append unique because it would only do 1 employee.
Any ideas?
Charlie Crimmel

[Tom Cone Jr]

RE: **Puzzled**-employee absent report?

Charlie, reporting on data that 'isn't there' can be tricky. There are probably several approaches you could take.

One approach would be to build an intermediate table whose only purpose would be for the report. This table would contain employee identifier fields and the date missed work. Each record would correspond to a single employee and a single missed day.

To build the intermediate table, write a script which

1) steps through the table of workdays for the year, one day at a time
2) for each day
-- step through your master list of employees
---- for each employee check the table of employee time worked
---- if there's a record of time worked for that day skip it
---- otherwise, write that employee and the date to the temp table
3) repeat cycle until all days of the year are processed.

-- tom

[Jim Chapman]

RE: **Puzzled**-employee absent report?

Hi Charlie,

You could do this with your current structure, but I don't know how the reporting would like for sure as I've never tried this.

Link your employee table to the table showing workdays and holidays. This link (one to many) would be such that each employee record would link to all records in the workday table (you can do this easily with an expression). Now link the actual work records to the workday table linking on the date fields ONE TO ONE.

Now, the unknown part. This stucture will allow you to print a report for each employee showing all days in the year and which ones where worked. You should be able to come up with a filter (as you've linked one to one the workdays table with the worked table) that will only show those days not worked as alpha basically treats a one to one link as the 'same' table.

Never done this, just thinking out loud,

Jim

[Jim Chapman]

RE: **Puzzled**-employee absent report?

Charlie,

This isn't going to work as I've described it as it would link all worked records to the workdays and holidays table not discriminating different employees. This grandchild link would have to include an employee ID. I don't know how you are going to get this is it wouldn't be present in the workdays and holidays table. You could link in reverse and might be able to come up with a way to do this with your current structures however this would appear to give you 365 days and which employees worked on a given date. In other words it would be date centric rather than employee centric.

Sorry, spoke before thinking enough,
Jim

[JohnZaleski]

RE: **Puzzled**-employee absent report?

Charlie,
Reporting on non events can be tricky business in a database application. An app created for a client of mine collects time with job codes etc. We have different job phases for many kinds of non productive work. You could create a job or phase for absentees. You could have a routine like Tom described that would create a permanent record for each active employee for each absent day ( zero hours worked ). This would not only lend itself to easy reporting, but could be seen immediately in an employee inquiry form.

[Robert Adler]

RE: **Puzzled**-employee absent report?

charlie:

I do prepare a report similar to what you are looking to do. I create a table that contains one entry for each day of the month. I process the data for the month and populate the records based on the day of the month. a second scan of the file indicates which days are empty. those are the exceptions you are looking for. I will look for this evening and send it to you.

bob adler