what's wrong with my query

G

Guest

I have a query that is supposed to group results and sum totals of certain
fields. The sql from the query design window is below...

SELECT BeginningOfWeek([pcslog dos]) AS SundayDate, Employee![EMP Last Name]
& ", " & Employee![EMP First Name] & " " & Employee![EMP Middle Name] AS
EmployeeName, [CC PCS Billing Log].[PCSLOG-FK Emp ID], Sum([CC PCS Billing
Log].[PCSLOG Emp Hours]) AS [SumOfPCSLOG Emp Hours], Min([CC PCS Billing
Log].[PCSLOG Late Time]) AS [MinOfPCSLOG Late Time], Employee.[EMP-PK Emp ID]
FROM Employee INNER JOIN [CC PCS Billing Log] ON Employee.[EMP-PK Emp ID] =
[CC PCS Billing Log].[PCSLOG-FK Emp ID]
GROUP BY BeginningOfWeek([pcslog dos]), Employee![EMP Last Name] & ", " &
Employee![EMP First Name] & " " & Employee![EMP Middle Name], [CC PCS Billing
Log].[PCSLOG-FK Emp ID], Employee.[EMP-PK Emp ID]
HAVING (((Min([CC PCS Billing Log].[PCSLOG-FK Proc Code]))<>"A0080" And
(Min([CC PCS Billing Log].[PCSLOG-FK Proc Code]))<>"S0215" And (Min([CC PCS
Billing Log].[PCSLOG-FK Proc Code]))<>"Mi P Pay" And (Min([CC PCS Billing
Log].[PCSLOG-FK Proc Code]))<>"MI Super"))
ORDER BY BeginningOfWeek([pcslog dos]), Employee![EMP Last Name] & ", " &
Employee![EMP First Name] & " " & Employee![EMP Middle Name]
WITH OWNERACCESS OPTION;

a sub set of the results without the "totals" grouping is below i
substituted data for empname for privacy, but in this subset all employee
names are the same

SundayDate | EmployeeName | Employee | Emp Hours | Late Time | Employee ID
04/10/2005 | 7 | 7 | 1 |
No | 7
04/10/2005 | 7 | 7 | 5 |
No | 7
04/10/2005 | 7 | 7 | 5 |
No | 7
04/10/2005 | 7 | 7 | 5 |
No | 7
04/10/2005 | 7 | 7 | 4 |
No | 7
04/10/2005 | 7 | 7 | 1.25 |
No | 7
04/10/2005 | 7 | 7 | 2 |
No | 7
04/10/2005 | 7 | 7 | 2 |
No | 7
04/10/2005 | 7 | 7 | 2 |
No | 7
04/10/2005 | 7 | 7 | 3.5 |
No | 7
04/10/2005 | 7 | 7 | 4.75 |
No | 7
04/10/2005 | 7 | 7 | 2 |
No | 7
04/17/2005 | 7 | 7 | 2 |
No | 7
04/17/2005 | 7 | 7 | 1 |
Yes | 7
04/17/2005 | 7 | 7 | 4 |
Yes | 7
04/17/2005 | 7 | 7 | 4.75 |
Yes | 7
04/17/2005 | 7 | 7 | 1 |
No | 7
04/17/2005 | 7 | 7 | 2 |
No | 7
04/17/2005 | 7 | 7 | 2 |
No | 7
04/17/2005 | 7 | 7 | 5 |
No | 7
04/17/2005 | 7 | 7 | 5 |
No | 7
04/17/2005 | 7 | 7 | 5 |
No | 7
04/17/2005 | 7 | 7 | 2.25 |
No | 7

with the totals using the sql above this is the results of the data for that
employee

04/10/2005 | 7 | 7 | 37.5 |
0 | 7

which is correct for the 10th but the 17th is missing
the results should be the following

04/10/2005 | 7 | 7 | 37.5 |
0 | 7
04/17/2005 | 7 | 7 | 34 |
-1 | 7

what's wrong with my query?
 
M

MGFoster

SuzyQ said:
I have a query that is supposed to group results and sum totals of certain
fields. The sql from the query design window is below...

