Here is an approach;
Query A
Code:
SELECT tbl_OnCallList.FldWorkerID
,tbl_OnCallList.fldOnCallHrs
,tbl_OnCallList.fldQualified
,tbl_OnCallList.fldWrkLocation
,tbl_OnCallList.fldWorkerTypeid
FROM tbl_OnCallList
WHERE (((tbl_OnCallList.fldQualified) = True))
ORDER BY tbl_OnCallList.fldOnCallHrs;
Query B
Code:
SELECT TOP 2 A.FldWorkerID
,A.fldOnCallHrs
,A.fldQualified
,A.fldWrkLocation
,A.fldWorkerTypeid
FROM A
Query C
Code:
SELECT tbl_OnCallList.FldWorkerID
,tbl_OnCallList.fldOnCallHrs
,tbl_OnCallList.fldQualified
,tbl_OnCallList.fldWrkLocation
,tbl_OnCallList.fldWorkerTypeid
FROM tbl_OnCallList
WHERE tbl_OnCallList.FldWorkerID NOT IN (
SELECT b.fldworkerId
FROM B
)
ORDER BY fldOnCallHrs;
Query UnionQueryKeepingQualifiedAtTopOfList
The FinOrder is just a temp field to keep the qualified with least OnCallHours at the top of the list.
I used U1 and U2 just to keep qualified (Top 2) at the top, the others are in order of the ascending OnCallHours
Code:
SELECT TOP 2 A.FldWorkerID
,A.fldOnCallHrs
,A.fldQualified
,A.fldWrkLocation
,A.fldWorkerTypeid
,"U1" AS FinOrder
FROM A
UNION
SELECT tbl_OnCallList.FldWorkerID
,tbl_OnCallList.fldOnCallHrs
,tbl_OnCallList.fldQualified
,tbl_OnCallList.fldWrkLocation
,tbl_OnCallList.fldWorkerTypeid
,"U2" AS FinOrder
FROM tbl_OnCallList
WHERE tbl_OnCallList.FldWorkerID NOT IN (
SELECT b.fldworkerId
FROM B
)
ORDER BY FinOrder
,fldOnCallHrs;
Good luck.