Negative Calculations

[Jima330]

I have a table that has these fields: EMPS, DATE, START_TIME, END_TIME, TASK_ID.
I need to have a report that calculates the Length of time from START to END (was that obvious) and give me a total of all ID's for a given day for a given emp.
Easy enough simply using the SUM calc on the Report Wizard. My problem is that some of the IDs need to be subtracted from the total.

I have a situation in which we track total work day time and subtract out the time an agent was not logged in. But in some instances the agent is replaced by another agent and goes on break or home or lunch. When this happens I need to subtract the time form the Logged In time.
More Exlanation:
WE have a call center in which all agents time logged in is tracked and reported as a total amount of time for the period (for my purposes a 24 day). If an agent is logged in all day (minus sceduled breaks) they should match up exactly with the time they were scheduled to be logged in (a constant). When the agent takes time during the day to do other tasks that do not require being logged in, this time must be added to the logged in time to match their scheduled time (so we know where they were). When off phone time happens the agent fills out a form with Start and End times as well as an ID for what they were doing. I have successfully built the DB to calculate the difference in the Start/End times and add all events together for the day (the simple Report Wizard way). I can then set up a report to add this total to the Logged In time as reported to the system and give me a total to match to the Scheduled Time (the constant) to see if there are any problems with people being away from their position for unaccounted time.
The problem arises when One agent takes over for another. When this happens the person who left is logged in but gone on some other duty or break. They are also being shown as logged in by the system. My problem is how to subtract the time for the rest of the call that they were not there but logged as there. When the agent leaves they fill in the start time and when the second agent finishes they fill in the end time. With both agents names I can add the second agents time into an Event but am stumped on how to subtract the first agenst time from Loggin In time.

I can either subtract the time from the Logged In time (not under my control) or subtract it from the time calculated in the report before I subtract that from the logged in time (effectively getting the same result). The problem is how do I get the report to add some IDs and subtract others? There are really only three IDs that need to be done negatively so a Code solution would not be out of the question. Dos this make more sense? I know this sounds like a query but god knows I don't know where to start on the negative calculations.

 

[scriverb]

You can add an additional field to your transaction. The default would be a +1. But for the transaction where you want to sub the time make it a -1. In the query for your report in all cases just multiply the elapsed time by the multiplier field. This will provide you with plus values most of the time and a few minus values. You could actually do that right in the report detail section. Just have a control for both the time and the multiplier.

Let me know if you need any more assistance with this. Bob Scriver

 

[Jima330]

Bob,
I'll admit I hadn't thought of something as simple as that. I am not completely without Access knowledge but it is something I do infrequently. Can you describe for me how I would set up the query so that it would differentiate which records to multiply
+1 and which to make -1? I Thought there might be an SQL way to use a CASE WHEN statement but have been unsuccessful in trying that (I don't know a lot about SQL). Forgive me if this all sounds unorganized.

 

[Jima330]

In actuality this did not work. This will reflect a negative number in the Number format but in the Short Time format it looks and acts exactly the same as positive number. I am unable to use it as a negative number in an equation. I have that since I am working with durations I can convert the short time format into a new column as minutes and use that to figure positive and negative differences. Anybody know how to convert short time (02:00) to reflect minutes as a whole number (120)? I saw this some where but can't find it now. Where would I put this in my column on my query to convert the time from another column in the same query? OR can I just do it in the column I wish to convert from?

 

[Jima330]

Just a post to close this out. I found the DateDiff function and applied it to an event and all works great! When I built the expression I used the "n" for the time format and this returns the total time in minutes as a whole number. This is then available to be manipulated (mathematically) as any number would be. I include my expression here on th off chance this may help someone else if they search for it.

DateDiff("n",[Forms]![testadd]![StartTime],[Forms]![testAdd]![EndTime])

testadd being the form it is contained in, starttime and endtime being user inputs on the form. I put this in a macro and applied the macro to an after update event in the end time text box (pointing it at a text named Total Time on the form). I then set the Total Time text box to control source a field in my table that I created for that purpose. This works because the total is forever tied to the other 2 time fields in the record and if one of them changes after the record has been saved it will need to be deleted anyway, so no problem with the hard number on the form. This also allows some very unusual options. If the end time is earlier than the start time the calc will return a negative number, so if you need to subtract DateDiff you can do it in htis fashion. Thanks toall the people who gave advice on my entire database, I get great help from Tek Tips and would really be lost without it.

Jim

 

[scriverb]

Jim, I am glad that have solved your problem. I am sorry about not responding to your posts here as somehow they have fallen through the cracks. Normally I make sure to respond quickly. Don't know how this happened. Going back through my emails to see what happened.

Good luck with your database.

Bob Scriver