VBA sql updates too many records in query

[arfox]

Hello all-

I have what I thought was a simple programming problem... but I think I am going about it the wrong way as the solution has turned into a disaster.

Basically I have a form called "frmworkCallSub" that users can use to enter scheduling information for employees. There is an employeeID field, Date, StartTime(in Short Time), EndTime(in Short Time), and other details.

I have on the form a calculated control to find the total time for each individual record, or "work call"
TotalTimePerCall=Format([StartTime]-1-[EndTime],"Short Time")

What I am trying to do is calculate the total number of hours for each employee over a specified date period.

I set up a query taking the "TotalTimePerCall" and EmployeeID, and an empty field called "HourTotal" I then wrote a function that loops through each employeeID and calculates a running sum. (HOURS worth of work on freakin' date/time syntax mind you!) However, when I try to run the sql statement in the function to update the "HourTotal" field- it updates that field for EVERY record in the query. i end up with the total for my first employee in EVERY employee.

Obviously I'm doing something wrong. Is there a way to only update the current record?

Help??
Thanks!

 

[ByteMyzer]

Would it be possible for you to attach a copy of your work thus far?

 

[davidmound]

don't try so hard

It's much easier than creating a loop.

But first, let's clarify:
If the calculated field is in the form only, than you've recreated it in the query?

Your query should have the following fields:
EmployeeID, TotalTimePerCall, DateField
(and anything else you want, but you don't need the HourTotalfield)

First, you want to set the criteria for the date field (which is whatever field you're using to determine the "specified date period" you talked about). In the Criteria line for that field, either put the range if it's static or prompt the user for the start and end dates if they vary (let me know if you need help with this).

Then you want to click the Totals button on the toolbar (the greek Sigma sign) looks like a strange E)) which gives you a "Total:" line in the query. In this line, select GROUP BY in the EmployeeID field and SUM in the TotalTimePerCall field.

This will give you a query that lists each employee that has worked in the specified period of time and will show the total time worked for that period. I would need more details to literally walk you thru the date criteria entry,(becuase I don't know what field(s), criteria, etc you're using to determine the 'specified date period').

Regards,
David