Sum based on criteria

[pcjimenez]

1 table tblHours

5 Fields:

nameUserName : txt field

timeIn : timestamp

timeOut : timestamp

date : date in format 01/01/2008

totalHrsInterval : Auto-Calc Field "Hour (timeOut-timeIn) & ":" & Minute ( timeOut-timeIn )"

sample record:

jsmith010188; 8/18/2008 11:13:05 AM; 8/18/2008 2:45:11 PM; 8/18/2008; 3:32

I want to Sum the totalHrsInterval fields that exist for a specific date such as

Sum(totalHrsInterval {where date = DateCurrent-1}

I would like to simply display this on a form without actually having an entirely new table...

I thought about a merge field, but I don't think I can calculate in a merge field, can I?

[mr_vodka]

Why dont you just use a calc field with Time result of timeOut - timeIn? A summary field that adds that calc should give you the total.

You can then also have an unstored calc that is Get ( Current Date ) - 1.

Now when you create a new table occurence and use a self join from this new calc field to your DATE field, you can now reference the summary field from the new self join table occurence and it should display all the totals from the previous day.

[pcjimenez]

man, I totally forgot about summary fields...

So I have the summary field showing the running total of the "totalHrsInterval" when grouped by "date"

then I have another field displaying that running total in a "HH:MM" format {hour(totalHrsInterval)&":"&minute(totalHrsInterval)}

However, I am trying to figure out if I am going to have to create a new table holding daily totals per user...

also, I am going to want to call the total for each weekday for the current week...

At first I was thinking a case() function, however, I am not sure how I can use a criteria, but this is what I was thinking:

#For a "totalHrMonday" field

case(

#Check day name of current date

dayName(DateCurrent)="Tuesday";

#{here's where I struggle with calling on criteria}

#get value of Monday's Total Hours

get totalHrsReadable where date = CurrentDate-1;

#Check day name of current date

dayName(DateCurrent)="Wednesday";

#{here's where I struggle with calling on criteria}

#get value of Monday's Total Hours

get totalHrsReadable where date = CurrentDate-2;

#Check day name of current date

dayName(DateCurrent)="Thursday";

#{here's where I struggle with calling on criteria}

#get value of Monday's Total Hours

get totalHrsReadable where date = CurrentDate-3;

#Check day name of current date

dayName(DateCurrent)="Friday";

#{here's where I struggle with calling on criteria}

#get value of Monday's Total Hours

get totalHrsReadable where date = CurrentDate-4;

This would be much easier if I could simply do this with a SQL select statement with a "Where" clause

I also thought I may have to create scripts to get this to work....

[mr_vodka]

Ok lets start over and approach this a little differently. Tell us exactly what you are trying to achieve and we will see if there is a better way to approach your issue. Is this for a report? If not, can you live with a report?