SELECT BeginningOfWeek([pcslog dos]) AS SundayDate, Employee![EMP Last Name]
& ", " & Employee![EMP First Name] & " " & Employee![EMP Middle Name] AS
EmployeeName, [CC PCS Billing Log].[PCSLOG-FK Emp ID], Sum([CC PCS Billing
Log].[PCSLOG Emp Hours]) AS [SumOfPCSLOG Emp Hours], Min([CC PCS Billing
Log].[PCSLOG Late Time]) AS [MinOfPCSLOG Late Time], Employee.[EMP-PK Emp ID]
FROM Employee INNER JOIN [CC PCS Billing Log] ON Employee.[EMP-PK Emp ID] =
[CC PCS Billing Log].[PCSLOG-FK Emp ID]
GROUP BY BeginningOfWeek([pcslog dos]), Employee![EMP Last Name] & ", " &
Employee![EMP First Name] & " " & Employee![EMP Middle Name], [CC PCS Billing
Log].[PCSLOG-FK Emp ID], Employee.[EMP-PK Emp ID]
HAVING (((Min([CC PCS Billing Log].[PCSLOG-FK Proc Code]))<>"A0080" And
(Min([CC PCS Billing Log].[PCSLOG-FK Proc Code]))<>"S0215" And (Min([CC PCS
Billing Log].[PCSLOG-FK Proc Code]))<>"Mi P Pay" And (Min([CC PCS Billing
Log].[PCSLOG-FK Proc Code]))<>"MI Super"))
ORDER BY BeginningOfWeek([pcslog dos]), Employee![EMP Last Name] & ", " &
Employee![EMP First Name] & " " & Employee![EMP Middle Name]
WITH OWNERACCESS OPTION;
Click to expand...
the results should be the following

04/10/2005 | 7 | 7 | 37.5 |
0 | 7
04/17/2005 | 7 | 7 | 34 |
-1 | 7
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I cleaned up your query for easier reading and fixed some anomolies &
redundancies:

SELECT BeginningOfWeek([pcslog dos]) AS SundayDate,
E.[EMP Last Name] & ", " & E.[EMP First Name] & " " &
E.[EMP Middle Name] AS EmployeeName,
E.[EMP-PK Emp ID],
Sum(L.[PCSLOG Emp Hours]) AS [SumOfPCSLOG Emp Hours],
Min(L.[PCSLOG Late Time]) AS [MinOfPCSLOG Late Time],

FROM Employee As E
INNER JOIN [CC PCS Billing Log] As L
ON E.[EMP-PK Emp ID] = L.[PCSLOG-FK Emp ID]

WHERE L.[PCSLOG-FK Proc Code] NOT IN
("A0080","S0215","Mi P Pay","MI Super")

GROUP BY BeginningOfWeek([pcslog dos]),
E.[EMP Last Name] & ", " & E.[EMP First Name] & " " &
E.[EMP Middle Name],
E.[EMP-PK Emp ID]


ORDER BY BeginningOfWeek([pcslog dos]),
E.[EMP Last Name],
E.[EMP First Name],
E.[EMP Middle Name]

WITH OWNERACCESS OPTION;

The HAVING clause didn't make any sense. First, it should be the WHERE
clause. Second, we don't usually use MIN("text value") for multiple
text values. The IN () phrase works better.

You are asking to show the Min() of [PCSLOG Late Time]. I'm assuming
that it is stored as -1 for Yes and 0 for No. It will always select Yes
(-1 being the minimum value in that column). This doesn't mean that the
Sum() will sum ONLY the Yes records; it will sum BOTH Yes and No "Late
Times," but, the [MinOfPCSLOG Late Time] column will only show the value
"Yes" 'cuz that is the minimum value in that column. If you only want
to return Sums of the Late time values = "Yes" (True) you'd change the
WHERE clause to something like this:

WHERE L.[PCSLOG-FK Proc Code] NOT IN
("A0080","S0215","Mi P Pay","MI Super")
AND [PCSLog Late Time] = True

All the above doesn't answer your initial question. My question to you
is do the records for the 10th have Proc Codes of ("A0080","S0215",
"Mi P Pay","MI Super")? If they don't then records for the 10th won't
show in the result set.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQvElIYechKqOuFEgEQJObwCgu1XCuj30goa2Bq6oNyKF/QQ3eWYAoKy1
8lRhFE7tLcPFPNdQzWMl7xhk
=NKfx
-----END PGP SIGNATURE-----
 
G

Guest

the sql is exactly like the wizard created it, creating it myself I mignt not
have used having.

I want to sum all the records whether or not they are marked late time, but
I want to know if there was any late time in that week. I only want to
filter out the specific codes listed.

All the above doesn't answer your initial question. My question to you
is do the records for the 10th have Proc Codes of ("A0080","S0215",
"Mi P Pay","MI Super")? If they don't then records for the 10th won't
show in the result set.
Click to expand...

The 10th is working, it's the 17th that isn't. Both sets of data have the
some codes listed above which I want filtered out, and they are being
filtered out. (As seen when I modify the query to show me the procedure code
and don't sum totals). I'm using MIN on the proc code field because I need
it to filter out those codes, but I don't want a separate record for every
other proc code that exists. Otherwise it would group on each unique
procedure code. The difference that I see between the two sets of data is
that the 17th has late time and the 10th doesn't.

However I have split the query into two queries one to filter the procedure
codes, and the other to sum the specific fields I need. So far this seems to
work. Although I would like to know why the query didn't work, I have
resolved the issue a different way. Thank you for you time and help.

MGFoster said:
SuzyQ said:
I have a query that is supposed to group results and sum totals of certain
fields. The sql from the query design window is below...

SELECT BeginningOfWeek([pcslog dos]) AS SundayDate, Employee![EMP Last Name]
& ", " & Employee![EMP First Name] & " " & Employee![EMP Middle Name] AS
EmployeeName, [CC PCS Billing Log].[PCSLOG-FK Emp ID], Sum([CC PCS Billing
Log].[PCSLOG Emp Hours]) AS [SumOfPCSLOG Emp Hours], Min([CC PCS Billing
Log].[PCSLOG Late Time]) AS [MinOfPCSLOG Late Time], Employee.[EMP-PK Emp ID]
FROM Employee INNER JOIN [CC PCS Billing Log] ON Employee.[EMP-PK Emp ID] =
[CC PCS Billing Log].[PCSLOG-FK Emp ID]
GROUP BY BeginningOfWeek([pcslog dos]), Employee![EMP Last Name] & ", " &
Employee![EMP First Name] & " " & Employee![EMP Middle Name], [CC PCS Billing
Log].[PCSLOG-FK Emp ID], Employee.[EMP-PK Emp ID]
HAVING (((Min([CC PCS Billing Log].[PCSLOG-FK Proc Code]))<>"A0080" And
(Min([CC PCS Billing Log].[PCSLOG-FK Proc Code]))<>"S0215" And (Min([CC PCS
Billing Log].[PCSLOG-FK Proc Code]))<>"Mi P Pay" And (Min([CC PCS Billing
Log].[PCSLOG-FK Proc Code]))<>"MI Super"))
ORDER BY BeginningOfWeek([pcslog dos]), Employee![EMP Last Name] & ", " &
Employee![EMP First Name] & " " & Employee![EMP Middle Name]
WITH OWNERACCESS OPTION;
Click to expand...
the results should be the following

04/10/2005 | 7 | 7 | 37.5 |
0 | 7
04/17/2005 | 7 | 7 | 34 |
-1 | 7
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I cleaned up your query for easier reading and fixed some anomolies &
redundancies:

SELECT BeginningOfWeek([pcslog dos]) AS SundayDate,
E.[EMP Last Name] & ", " & E.[EMP First Name] & " " &
E.[EMP Middle Name] AS EmployeeName,
E.[EMP-PK Emp ID],
Sum(L.[PCSLOG Emp Hours]) AS [SumOfPCSLOG Emp Hours],
Min(L.[PCSLOG Late Time]) AS [MinOfPCSLOG Late Time],

FROM Employee As E
INNER JOIN [CC PCS Billing Log] As L
ON E.[EMP-PK Emp ID] = L.[PCSLOG-FK Emp ID]

WHERE L.[PCSLOG-FK Proc Code] NOT IN
("A0080","S0215","Mi P Pay","MI Super")

GROUP BY BeginningOfWeek([pcslog dos]),
E.[EMP Last Name] & ", " & E.[EMP First Name] & " " &
E.[EMP Middle Name],
E.[EMP-PK Emp ID]


ORDER BY BeginningOfWeek([pcslog dos]),
E.[EMP Last Name],
E.[EMP First Name],
E.[EMP Middle Name]

WITH OWNERACCESS OPTION;

The HAVING clause didn't make any sense. First, it should be the WHERE
clause. Second, we don't usually use MIN("text value") for multiple
text values. The IN () phrase works better.

You are asking to show the Min() of [PCSLOG Late Time]. I'm assuming
that it is stored as -1 for Yes and 0 for No. It will always select Yes
(-1 being the minimum value in that column). This doesn't mean that the
Sum() will sum ONLY the Yes records; it will sum BOTH Yes and No "Late
Times," but, the [MinOfPCSLOG Late Time] column will only show the value
"Yes" 'cuz that is the minimum value in that column. If you only want
to return Sums of the Late time values = "Yes" (True) you'd change the
WHERE clause to something like this:

WHERE L.[PCSLOG-FK Proc Code] NOT IN
("A0080","S0215","Mi P Pay","MI Super")
AND [PCSLog Late Time] = True

All the above doesn't answer your initial question. My question to you
is do the records for the 10th have Proc Codes of ("A0080","S0215",
"Mi P Pay","MI Super")? If they don't then records for the 10th won't
show in the result set.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQvElIYechKqOuFEgEQJObwCgu1XCuj30goa2Bq6oNyKF/QQ3eWYAoKy1
8lRhFE7tLcPFPNdQzWMl7xhk
=NKfx
-----END PGP SIGNATURE-----
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Looping through data with calculations 1
Grouping - Distinct 5
count how many staff are scheduled between... 3
Subquery not integrating to query to display previous sum of quantities 3
Query to return complete record with max cost within grouping... 6
Duplicates numbered but with a new condition 4
Help with Query?? 1
Crosstab Query based on Ordinal Values 6

Top