Date Done and Date Due problems

[Lostguy]

All,

Hopefully, this is the last piece to this database I have been working
on with y'all's help. Here's the setup:

tblEventType (EventTypeIDpk, EventName, EventPeriodicity) (Dental Exam–
every 365 days, Medical exam-every 730 days, Counseling every 182
days, etc.) (All the events)

tblEventWhen (EventWhenIDpk, EventTypeIDfk, EventDate,
EventLocation) (Event X happened on 1-1-09 at Hospital) (where and
when the event happened)

tblEmployee (EmployeeIDpk, LName (Smith, Jones) (Who can attend the
event)

tblEmpEvent: (EmployeeIDfk, EventWhenidfk)(Which employees were at
which events)



1) I am trying to do a report showing (for a given person) what
event is done and what still needs to be done. I can set up the “done”
part but I can’t get the “to-be-done” part because those items do not
have an EventDate. (It just needs to show list of all the possible
events and a date next to the ones that he did. It’s the events
without dates that aren’t showing up.)

2) DateDiff needs a date to do its calculation. With Jones doing
his Medical on 1-1-09 and his dental on 12-2-09 and both of those
dates are in the same “EventDate” column, is there a way to do
“DateDiff the EventDate if the EventType is X”?? (I am trying to use
the EventDate to show when the Event is due based on a periodicity.)

3) Is this set up the best way?

I appreciate all your help!

V/R Lost

 

[NevilleT]

Hi
Just trying to get my head around what you are doing. Is tblEventType a
generic type of event not related to patients? For example are you setting
up a table to say for everyone, dental happens every 365 days? If so, how do
you create records for tblEventWith? Do you set them up manually?

This leads me on to another question. Do all employees have to have a
dental Examination every 365 days, and if so, how do you create the first
record? What is the trigger for their events to start?

Neville Turbit
www.projectperfect.com.au

 

[Lostguy]

NevilleT,

a) Every day, you have a to-do list.
b) Some items are just done one-time, others weekly, monthly,
annually, etc.
c) You have 40 employees that you are tracking their to-do list items.
d) You would like to be able to print out reports showing what they
did when and what they still have to do and what is overdue.
e) Some items are by just one person on that date; other items are
done at the same date/time by multiple people (monthly training, etc.)

tblEmployees is the list of 40 people. (Bob, Bill, etc.)
tblEventType is the To-Do list item and how often (in days) in
repeats. The user can update this table via a form.(Arrived to work,
annual evaluation, annual ethics classes, annual dental exam, biweekly
timecard input, etc.)
tblEventWhen is when and where that To-Do list item was accomplished.
(On 1-1-90 at the Oak Conference Center, there was a session of sexual
harrassment training) (The user types in the where and when and
selects the To-Do list item via combobox tied to tblEventType)
tblEmpEvent relates the employees records to the EventWhen records
(Who did what To-Do list item).

The user opens up the form, types in the date, location, and picks the
to-do item from a dropdown. In the continuous view subform below, the
user selects via combobox the first person and any additional people
that did that item at that place on that day.

My problems are:

1) I don't know how to set it up so that on a report, it shows me (for
a particular employee) all the possible to-do list items from
tblEventType AND the date accomplished for the ones that are done and
the date of when it is due. So for Bill, it should be a list of 60
items with 30 of them having a DateDone and DateDue. The other 30
would be for management to bug Bill about to find out why he hasn't
done them.
2) Setting up the DateDue for each To-Do list item. That would be
based on when that item was done. But each of the 80 items (unless
they were a one-time thing) would have their own DatesDue based on the
EventPeriodicity.)
3) I still am not certain that the setup above is the best way to
arrange the tables.


BTW, we aren't a hospital. We are a military organization. We track
readiness items (required DoD and local training, flu shots, annual
dental exams, etc.) for all employees. We would like to be able to
print out this list of "done" and "not done" items for each person to
have that at management meetings.

HTH and I appreciate your assistance!

VR/Lost

 

[NevilleT]

Hi
It is getting late here in Australia. Almost 11 so I won't be responding
again tonight. If I understand it, I join the happy band and have to have a
dental examination. There is no record of that until I actually create a
record and say I am going for a dental exam next week. In other words, the
record is not created by the system.

