Sort least first query

[NT100]

I've a table containing staff names, another table containing the occurrences of the staff names with a date.

For example,
tblStaff -> StaffKey, StaffName,
tblSOccurence -> StaffKey, ActivityDate

tblStaff
StaffKey StaffName
73938 Ah Ong
93948 Owe Tam
94331 Weng Housin

tblSOccurrence ActivityDate
73938 12-Oct-2016
73938 22-Nov-2016
73938 3-Jan-2016
93948 15-May-2016
93948 22-July-2016
94331 6-Jan-2016

Sorted by least occurrence first in a textbox as shown below
Weng Housin
Owe Tam
Ah Ong


Any idea on this

 

[Gasman]

Create a query to count the occurrences, then link that query to the table for another query, and sort that by count of occurrences descending?

 

[arnelgp]

SELECT tblStaff.StaffKey, tblStaff.StaffName, (select count(*) from tblSOccurrence AS T1 WHERE T1.StaffKey=tblStaff.Staffkey) AS Occurrence
FROM tblStaff ORDER BY 3 DESC;

the above does not work, why?

try this

SELECT STAFFKEY, STAFFNAME, OCCURRENCE FROM (SELECT tblStaff.StaffKey, tblStaff.StaffName, (select count(*) from tblSOccurrence AS T1 WHERE T1.StaffKey=tblStaff.Staffkey) AS Occurrence
FROM tblStaff) ORDER BY 3;