Data Table Sturcture

[Guest]

I'm new to Access. First project is to creat Db to track employee time. We
need to track total hours used as well as well as "Occurrences". For
example, an employee calls in sick for two consecutive days would result in
two 8-hour blocks of sick time used but would only count as one occurence
(per union contract).

I've creatd one table called "tblOccurrence" and another called
"tblTimeEvents". The form developed populates tblTimeEvents as a subform for
tblOccurrences. In tblOccurrence I have a field for StartDate and another for
EndDate while each TimeEvent also has an EventDate field.

The problem I have is in the work flow with this setup. Most "Occurrences"
are only one day so I frequently end up entering the same date in three
different fields, that seems rather inefficient.

Would it be best to correct this by eliminating the two date fields in the
"tblOccurrences" (and work it out in a report) or is there a reasonably easy
way to default the EndDate in "tblOccurrences"and the eventDate in
"tblTimeEvents" to the first date I'd normally enter, the StartDate field. I
like the way my inital attempts at reports look with the two extra fields (as
far as the data structure,that is; it still isn't very pretty.)

 

[Gary Miller]

Would it be safe to say that each TimeEvent occurs on only
one day? I noted that each TimeEvent record has only one
date field.

Is it also the case that there will only be one TimeEvent
each day?

Would it also be true that an Occurance would consist of
contiguous TimeEvents spread over multiple days or a single
TimeEvent on one day, but if TimeEvents were broken up by
more than a day it would result in a separate Occurance?

If those do hold true then you should be able to drop both
of your date fields in tblOccurance and just use the date in
tblTimeEvents. The StartDate would then be the Min() of the
EventDates and the EndDate would be the Max() of the
EventDates in the tblEvents. Your total days would be the
Count() of your TimeEvents and your total hours would be the
Sum() of the hours in the TImeEvents.


Gary Miller
Sisters, OR



in message
news:[email protected]...

 

[Guest]

Your assumptions are right on. A time event would be limited to a single
date and that is where I hope to track time used in amount (hours) and type
(vacation, emergency, sick, etc). An occurrence tracks time events on
continuous scheduled days (one occurrence could bridge over scheduled days
off).

Your suggestion seems more elegant and efficient than what I've put together.

 

[Gary Miller]

Glad that the idea will work. The situation somewhat creates
the solution in this case.

--
Gary Miller
Sisters, OR



in message
news:[email protected]...