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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Yes, this is pretty advanced stuff. You should be able to just change Table1
to Move_Task_Master in the sub-query to get it to work.

However, an easier way to do this would be to create the sub-query and save
it.

SELECT Task_ID, Task_Type, Assigned_To
FROM [Move_Task_Master]
WHERE [Task_Status] IN ("AVL", "ASN")
AND [Task_Type] IN ("Picking", "Replenish")

Save this query as qryTasks

Now, create a new query, add the Users table, and qryTasks to the query grid.

Join the User field from Users to the Assigned_To field in qryTasks. Then
right click on the join and set it up to select all from users and only those
that match from qryTasks.

Add the PM, User, Name fields from Users, and the Task_Type and Task_ID
fields from qryTasks to the grid.

Change the query type to an aggregate query (click the sigma). Then change
the GroupBy to Count under the Task_ID field. All the other fields should
say Group By.

Finally, add [Enter Supervisor] in the criteria line of the PM field.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Joseph C." wrote:

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

  • Re: How do I show all in a count query?
    ... "Dale Fye" wrote: ... an easier way to do this would be to create the sub-query and save ... Now, create a new query, add the Users table, and qryTasks to the query grid. ...
    (microsoft.public.access.queries)
  • A Bit of Query Help....
    ... I need to write a query or sub-query, but only use a parameter once. ... FROM table1 ... I've been playing with sub-selects and MAX, ...
    (comp.databases.oracle.misc)
  • Funky Query
    ... I need to write a query or sub-query, but only use a parameter once. ... FROM table1 ... I've been playing with sub-selects and MAX, ...
    (comp.databases.oracle.misc)
  • Re: Displaying row no/Record Counting
    ... EnteredOn Date/Time when the record was added. ... You create a query that contains Table1. ... On each row of your query, you need to count the number of records in Table1 ... In> order to generate an alphabetical listing of these people,> I have to analyze the report in Excel and sort the> spreadsheet based on the client's name. ...
    (microsoft.public.access.queries)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)