Hello,

I am using qryOutstandingWork2 as a recordsource for a form. The form is used to update tasks worked by a department of users. The basic schema is as follows:

tblIncomingWork - one record per task per day
[Work Incoming ID]
[Signon ID]
[Task Name]
[Amount Incoming]



tblWorkCompleted
[Work Completed ID]
[Work Incoming ID] FK to work table
[Work Date]
[Work Amount]

The interface has been designed to allow the users to first input their incoming work for the day. They then open the oustanding tasks form which displays all outstading tasks and allows them to log worked tasks. There may a number of items coming in for each task each day [Amount Incoming] and the users may complete all or some of the oustanding tasks per day [Work Amount. The outstanding tasks form may then hold outstading work for today and tasks from previous days which haven't yet been cleared down.

I am working out what is outstanding using the queries below which do work but are very slow. I expect this is down to the DSum function I am using to grab the sum of the [Worked Field], I resorted to the DSum function as I couldn't find a way to use SQL and still keep the recordset updateable. Maybe this could be done using subqueries but I couldn't figure out how to do this. I also suspect I could combine these queries but again my SQL skills are not up to scratch!

qryOutstandingWork
Code:
SELECT tblWorkIncoming.[Work Incoming ID], tblWorkIncoming.SignonID, tblWorkIncoming.[Team Name], tblWorkIncoming.[Task Name], tblSystems.System, tblWorkIncoming.[Sub Section], tblWorkIncoming.[Date Allocated], tblWorkIncoming.[Amount Incoming], dSum("Amount Worked","tblWorkCompleted","[Work Incoming ID] = " & [Work Incoming ID]) AS Worked, tblWorkIncoming.[Worked-Temp]
FROM tblSystems INNER JOIN tblWorkIncoming ON tblSystems.ID = tblWorkIncoming.System
WHERE (((tblWorkIncoming.SignonID)=fGetUserName()));
qryOutstandingWork2
Code:
SELECT qryOutstandingWork.[Work Incoming ID], qryOutstandingWork.SignonID, qryOutstandingWork.[Team Name], qryOutstandingWork.[Task Name], qryOutstandingWork.System, qryOutstandingWork.[Sub Section], qryOutstandingWork.[Date Allocated], qryOutstandingWork.[Amount Incoming], qryOutstandingWork.Worked, IIf(IsNull([Worked]),[Amount Incoming],[Amount Incoming]-[Worked]) AS Outstanding, qryOutstandingWork.[Worked-Temp]
FROM qryOutstandingWork
WHERE (((IIf(IsNull([Worked]),[Amount Incoming],[Amount Incoming]-[Worked]))>0))
ORDER BY qryOutstandingWork.[Date Allocated];
Can anyone suggest any methods for speeding up these queries? I am experimenting with using Trevor Best's basLookup module which replaces the dSum function with a more efficient tSum function. Up to now though, I have not managed to get this working properly.


Many thanks

Edgar