Slow query with sequence field

[ErinL]

Hi Everyone -

I am having a problem with a query I have created. I have a query called qryDockRuns that runs perfect and within seconds the records appear. However, when I use that query to create another query and add a sequence number field, the query runs unacceptably slow. I don't even know how long it would actually take to get the results since I have never waited beyond 30 minutes for it to finish.

This is the sql for the query I am having problems with:

INSERT INTO tblDockRunsNumbered ( Sequence, EmployeeID, Employee, [Date], RunTime, Type, [Container], StopLocation, [From], [To] )
SELECT IIf([StopLocation]="Door",0,(SELECT Count(*) FROM qryDockRuns AS T WHERE T.EmployeeID = qryDockRuns.EmployeeID AND T.RecordNumber < qryDockRuns.RecordNumber And T.StopLocation <> "Door" And T.RunTime > DMax("RunTime", "qryDockRuns", "EmployeeID=" & T.EmployeeID & " And StopLocation='Door' And Date=#" & qryDockRuns.Date & "# And RunTime<#" & qryDockRuns.RunTime & "#"))+1) AS Sequence, qryDockRuns.EmployeeID, qryDockRuns.Employee, qryDockRuns.Date, qryDockRuns.RunTime, qryDockRuns.Type, qryDockRuns.Container, qryDockRuns.StopLocation, qryDockRuns.From, qryDockRuns.To
FROM qryDockRuns
ORDER BY qryDockRuns.EmployeeID, qryDockRuns.Date, qryDockRuns.RunTime;

If anyone has any suggestions on something that I can change I would greatly appreciate it.

Thank you in advance.

 

[jdraw]

Why do you want/need a Sequence number?

In my view the IIF and DMax are factors which will cause all records to be read. Also the Where clause with multi criteria will add to "slowness".
How many records are involved?
Are any of your fields indexed-- even that would have a small affect in my view.

You are not just creating another query you are populating a table also.

 

[ErinL]

Thank you for the quick response.

The intention was for the sequence number field to identify how many trips an associate took to home slots in the warehouse before going back to the dock door for another stack of pallets. This is an abbreviated view but the results should look like this:

Associate Sequence Stop Location
John Doe 0 Door
John Doe 1 Slot
John Doe 2 Slot
John Doe 0 Door

This shows two stops at home slots before going back to the dock.

There are currently 2,138 records in the table. Out of curiousity, I deleted records until it ran at a decent speed and at 100 records it runs like a normal query.

As far as indexes, I have all fields indexed with no duplicates on the RecordNumber field.