Auto Enter Calc comparing other records

[pcjimenez]

Holy moly...

Having a hard time with even explaining this one...

I need to come up with a calculation based on the following:

Table Has

Username; TimestampIn; TimestampOut; Date

There are maximum of 4 records per user per date, for example:

jsmith; 1/1/08 8:00:00 AM; 1/1/08 9:15:00 AM; 1/1/2008

jsmith; 1/1/08 9:30:00 AM; 1/1/08 11:30:00 AM; 1/1/2008

jsmith; 1/1/08 12:00:00 PM; 1/1/08 1:15:00 PM; 1/1/2008

jsmith; 1/1/08 1:30:00 PM; 1/1/08 3:30:00 PM; 1/1/2008

I have added 40 fields in the following format:

TimeMonIn1; TimeMonOut1; TimeMonIn2; TimeMonOut2 etc up to in/out 4 for each week day

I want to auto-calc in the time fields, such as timeMonIn1=

timestampIn if timestamp is the earliest time in for that user for that date

kinda like:

if timestampIn < each record for that date

gotta be a loop or lookup...

confused yet?

I know I am... gonna go sleep on it...

[aldipalo]

Sounds like you're trying to develop a TimeSheet.

Take a look at this post.

http://fmforums.com/forum/showtopic.php?tid/196814/post/297572/fromactivity/myposts/#297572

You may find enough of what you need to get going in the right direction.

If not, try uploading your file and we can take a look.

hth

If

[pcjimenez]

So confused with how I can display this...

I am starting to think of all kinds of formulas, yet unsure about how to specify the data I want...

Basically I need a time sheet display located on the "Attendance Tab"

Showing the clock-in/out times for the current week per day (maximum of 4 in/out per day)

then show the total hours per week, per month...

I think I can handle the sum of hours for each day/ and month, as long as I can differentiate the clock times...

I have a fake user and time records for two different weeks... however, there will be multiple weeks in the "tblAttendance" for multiple users.... no indexing on the time table, My field number for the tblAttendance is 47 , which I think is way too huge and unnecessary, however, I can't think of a more simplified way of storing the data per record... perhaps with auto-calc formulas for the field...

This is tough stuff...

Thanks for your time and help!!!

here is my DB

Student_Database.zip

[aldipalo]

Did you look at the link I posted? I think it covers everything you want. If not, please explain what you are looking to do that's different from what is there.