Overdue item Report

[cpocpo]

Hello all!

I have a tblEmployee with date fields LastDentalExam, LastMedExam,
LastEOTrng, etc. (30 of these) for each person.

I run a query using DateAdd for each of those fields which figures out
the due dates for each.

I use that query for a overdue item report, but that report prints out
ALL items and their due dates.

What I would like to do is after the startup screen, a report would
popup showing that "for person X, the following items are coming due
(within 15 days)" and "the following items are overdue". How do I set
this up to look at every DateAdd field, determine what items are close
to due or overdue, and generate a list??

Appreciate the guidance!

V/R

LostGuy

 

[Guest]

Change your table structure.

tblEmployee –-
EmpID – autonumber – primary key
LName
FName
MI
Etc.

Examinations –-
ExamID - autonumber – primary key
Exam – text – name of exam
Freq – integer – month intervals – use zero for one time.
Recur – integer - 1- next due based on last completed 2- next due based
on last duedate

EmpExam –
EmpExamID - autonumber – primary key
EmpID – integer – foreign key
ExamID - integer – foreign key
DueDate – datetime
CompDate – datetime
Rmks – memo

Use a form/subform for employee/exams. Use combo in subform to pick
required exams.

Use append query to add EmpExam records. Use
DateAdd(“mâ€, [Freq], IIF([Recur] =1, [MaxOfCompDate], [MaxOfDueDate])

 

[Guest]

Your problem arises from a design flaw. By having multiple columns you are
doing what's known as 'encoding data as column headings'. In a relational
database data should be stored only as values at column positions in rows in
tables; this is known as the 'information principle'.

Your table should be decomposed into three related tables as follows.

tblEmployees----<tblEvents>----tblEventTypes

tblEvents would have a foreign key column, e.g. EmployeeID, referencing the
primary key of tblEmployees along with columns EventDate, EventType. The
EventType column would be a foreign key referencing the primary key of the
single column table EventTypes.

With this correctly structured set of tables it’s a simple matter of joining
the first two tables in a query and restricting the query on the EventDate
column to produce a list of all overdue/ coming due events per employee, e.g.

SELECT FirstName, LastName, EventType,
IIF(EventDate < DATE(), "Overdue", "Due within 15 days")
AS DueStatus
FROM tblEmployees INNER JOIN tblEvents
ON tblEmployees.EmployeeID = tblEvents.EmployeeID
WHERE EventDate < DATE() + 15;

Base your report on this query, and group the report by employee.

You can easily populate the tblEvents table with a series of 'append'
queries once you have set up the two new tables. First create the
tblEventTypes table and insert the 30 rows, one for each event type. Create
the tblEvents table. Create a relationship between tblRmployees and
tblEvents, enforcing referential integrity. Create a relationship between
tblEvents and tblEventTypes and enforce referential integrity. Then execute
30 'append' queries, inserting the event type as a constant and the
employeeID and dates from the relevant columns in your current employees
table, e.g.

INSERT INTO tblEvents(EmployeeID, EvenDate, EventType)
SELECT EmployeeID, LastDentalExam, "Last Dental Exam"
FROM tblEmployees;

Note that the constants such as "Last Dental Exam" must match exactly the
values in the EventTypes table or referential integrity will be violated and
the INSERT operation will fail.

Once the new tables are populated you can delete the 30 redundant columns
from tblEmployees.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

Hello all!

I have a tblEmployee with date fields LastDentalExam, LastMedExam,
LastEOTrng, etc. (30 of these) for each person.

Then you have a misdesigned table.

If each employee has many appointments, and each kind of appointment applies
to many employees, you have a many to many relationship. "Fields are
expensive, records are cheap" - a better design would have THREE tables:

Employees
EmployeeID
LastName
FirstName
<other biographical data>

AppointmentTypes
AppointmentType <Primary Key> <e.g. "Dental Exam", "EO Training">
<any info about this type of appointment in general, e.g. repeat frequency>

Appointments
EmployeeID <who is involved>
AppointmentType <what they did>
AppointmentDate <when they did it>

This gives you one RECORD for each appointment; you can create a report
joining Appointments to Employees with criteria on the appointmentdate to
identify what they've done and what they need.

John W. Vinson [MVP]

 

[John]

Hello all!

Still missing something fundamental.

I have tbl Employee with 3 fields: EmployeeID (auonumber, PK), LName (text),
and FName (text).

I have tblEventType with 2 fields: EventType (Text, PK) and Periodicity
(Number)

I have tblEvents with 3 fields: EmployeeID (number), EventType (Text), and
EventDate (Date/Time). No PK assigned for this table.


Relationships: EmployeeID (from tblEmployee) to EmployeeID (from tblEvents)
one-to-many, referential integrity, no cascades, join type 1

and EventType (from tblEventType) to EventType (from tblEvents)
one-to-many, referential integrity, no cascades, join type 1


Created a form with the wizard pulling LName and FName from the first table,
EventType from the second table, and EventDate from the third.

This gives me a form with the FName and LName and the other two in a
subform.

Now, I can get the name to update but when I enter the date in the subform,
I get an error "ding" with every keystroke. I also get the "Changes not
successful because they would create duplicate values in the index" error
and the "field cannot be updated" error. There is also errors with letting
me enter an eventtype..

??

Lost for sure Guy