This formula doesn't work 2

[gypsi96]

Hi all,

I have this formula which works fine until you have a person who goes over into the next day. I have an employee who came in at 9:00 am left for lunch came back from lunch then left at 5 but then came back same day at 9:00pm and left again at 1:15 am, which also requires a second record for the day to be entered into the BD. So my formula now has him at -19 hours, which is throwing off his total weekly time. Any ideas as to how to correct this?

Thankx

Here's the formula:
DailyTime: Round((TimeValue([Time Out PM])-TimeValue([Time In AM])-IIf([Time In Lunch] Is Not Null And [Time Out Lunch] Is Not Null,TimeValue([Time In Lunch])-TimeValue([Time Out Lunch]),0))*24,2)

 

[ProgramError]

Seems to me that you need to check if there's been a change of date between the start hours and the end hours before you make the calculation and if so, then increase the timevalue(endtime) by 24 somewhere along the line.


Program Error
Programmers do it one finger at a time!

 

[MichaelRed]

A wholy different concept would be to normalize the data. Since your design appears to more-or-less totally ignore the concept, I will not dwell on it, but advise the you to seek the reference manual of your choice re database design and curl up for a few hours of careful reading.

Once you get to a normalized database design, the overall calculation will become quite a bit simpler than the current. On the other hand, relational database design with normalized structures can be a challenge to understand and deploy, so -perhaps- the ignorance-is-bliss current difficulty is a better approach?





MichaelRed

 

[TheAceMan1]

How are ya gypsi96 . . . . .

[purple]Your formula does not allow for multiple Start/Stop times outside of lunch.[/purple] You at least need to be more specific as to how you DB logs these multiple time spans . . .

Also, [purple]if a time span crossovers midnight[/purple], be more specific about what you want the DB to do for you . . .

See Ya! . . . . . .

 

[gypsi96]

Thank you all for your reply's but it doesn't help me much as I am unsure of what to do and where I made my error. This is an access database that was developed to track employee time as close as possible. We only have this happen once and a great while that an employee will crossover from one day to the next but I need to account for that in this formula. If you know of an easier way to write it, I'd greatly appreciate the help.

Thank you

 

[TheAceMan1]

gypsi96 . . . . .

I'll try to ask another way:

[blue]Is your database setup to handle multiple start/stop time spans outside of Lunch?

If so . . . how is this done?[/blue]

See Ya! . . . . . .

 

[earthandfire]

You could try :

If stop time is less than start time add 24 hours to stop time - this wont work if start time is 09:00 on one day and stop time is >= to 09:00 on next day.

In you example therefore the stop time would be 26:15

 

[Eupher]

gypsi96,

Without including the date in your time calculations, I don't see an easy way to account for a work shift that crosses into the next day. MichaelRed is an experienced programmer, and though he might have said it a bit more gently, ;-) I think he is on the right track with his advice about normalizing your data. In other words, it appears what you have now is a table with a field structure that could look something like this:

EmployeeID
Date
TimeInAM
TimeOutPM
TimeInLunch
TimeOutLunch
ExtraTimeIn1 (?)
ExtraTimeOut1 (?)
ExtraTimeIn2 (?)
ExtraTimeIn2 (?)

This is more of a flat-file spreadsheet approach. Your initial question demonstrates one of the difficulties: it can make it hard to query your data without some rather complex expressions. Taken to an extreme, it can render a database virtually useless. A normalized database might have tables with this sort of structure:

tblEmployee
EmployeeID
LastName
FirstName
etc.

tblWorkHours
RecID
EmployeeID
DateTimeIn
DateTimeOut

Where tblEmployee and tblWorkHours are related in a one-to-many relationship on the EmployeeID field. This arrangement makes it *much* easier to extract meaningful information from your database and really is the core of the concept of a relational database.

HTH,

Ken S.

 

[KenReay]

Hi

In addition to MichaelRed's comments reference your basic design, which are highly relevant, it may also be worth reading up on the DateDiff() function in help, despite its name it does calculate time differences in Hours, minutes days whatever

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[gypsi96]

Thank you all for your input on this. I will have to look at my information and see if I can split out the data as recommended without causing to much trouble. Yes, my DB is very simple, I was asked to throw something together quickly and so I did.

I am a very basic programmer and can toss together the simple stuff very easily from scratch but when it gets into the more complex stuff I get a bit lost.

I do have a book on Access that I use as a guide but I just didn't see anything in it that would help.

Thank you all,

Kendra

 

[gypsi96]

Sorry for all the questions, I'm a bit of a newbie here and I have another question on the Tables.

If I split out the table into two, do I include the time data in the WorkHours one or create yet a third table for that?

Thankx

 

[Eupher]

gypsi96,

It really depends on how you wish to organize your data, but in my example I envisioned the time data going into the WorkHours table. So for tblWorkHours you might have data entries that look like this:

[tt]RecID EmployeeID DateTimeIn DateTimeOut
-----------------------------------------------------
1 23 5/1/2005 8:00AM 5/1/2005 11:30AM
2 23 5/1/2005 12:30PM 5/1/2005 5:00PM
3 23 5/1/2005 7:30PM 5/2/2005 1:30AM
4 24 5/1/2005 9:00AM 5/1/2005 3:00PM
5 23 5/2/2005 8:00AM 5/2/2005 12:30PM[/tt]
etc.

With this layout you can easily query and sum all hours for a given employee within a given date range.

Ken S.

 

[TheAceMan1]

gypsi96 . . . . .

There are other considerations when setting up your tables. For instance . . . [purple]how you want to view your forms and reports.[/purple]

Since you dealing with employees I'm sure you want to see hours worked per week. Now, you can query to view any week you like or you can add another table to make things easier:

[blue]tblEmployee
EmployeeID PrimaryKey
LastName
FirstName
etc.

tblWorkWeeks
WorkWeekID PrimaryKey
EmployeeID ForeignKey to tblEmployee
WeekStart
WeekEnd

tblWorkHours
RecID PrimaryKey
WorkWeekID ForeignKey to tblWorkWeeks
DateTimeIn
DateTimeOut[/blue]

This is just an Idea of how table structure can change according to your intent of view. There are many others, most determined by you! All this is involved in normalizing tables.

[blue]If I split out the table into two, [blue]do I include the time data in the WorkHours[/blue] one or create yet a third table for that?[/blue]

Your calculating WorkHours, and calculations are rarely stored in a DB mainly because its wasted space.

A few references:

Normalizing Tables

Table Relationships

Fundamentals of Relational Database Design

See Ya! . . . . . .

 

[Eupher]

AceMan1 is right - I sorta misunderstood the question. You shouldn't normally store calculated data in your tables.

Ken S.

 

[gypsi96]

ok, The calculation is not in the table itself but in a query, sorry I should have mentioned that.

When I ran last weeks reports I came up with an employee that had neg. hours and it was due to him coming back in after 9 pm and not leaving until 1:15 am the next day

The way my table is current set up is:
ID
Date
Employeename (Last,First)
TimeInAM
TimeOutLunch
TimeInLunch
TimeOutPM
Comments

I have it linked to a table with the employee info in it. I then have a query that I run qry_timecal and I have two reports that use it, Weekly time report full detail with Totals and Total Time Report Summary Per Employee by Week.

These reports are needed by the Director of Ops.

I guess I should be more specific when I ask for help. Sorry.