TIME field calculation help please

[scott munkirs]

I am completely stuck on my issue: I have created a database fo
employee payroll. Time sheet, percentages and all. The issue I a
having is with the timesheets. I have 2 fields per day.. Dayin an
Dayout. I then in a query created 2 other fields per day for hrs. Fo
example; MonIn: 8:00 am. MonOUT: 6:00 PM. The 2 fields below tha
would be 10 (total hrs) and 9.50 (half hour deduction for lunch).

I would then have a running calculation for total hrs worked at week
end. Reg hrs and OT. Everything works except when they have a da
off

If I leave the DAYIN and DAYOUT blank then my running calculations fo
hrs week do not add up. I must always put 12:00 pm and 12:00 pm in th
fields to show day off. I then get 0 and 0 for hrs that day (the
fields below). THis is a workaround but not to what I like

How do I allow blank time to indicate days off and still keep m
running calculation for the week. So for example: I would have a
employee clocked in 8:00 am and clocked out 5:00 pm Monday thr
Friday and OFF Saturday. Tot R would show 40 hrs and TOT OT woul
show 2.50. The field for Saturday would be left blank. If I do i
this way right now, TOT R would show 40 with no TOT OT shown at all

Please help!!! I really do apologize for the long winde
explanation

Scot

 

[Marshall Barton]

I am completely stuck on my issue: I have created a database for
employee payroll. Time sheet, percentages and all. The issue I am
having is with the timesheets. I have 2 fields per day.. Dayin and
Dayout. I then in a query created 2 other fields per day for hrs. For
example; MonIn: 8:00 am. MonOUT: 6:00 PM. The 2 fields below that
would be 10 (total hrs) and 9.50 (half hour deduction for lunch).

I would then have a running calculation for total hrs worked at weeks
end. Reg hrs and OT. Everything works except when they have a day
off.

If I leave the DAYIN and DAYOUT blank then my running calculations for
hrs week do not add up. I must always put 12:00 pm and 12:00 pm in the
fields to show day off. I then get 0 and 0 for hrs that day (the 2
fields below). THis is a workaround but not to what I like.

How do I allow blank time to indicate days off and still keep my
running calculation for the week. So for example: I would have an
employee clocked in 8:00 am and clocked out 5:00 pm Monday thru
Friday and OFF Saturday. Tot R would show 40 hrs and TOT OT would
show 2.50. The field for Saturday would be left blank. If I do it
this way right now, TOT R would show 40 with no TOT OT shown at all.

You actually have fields in the table for each day of the
week? If that's the case, then the best thing you could do
is study up on database normalization and redesign your
tables to avoid problems like this.

But, facing reality, that's probably not going to happen so
I suggest that you try using the Nz function. For example,
you can automate your workaround by changing each of those
fields to something like:
DayIn: Nz(DayIn, #12pm#)

However, having an entry that results in a zero time worked,
is ging to throw your percentage calculations off, If
that's a problem, you have to come up with something more
complex. Eventually, these workarounds will snowball out of
control and you'll have to redesign anyway. Remeber, it's a
lot easier to redesign earlier than later.

 

[scott munkirs]

I do appreciate the advice but not knowing Access like an expert
need to ask how or where do I use the NZ that you mentioned? o
better yet, How do I set up a table so I do not need fields for ever
day of the week?

If that is hard to explain do you know of a place for me to see how i
is done? or a possilbe Access database file to show and example

Again, thanks for the help

Scot

 

[Marshall Barton]

I do appreciate the advice but not knowing Access like an expert I
need to ask how or where do I use the NZ that you mentioned? or
better yet, How do I set up a table so I do not need fields for every
day of the week?

If that is hard to explain do you know of a place for me to see how it
is done? or a possilbe Access database file to show and example?

You haven't explained where the calculation is needed so I
can't be specific about where you need to use the Nz
function. My earlier suggestion is just to use a query to
convert your existing data to the manual workaround you
mentioned. The query would be based on your table with all
its fields dragged to the query's field list and changing
each of the problem fields as described.

This is going to be an extremely tesious task and I do not
think this is the best (and maybe not even workable)
approach, but you could give it a try and see how severe any
additional issues are. Personally, I think your time would
be better spent learning about normailzation and redesigning
the tables so you don't have to continue to create
workarounds every time you run into another one of these
problems.

You can start reading up on Normalization by looking at this
KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;100139

You can Google for tons more on the web.

Once you get a basic idea of how to redesign your tables,
ask specific questions about table design in the
tabledbdesign newsgroup.