Once I have a dental exam, the system has the potential to say in another
365 days I am due for another exam. Not sure if this happens
programatically, or if I have to create a record manually. If the initial
date is there, and there is a frequency in tblEventType, I could create the
record programatically. If I get to 400 days since my last exam, there is no
record to track the fact I missed my appointment. Also, if I skip the first
exam, there is no record to build on programatically. I can't add 365 days
to nothing.

One suggestion is that when you set up a new user, you automatically create
a set of recurring event records for that person. Not sure how you set dates
but we can come to that. Maybe they are all today, and you manually adjust
them to suit the individual.

If you create a module that runs every day, you can create new appointments.
For example, search all dental exams in the last year and if they are due to
be repeated in the next month, and a record does not exist, create a record
for the next dental exam.

Next you need to add a date completed, or checkbox for completed field on
the tblEventWhen table. You can then see it was due on 1 July, and it was
completed on that date (or maybe another date if you use date). That allows
you to run reports of what is complete and overdue. Looking at requirements
it should be a date so you can report on who did what when.

I think the key thing is that tblEventWhen becomes a record of both
scheduled date, and date completed. It is not just completed date.

Leave another post and I will get back to you tomorrow.

Neville Turbit
www.projectperfect.com.au

 

[Lostguy]

This is what I am having trouble with (Forget all the other stuff
above for now. This is fundamental stuff that I need help with.)

Who when Were:

Bob and Doug reported to Work on 1/1/09.
Bill reported Home on 1-1-09.
Bob and Bill reported to Home on 1-2-09., etc.

So, I have a tblEmployee (EmployeeIDpk, FName) and a tblEvent
(EventIDpk, EmployeeIDfk, EventLocation, EventDate).
I set up the Date and Location on a form, and then the employees on a
continous subform, but I can never get it to work right.

I can't get the tables/forms set up to produce these 5 records in the
tblEvent:
EventID-EmployeeIDfk-EventLocation-EventDate
1: Bill-Work-1/1/09
2: Doug-Work-1/1/09
3: Bill-Home-1/1/09
4: Bob-Home-1/2/09
5: Bill-Home-1/2/09
etc.


VR/ Really lost

 

[NevilleT]

Hi
Try this download I. I tried to set it up from your description. It is a
datasheet but you can make it a continuous form if you want. Is this what
you are trying to achieve?
www.projectperfect.com.au/downloads/temp/LostGuy.zip

Neville Turbit
www.projectperfect.com.au

 

[Lostguy]

NevilleT,

Yes, that is the start.

If you look at post #5 above, I changed it up a little and combined
the EmpEvent and the tblEventWhen into one table: tblEvent (EventIDpk,
EmployeeIDfk, EventTypeIDfk, EventLocation, EventDate).

I noticed that you added a tblLocation (lookup table for locations).

But I need to track the employees who were at those events via a
continuous subform.

So for the form, you would enter the data, enter the event, enter the
location (which is what you have on the form you sent), but then enter
the employees who were at that event. So for the first date, there
could be only 2 employees there. For the next date, there could be 20
employees.

One of the tables should be storing Date-Place-Event-Employee, but
that isn't happening. (See post #5).

I really appreciate you working with me on this!
VR/Lost

 

[NevilleT]

Try the latest version in the download. It has a main form datasheet you
double click to open the individual form. Think one of the issues you might
have come up against is that you cannot add a subform or datasheet in a
continuous form.

Let me know how this works for you

Neville Turbit
www.projectperfect.com.au

 

[Lostguy]

NevilleT,

It has taken me a while to get started on figuring out how you worked
this.

1) Form1 does not seem to be used for anything. Double-clicking both
fields on Form 3 brings up Form 2. Is Form1 extra?

2) My original question was how to set up the report to show all the
things that Jones did, and DIDN'T do based on the periodicity.

So Jones did Medical on 1-1-09, and has Medical due in 365 days. He
still needs to do his Dental. He did his counseling on 1-2-09, but it
is overdue based on a 6-month periodocity. Blank and overdue fields
would be colored red to flag them. So the report should look something
like:

