Re: How do I show all in a count query?

This is much more advanced than I am, so I am having a hard time
troubleshooting. the name of "table1" is actually "MOVE_TASK_MASTER" if this
makes a difference, I think it does. I am getting an error message that
says: "Syntax error in FROM clause."


"Dale Fye" wrote:

Joseph,

Because of the sub-query, you will have to do this in the SQL view of the
query. You should be able to just cut my SQL and paste it into the SQL view
of a new query. Additionally, I left unintentionally left out the supervisor
piece of the query, so try:

SELECT U.PM, U.User, U.Name, T.Task_Type,
Count(T.Task_ID) as RecCount
FROM Users as U
LEFT JOIN (SELECT Assigned_To, Task_ID, Task_Type
FROM Table1
WHERE [Task_Status] IN ("AVL", "ASN")
AND [Task_Type] IN ("Picking", "Replenish")) as T
ON U.User = T.Assigned_To
GROUP BY U.PM, U.User, U.Name, T.Task_Type
HAVING U.PM = [Enter Supervisor]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Joseph C." wrote:

Dale: I am trying to put this in.


"Dale Fye" wrote:

Your criteria in the WHERE and HAVING clauses are what are causing your
problem. You are also grouping by Task_Type, but don't list it in the fields
you are selecting, which could lead to multiple lines for a particular user,
and no indication of why. I've added the Task_Type into the select

Try:

SELECT U.PM, U.User, U.Name, T.Task_Type,
Count(T.Task_ID) as RecCount
FROM Users as U
LEFT JOIN (SELECT Assigned_To, Task_ID, Task_Type
FROM Table1
WHERE [Task_Status] IN ("AVL", "ASN")
AND [Task_Type] IN ("Picking", "Replenish")) as T
ON U.User = T.Assigned_To
GROUP BY U.PM, U.User, U.Name, T.Task_Type

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Joseph C." wrote:

Let me show you the SQL as I have already tried to change the join type.
--------------
SELECT USERS.PM, USERS.USER, USERS.NAME, Count(table1.TASK_ID) AS
CountOfTASK_ID
FROM USERS LEFT JOIN table1 ON USERS.USER = table1.ASSIGNED_TO
WHERE (((table1.TASK_STATUS)="AVL" Or (table1.TASK_STATUS)="ASN"))
GROUP BY USERS.PM, USERS.USER, USERS.NAME, table1.TASK_TYPE
HAVING (((USERS.PM)=[Enter Supervisor]) AND ((table1.TASK_TYPE)="PICKING" Or
(table1.TASK_TYPE)="REPLENISH"));
--------------
Thoughts?
.

Relevant Pages