MORE PROBLEMS WITH RECORDS NOT SHOWING

[obriend]

Here is my SQL statement

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
GROUP BY [TIME WORKED].DATE, [Employee list].NAME, [Employee list].
[SHIFT/SQUAD]
HAVING ((([TIME WORKED].DATE) Between #1/1/2007# And #1/30/2007#) AND
(([Employee list].[SHIFT/SQUAD]) Like 4 Or ([Employee list].[SHIFT/
SQUAD])=8 Or ([Employee list].[SHIFT/SQUAD])=12 Or ([Employee list].
[SHIFT/SQUAD])=16))
ORDER BY Sum([TIME WORKED].[HOURS WORKED]), [Employee list].NAME;

I was showing all records from the Employee list whether or not they
had worked hours. Now I tried to add a date range and all I get is
what I had before - employees that just worked OT.

Help!

 

[Jeff Boyce]

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

 

[John Spencer]

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.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[obriend]

Thanks, but that did not do anything - I can change the "and" to an
"or" but then all dates show up, rather than the range. I need all
employees to show up, even those with null values for those dates.

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

Here is my SQL statement

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
GROUP BY [TIME WORKED].DATE, [Employee list].NAME, [Employee list].
[SHIFT/SQUAD]
HAVING ((([TIME WORKED].DATE) Between #1/1/2007# And #1/30/2007#) AND
(([Employee list].[SHIFT/SQUAD]) Like 4 Or ([Employee list].[SHIFT/
SQUAD])=8 Or ([Employee list].[SHIFT/SQUAD])=12 Or ([Employee list].
[SHIFT/SQUAD])=16))
ORDER BY Sum([TIME WORKED].[HOURS WORKED]), [Employee list].NAME;

I was showing all records from the Employee list whether or not they
had worked hours. Now I tried to add a date range and all I get is
what I had before - employees that just worked OT.

Help!- Hide quoted text -- Show quoted text -

 

[Jeff Boyce]

That isn't what I suggested.

I suggested using parentheses around your clauses to make explicit the order
in which you want Access to evaluate the conditions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, but that did not do anything - I can change the "and" to an
"or" but then all dates show up, rather than the range. I need all
employees to show up, even those with null values for those dates.

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

message

Here is my SQL statement

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
GROUP BY [TIME WORKED].DATE, [Employee list].NAME, [Employee list].
[SHIFT/SQUAD]
HAVING ((([TIME WORKED].DATE) Between #1/1/2007# And #1/30/2007#) AND
(([Employee list].[SHIFT/SQUAD]) Like 4 Or ([Employee list].[SHIFT/
SQUAD])=8 Or ([Employee list].[SHIFT/SQUAD])=12 Or ([Employee list].
[SHIFT/SQUAD])=16))
ORDER BY Sum([TIME WORKED].[HOURS WORKED]), [Employee list].NAME;

I was showing all records from the Employee list whether or not they
had worked hours. Now I tried to add a date range and all I get is
what I had before - employees that just worked OT.

Help!- Hide quoted text -- Show quoted text -

 

[lewie]

You only ANDed the date with the first criteria. If you switch to
design view each line in the criteria is an OR.
and you must add the date to the criteria of every OR entry. so you
should have a line for every OR condition and a corresponding entry in
the criteria of the date field thus creating an AND for all your OR's.
Lewie

Thanks, but that did not do anything - I can change the "and" to an
"or" but then all dates show up, rather than the range. I need all
employees to show up, even those with null values for those dates.

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...

Jeff Boyce
Microsoft Office/Access MVP

Here is my SQL statement
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
GROUP BY [TIME WORKED].DATE, [Employee list].NAME, [Employee list].
[SHIFT/SQUAD]
HAVING ((([TIME WORKED].DATE) Between #1/1/2007# And #1/30/2007#) AND
(([Employee list].[SHIFT/SQUAD]) Like 4 Or ([Employee list].[SHIFT/
SQUAD])=8 Or ([Employee list].[SHIFT/SQUAD])=12 Or ([Employee list].
[SHIFT/SQUAD])=16))
ORDER BY Sum([TIME WORKED].[HOURS WORKED]), [Employee list].NAME;
I was showing all records from the Employee list whether or not they
had worked hours. Now I tried to add a date range and all I get is
what I had before - employees that just worked OT.
Help!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

 

[obriend]

Thanks Jeff - you did it! Much appreciated. I can run everything
together if I rename my fields?

Thanks, but that did not do anything - I can change the "and" to an
"or" but then all dates show up, rather than the range. I need all
employees to show up, even those with null values for those dates.

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...

Jeff Boyce
Microsoft Office/Access MVP

Here is my SQL statement
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
GROUP BY [TIME WORKED].DATE, [Employee list].NAME, [Employee list].
[SHIFT/SQUAD]
HAVING ((([TIME WORKED].DATE) Between #1/1/2007# And #1/30/2007#) AND
(([Employee list].[SHIFT/SQUAD]) Like 4 Or ([Employee list].[SHIFT/
SQUAD])=8 Or ([Employee list].[SHIFT/SQUAD])=12 Or ([Employee list].
[SHIFT/SQUAD])=16))
ORDER BY Sum([TIME WORKED].[HOURS WORKED]), [Employee list].NAME;
I was showing all records from the Employee list whether or not they
had worked hours. Now I tried to add a date range and all I get is
what I had before - employees that just worked OT.
Help!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

 

[Jeff Boyce]

I'm not sure I understand what you mean by "run everything together if I
rename my fields"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff - you did it! Much appreciated. I can run everything
together if I rename my fields?

Thanks, but that did not do anything - I can change the "and" to an
"or" but then all dates show up, rather than the range. I need all
employees to show up, even those with null values for those dates.

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...

Jeff Boyce
Microsoft Office/Access MVP

messagenews:[email protected]...

Here is my SQL statement

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
GROUP BY [TIME WORKED].DATE, [Employee list].NAME, [Employee list].
[SHIFT/SQUAD]
HAVING ((([TIME WORKED].DATE) Between #1/1/2007# And #1/30/2007#) AND
(([Employee list].[SHIFT/SQUAD]) Like 4 Or ([Employee list].[SHIFT/
SQUAD])=8 Or ([Employee list].[SHIFT/SQUAD])=12 Or ([Employee list].
[SHIFT/SQUAD])=16))
ORDER BY Sum([TIME WORKED].[HOURS WORKED]), [Employee list].NAME;

I was showing all records from the Employee list whether or not they
had worked hours. Now I tried to add a date range and all I get is
what I had before - employees that just worked OT.

Help!- Hide quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -