Have you tried using parentheses around your "And" and "Or" clauses?  It may
be that Access is interpreting the precedence order differently than you
intended...

Regards

Jeff Boyce
Microsoft Office/Access MVP

SELECT Sum([TIME WORKED].[HOURS WORKED]) AS [SumOfHOURS WORKED]
, [TIME WORKED].DATE
, [Employee list].NAME
, [Employee list].[SHIFT/SQUAD]

FROM [Employee list] LEFT JOIN [TIME WORKED]
ON [Employee list].ID = [TIME WORKED].ID

WHERE [TIME WORKED].DATE Between #1/1/2007# And #1/30/2007#
AND
[Employee list].[SHIFT/SQUAD] IN (4,8,12,16)

GROUP BY [TIME WORKED].DATE
, [Employee list].NAME
, [Employee list].[SHIFT/SQUAD]

ORDER BY Sum([TIME WORKED].[HOURS WORKED]), [Employee list].NAME;

This needs two queries to solve.  Query one gets the data from time worked
and then is used in a second query to show you the desired results

SELECT [Time Worked].ID
, Sum([TIME WORKED].[HOURS WORKED]) AS [SumOfHOURS WORKED]
, [TIME WORKED].DATE
FROM  [TIME WORKED]
WHERE [TIME WORKED].DATE Between #1/1/2007# And #1/30/2007#
GROUP BY  [Time Worked].ID, [TIME WORKED].DATE

Save that as qTimeWorked and use that in a second query

SELECT qTimeWorked.[SumOfHOURS WORKED]
, qTimeWorked.DATE
, [Employee list].NAME
, [Employee list].[SHIFT/SQUAD]
FROM [Employee list] LEFT JOIN qTimeWorked
ON [Employee list].ID = [qTimeWorked].ID
WHERE [Employee list].[SHIFT/SQUAD] IN (4,8,12,16)
ORDER BY qTimeWorked.[SumOfHours WORKED], [Employee list].NAME;

By the way if your table and field names didn't have spaces and special
characters in them you could do this all in one query.

Date is a bad name for a field since Date is also a function to return the
current date.  You would be better off if the field was named something like
WorkDate.

Name is another field name that can cause problems since all objects in
Access have a Name property.  I would suggest FullName or EmployeeName might
be a better field name.