Assuming a table called tblStaff with field names........SName,Mon,Tue,Wed,Thu,Fri
then the SQL required for reproducing your goal (which you can paste into the SQL window) in query design is.....
Code:
SELECT s.Staff,
(SELECT Count(tblStaff.Mon) AS Mon FROM tblStaff WHERE (((tblStaff.Mon)=s.Staff))) AS Mon,
(SELECT Count(tblStaff.Tue) AS Tue FROM tblStaff WHERE (((tblStaff.Tue)=s.Staff))) AS Tue,
(SELECT Count(tblStaff.Wed) AS Wed FROM tblStaff WHERE (((tblStaff.Wed)=s.Staff))) AS Wed,
(SELECT Count(tblStaff.Thu) AS Thu FROM tblStaff WHERE (((tblStaff.Thu)=s.Staff))) AS Thu,
(SELECT Count(tblStaff.Fri) AS Fri FROM tblStaff WHERE (((tblStaff.Fri)=s.Staff))) AS Fri
FROM
[SELECT DISTINCT tblStaff.Mon as Staff FROM tblStaff WHERE (((tblStaff.Mon) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Tue as Staff FROM tblStaff WHERE (((tblStaff.Tue) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Wed as Staff FROM tblStaff WHERE (((tblStaff.Wed) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Thu as Staff FROM tblStaff WHERE (((tblStaff.Thu) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Fri as Staff FROM tblStaff WHERE (((tblStaff.Fri) Is Not Null))]. AS s
ORDER BY s.Staff DESC;
What this SQL is doing by way of explanation is using correlated sub queries in tandem with the UNION query. The union query firstly selects distinct instances of each value (SF, MD or any other value for that matter) for each day of the week and presents this as a column aliased to the name of 'Staff'. We then have Six columns of data The first column is the 'Staff' column derived from the correlated sub query followed by five columns raised by declaring separate SELECT COUNT sql statements that each represent a day of the week Monday to Friday and each of which has its criteria clause set to equal the value found in any of the data values that appear in the rows for the 'Staff' column.
Hope this helps you although I tend to agree with the earlier post you seem to be using a table like a spreadsheet
Regards