View Single Post
  #8 (permalink)  
Old April 10th, 2004, 12:07 PM
jurgenw jurgenw is offline
Authorized User
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts

SELECT employees.empno,, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno
Order By IIF(assignments.empno='Sr. Resident Engineer', 1, IIF(assignments.empno= 7,'Resident Engineer', IIF (assignments.empno='Asst. Resident Engineer', 3, IIF(assignments.empno='Quantity Suveyor', 4, IIF(assignments.empno='Inspector (As Built)', IIF(assignments.empno='Surveyor', 6, 7))))))

You could alternatively Select on Where clauses for each type and select an integer for each type and union the selects together and order by the integer or you could add a sort field to the table to make life really easy, or join a sort table comprised of the empno field with the sort value.

J├╝rgen Welz
Edmonton AB Canada
Reply With Quote