Hello, I have tried to create

[heathlovell]

Hello,
I have tried to create an expression in a query to look up how much time each employee has taken for vacation using a Dsum. I can't get the criteria section to work. I need the employeeID for each record in the query to match employeeID in the attendance table, but I don't know how to do this. Thanks for the help,
Heath

 

[RickSpr]

The answer depends largely on what records your query contains. Please provide names for the query's tables and their key columns; it's easier to communicate using definite names. Also, how are the tables related, and what column contains information about vacation time? Rick Sprague

 

[heathlovell]

I have an Employee table (tblEmployee) which has the fields employeeID (autonumber), lastName, firstName, etc. I have an attendance table (tblAttendance) which has the fields attendanceID (autonumber), employeeID, date, hours, and attendanceType (work, absent, vacation). I have created a query that lists all the employees. The query has the fields employeeID, lastName, firstName from the tblEmployee. I have a relationship between employeeID in tblEmployee and employeeID in tblAttendance. I am trying to use Dsum to add all of the vacation time for each employee. I have tried several different possibilites in the criteria section of the Dsum function, but none have worked. Basically, my function is Dsum([hours], [tblAttendance], [employeeID] = employeeID]). The syntax is not correct but it gives you idea of what I am trying to accomplish. I have a report where I can get this to work, but I am not having any luck in the query.

Thanks,
Heath

 

[RickSpr]

It looks to me like you're on the right track.

You don't have tblAttendance in your query's source tables, do you? You shouldn't, if you're doing a DSum() expression as one of the columns in the result.

The main problem I see here is that you aren't restricting DSum to just the vacation records. I think the DSum function should be more like this:
DSum("[hours]", "[tblAttendance]", "[tblAttendance].[EmployeeID] = [tblEmployee].[EmployeeID] AND [tblattendance].[AttendanceType] = 'V'&quot

Do you think that would solve your problem?

---------------------------

There's an alternative way to do this that doesn't use DSum(). You could include both tables in your query, make it a totals query, and group by EmployeeID, LastName, and FirstName. Include the Hours column, specifying the Sum function on the Totals line, and include the AttendanceType column (but don't check its Show box) with the Totals line set to Where, and the Criteria line set to "V" (or whatever value that indicates vacation time).

The second query would run somewhat faster. In the first query, the DSum function gets run over the whole tblAttendance table for each and every row selected from tblEmployee. That means the same rows get processed many times. In the second query, this doesn't happen; instead, the joined tables produce an intermediate results table, and then this is sorted by EmployeeID. After that, the result table is read sequentially and the running total of hours is calculated during the Group By processing. If either of your tables is large, the time savings can be significant.

Rick Sprague