hi it is easy to be done,
I have created it and upload it here as MyMood.zip, download the file and study the two tables My Mood and Status and the Query1 which returns the result to you, it took me only two minutes to do it, not that complicated, hope it helps.
Regards.
SELECT mood, count(mood)
FROM tablename
GROUP BY mood;
Basically this will list each different value in the mood field and then count the number of occurrences of that value.
You can also use the Query Designer in Access if you are not comfortable with SQL.
Start a new query in the Design View, add the relevant table and add the mood field to the field list twice. click the Σ sign at the very top on the main Access toolbar to enable grouping. On the designer an extra row will be added for each field so you will have the field name, the table name, the word Total with Group By in each column. In one of the fields click on the Group By and a drop down selector will appear with a list of options. Scroll down until you find Count and click it. Run the query and you should get your count of moods.
Not sure if it can be done in one query. My approach would be to use VBA, create the recordset, read all the records to an array, create a counter variable and then loop through the array.
if the mood value in Arr(1) = Arr(0) then counter = counter + 1
if the mood is different then write the mood and the counter values to a string variable and reset the counter.
Once you have completed the loop display the string in a text box or message box.
Again not sure if it can be done in a single query - others may be able to correct me on that though.