Jones Done Due
Medical 1-1-2009 1-1-2010
Dental <blank> <blank> (colored red because not done)
Counseling 1-2-2009 7-2-2009 (colored red because overdue)

This whole thing posting has been how to set this report up so that I
can take it to management and say "Here is Jones. His Medical is not
due until 2010, but all his other stuff is due now beause he hasn't
did it (or it has exceeded its periodicity). The red on the report
will go away when he gets that other stuff done."

Can this be done in this setup?

This is good stuff!

VR/Lost (but getting assisted out of the confusion)

 

[NevilleT]

Hi LostGuy

OK. Form 1 was version 1 so it is now obsolete. Would start with Form 3 to
find the event then doubleclick to drill down to form 2.

Your problem with reporting is that you are trying to report on something
that isn't there. If there is no record, you cannot find it. You need to
create the missing information before you can report on it.

Going back to an earlier post, if you create records for scheduled events,
then you can report on whether they are completed or not. That means a
number of things.
- You have to create the initial record in the series. Smith had a dental
appointment on a certain date.
- You need to have some code that says if Smith attended a dental
appointment on this date, he is due for his next one on this date + 365 days
later, so I will create a record for an appointment on that date.

The code to do that is not too complicated. Create a table called
tblLatestBatchRun with a field for date run. Every time someone opens the
program, if date run is before today. If it is, run the batch to go through
all records to see if any new appointment records should be set up. Once
done, add a record of today's date in tblLatestBatchRun. Can expand on this
if you want.

If you played around with a query, you might be able to get a result in
terms of overdue appointments but it still relies on the first record being
in place.

Alternatively, if everyone has to do all the same checks, you might be able
to run some code and create a temporary table to use in the report. Would
still rely on some sort of date to kick off - like a date joined. All very
messy.

Let me know if all the records in tblEvent apply to everyone. In other
words, does everyone have to go through all the checks. If not, you can
forget this approach.

 

[Lostguy]

NevilleT,

This morning, I figured out what you were saying about the events.
When a new person arrives, you need to know which events are
applicable to each person so you can start their clock.

So, here's the information that might help.

40 employees, of which 10 are civilian, 30 are military.

20 events (for example). 5 applicable to just civilian, 5 to just
military, and 10 to both.

The 40 employees are divided among 4 Departments (A, B, C, D) . Most
of the events are applicable to all Departments, but a few are
applicable to just Department A.

As I see it, there can be a TblEvent with EventIDpk, EventDesc,
EventPeriodicity, CivorMilorBoth, ApplicableSection.

I guess you can also create just a tblMilEvent, tblCivEvent,
tblDeptAEvent, etc.

So, when the new person arrives, you find out whether he/she is
civilian or mil and what Department. Based on that, that gives them a
predetermined list of Events.
Then, for his/her report, all the DatesDue would be blank and red-
colored because nothing has been done yet. As the events are done,
their due dates would change based on EventPeriodicity.

Hope this helps. I am still looking at what you sent. Thank you for
everything!

VR/ Lost

 

[NevilleT]

Hi
Getting there. Would suggest in the table events, two yes/no fields. One
for military and one for civilian. Easier to pick events if you just filter
on the one field. Show me all military events using the military checkbox =
true. Show me civilian events using the civilian checkbox = true.

When you add a new person, you can have some code that checks if they are
military or civilian, sets up records for the first set of records with a
date of maybe today, or in a week time, or a month. You can then go in and
manually adjust the dates.

If you have a completed checkbox on the tblEventWhen record, you can use the
after update event to create a new record using the EventPeriodicity. You
will need to first check if the new record already exists.

One technique I use is to have a completed checkbox on a form that not only
stores the fact it is completed, but changes the visible property of the date
completed to true. In other words, when they tick the completed box, they
see a date completed textbox to fill in. Bit of double recording but you
need the date for reporting purposes, and it is useful to have a boolean
field to check for completed records. The user thinks they are just
displaying the date box to fill in, but they are actually recording
information for you. You do need a back out option in case they untick the
box. something like
If Me.chkComplete = False then Me.txtDateComplete = ""

Let me know how that goes.

Neville Turbit
www.projectperfect.com.au