How do I show all in a count query?

[Joseph C.]

I have a query that counts transactions by user and I need the query to show
me those users that have not completed any transactions as well as those that
have completed transactions. Currently the query only displays a count if
the number is greater than zero. How would I get this query to show me all
all users?

 

[Michel Walsh]

There are many ways, one will be to include records for all users with NULL
values under some field, say myNullableField and then:

SELECT user, COUNT(myNullableField)
FROM myTable
GROUP BY user


A second solution is to use an outer join with all the user:


SELECT users.user, COUNT(myTable.SomeField)
FROM users LEFT JOIN myTable
ON users.user = myTable.user
GROUP BY users.user


where here, I assumed the table users has a list of all users, no dup (ie.
users.user if a potential primary key)
..


Vanderghast, Access MVP

 

[Joseph C.]

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"));

 

[Dale Fye]

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.

 

[Michel Walsh]

As mentioned by Dale, your WHERE clause (and your HAVING clause) undo the
job made by the outer join. Indeed, after the join is made, values under
table1.status can be null (due to the outer join) so, at least, you should
also consider to add : OR table1.Task_Status IS NULL in the WHERE clause
and OR table1.Task_Type IS NULL in the HAVING clause. Note also that
your HAVING clause criteria could be moved into your WHERE clause (and doing
so, may make the execution time shorter)..


Vanderghast, Access MVP

 

[Joseph C.]

Dale: I am trying to put this in.

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.

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"));

 

[Joseph C.]

So if I understand you correctly, I am to add " Or Is Null" to the criteria
for task_status and task_type and change the totaling for task type tp a
"WHERE" function.
I tried this however the query has been running for >5 minutes and still has
not returned a result.

As mentioned by Dale, your WHERE clause (and your HAVING clause) undo the
job made by the outer join. Indeed, after the join is made, values under
table1.status can be null (due to the outer join) so, at least, you should
also consider to add : OR table1.Task_Status IS NULL in the WHERE clause
and OR table1.Task_Type IS NULL in the HAVING clause. Note also that
your HAVING clause criteria could be moved into your WHERE clause (and doing
so, may make the execution time shorter)..


Vanderghast, Access MVP

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"));

 

[Dale Fye]

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.

Dale: I am trying to put this in.

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.

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"));

 

[Joseph C.]

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

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.

Dale: I am trying to put this in.

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.



:

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"));

 

[Dale Fye]

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.

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

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.

Dale: I am trying to put this in.


:

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.



:

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"));

 

[Michel Walsh]

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 (((USERS.PM)=[Enter Supervisor])
AND ((table1.TASK_TYPE)="PICKING"
Or (table1.TASK_TYPE)="REPLENISH")
OR table1.Task_Type IS NULL)
AND (((table1.TASK_STATUS)="AVL"
Or (table1.TASK_STATUS)="ASN")
OR Table1.Task_Status IS NULL)

GROUP BY USERS.PM, USERS.USER, USERS.NAME, table1.TASK_TYPE



Note that the sub-query in the FROM clause (a virtual table) is also a valid
approach too.



Vanderghast, Access MVP

So if I understand you correctly, I am to add " Or Is Null" to the
criteria
for task_status and task_type and change the totaling for task type tp a
"WHERE" function.
I tried this however the query has been running for >5 minutes and still
has
not returned a result.

As mentioned by Dale, your WHERE clause (and your HAVING clause) undo the
job made by the outer join. Indeed, after the join is made, values under
table1.status can be null (due to the outer join) so, at least, you
should
also consider to add : OR table1.Task_Status IS NULL in the WHERE
clause
and OR table1.Task_Type IS NULL in the HAVING clause. Note also
that
your HAVING clause criteria could be moved into your WHERE clause (and
doing
so, may make the execution time shorter)..


Vanderghast, Access MVP

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"));

 

[Joseph C.]

That worked.
Thanks

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.

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

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.



:

Dale: I am trying to put this in.


:

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.



:

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"));

 

[Dale Fye]

Michael,

Won't adding the Task_Type to the Group By clause result in (potentially)
multiple rows for each PM/User, and no way to distinquish which Task_Type the
count applies to (since the SELECT clause does not contain a Task_Type field?

--
Dale

email address is invalid
Please reply to newsgroup only.

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 (((USERS.PM)=[Enter Supervisor])
AND ((table1.TASK_TYPE)="PICKING"
Or (table1.TASK_TYPE)="REPLENISH")
OR table1.Task_Type IS NULL)
AND (((table1.TASK_STATUS)="AVL"
Or (table1.TASK_STATUS)="ASN")
OR Table1.Task_Status IS NULL)

GROUP BY USERS.PM, USERS.USER, USERS.NAME, table1.TASK_TYPE



Note that the sub-query in the FROM clause (a virtual table) is also a valid
approach too.



Vanderghast, Access MVP

So if I understand you correctly, I am to add " Or Is Null" to the
criteria
for task_status and task_type and change the totaling for task type tp a
"WHERE" function.
I tried this however the query has been running for >5 minutes and still
has
not returned a result.

As mentioned by Dale, your WHERE clause (and your HAVING clause) undo the
job made by the outer join. Indeed, after the join is made, values under
table1.status can be null (due to the outer join) so, at least, you
should
also consider to add : OR table1.Task_Status IS NULL in the WHERE
clause
and OR table1.Task_Type IS NULL in the HAVING clause. Note also
that
your HAVING clause criteria could be moved into your WHERE clause (and
doing
so, may make the execution time shorter)..


Vanderghast, Access MVP


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"));

 

[Michel Walsh]

A null is not equal to another null, but is not DISTINCT from another null
either (equality and DISTINCT are two different concepts, in SQL): that
means that unless there is a bug, for a GROUP made of a single field,
multiple records with a null will make only one group, not one group for
each of the multiple null. Also, if there is a matching USER, the left join
won't generate a NULL for any field associated to table1. This leaves the
case where table1 has INITIALLY a null value for one of its field (not
involved in the join), in particular, for Task_type, which can, in this
case, lead to possible ambiguity, indeed.


users.name=Joe Blow, table1.task_id = null

can thus mean that Joe, in table Users, is NOT in table1, or that he is in
table1, but associated to as null task_id.

If you insert

table1.Assigned_to


in the GROUP By clause, the field we join ON:

ON USERS.USER = table1.ASSIGNED_TO

then you can REMOVE THE AMBIGUITY :


users.Name = Joe Blow, table1.Assigned_to = null, table1.task_id =
null

is a record without match in table1


users.Name = Joe Blow, table1.Assigned_to = Joe Blow, table1.task_id =
null

is a record with an initial null for task_id.




Vanderghast, Access MVP

Michael,

Won't adding the Task_Type to the Group By clause result in (potentially)
multiple rows for each PM/User, and no way to distinquish which Task_Type
the
count applies to (since the SELECT clause does not contain a Task_Type
field?

--
Dale

email address is invalid
Please reply to newsgroup only.

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 (((USERS.PM)=[Enter Supervisor])
AND ((table1.TASK_TYPE)="PICKING"
Or (table1.TASK_TYPE)="REPLENISH")
OR table1.Task_Type IS NULL)
AND (((table1.TASK_STATUS)="AVL"
Or (table1.TASK_STATUS)="ASN")
OR Table1.Task_Status IS NULL)

GROUP BY USERS.PM, USERS.USER, USERS.NAME, table1.TASK_TYPE



Note that the sub-query in the FROM clause (a virtual table) is also a
valid
approach too.



Vanderghast, Access MVP

So if I understand you correctly, I am to add " Or Is Null" to the
criteria
for task_status and task_type and change the totaling for task type tp
a
"WHERE" function.
I tried this however the query has been running for >5 minutes and
still
has
not returned a result.

:

As mentioned by Dale, your WHERE clause (and your HAVING clause) undo
the
job made by the outer join. Indeed, after the join is made, values
under
table1.status can be null (due to the outer join) so, at least, you
should
also consider to add : OR table1.Task_Status IS NULL in the WHERE
clause
and OR table1.Task_Type IS NULL in the HAVING clause. Note also
that
your HAVING clause criteria could be moved into your WHERE clause (and
doing
so, may make the execution time shorter)..


Vanderghast, Access MVP


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"));

 

[Dale Fye]

Michael,

I understood the issue of the NULLs, although I've never done it that way
(always used the sub-query). I wonder what the query plan would look like
for the two, and which would be more efficient.

My point is that if the user (we'll say he is USER #2 and his name is Joe
Blow, and that his boss is Bob) is mapped to Assigned_To in multiple records
(lets say Task_Type = "Picking" in one record, and "Replenish" in another
record in table1), then the SQL you have generated would result in something
like the following, because you included the Task_Type in the GroupBy
clause, but didn't include it in the SELECT clause:

PM User Name CountOfTask_ID
Bob 2 Joe Blow 1
Bob 2 Joe Blow 1

Dale

A null is not equal to another null, but is not DISTINCT from another null
either (equality and DISTINCT are two different concepts, in SQL): that
means that unless there is a bug, for a GROUP made of a single field,
multiple records with a null will make only one group, not one group for
each of the multiple null. Also, if there is a matching USER, the left
join won't generate a NULL for any field associated to table1. This leaves
the case where table1 has INITIALLY a null value for one of its field (not
involved in the join), in particular, for Task_type, which can, in this
case, lead to possible ambiguity, indeed.


users.name=Joe Blow, table1.task_id = null

can thus mean that Joe, in table Users, is NOT in table1, or that he is in
table1, but associated to as null task_id.

If you insert

table1.Assigned_to


in the GROUP By clause, the field we join ON:

ON USERS.USER = table1.ASSIGNED_TO

then you can REMOVE THE AMBIGUITY :


users.Name = Joe Blow, table1.Assigned_to = null, table1.task_id =
null

is a record without match in table1


users.Name = Joe Blow, table1.Assigned_to = Joe Blow, table1.task_id
= null

is a record with an initial null for task_id.




Vanderghast, Access MVP

Michael,

Won't adding the Task_Type to the Group By clause result in (potentially)
multiple rows for each PM/User, and no way to distinquish which Task_Type
the
count applies to (since the SELECT clause does not contain a Task_Type
field?

--
Dale

email address is invalid
Please reply to newsgroup only.

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 (((USERS.PM)=[Enter Supervisor])
AND ((table1.TASK_TYPE)="PICKING"
Or (table1.TASK_TYPE)="REPLENISH")
OR table1.Task_Type IS NULL)
AND (((table1.TASK_STATUS)="AVL"
Or (table1.TASK_STATUS)="ASN")
OR Table1.Task_Status IS NULL)

GROUP BY USERS.PM, USERS.USER, USERS.NAME, table1.TASK_TYPE



Note that the sub-query in the FROM clause (a virtual table) is also a
valid
approach too.



Vanderghast, Access MVP


So if I understand you correctly, I am to add " Or Is Null" to the
criteria
for task_status and task_type and change the totaling for task type tp
a
"WHERE" function.
I tried this however the query has been running for >5 minutes and
still
has
not returned a result.

:

As mentioned by Dale, your WHERE clause (and your HAVING clause) undo
the
job made by the outer join. Indeed, after the join is made, values
under
table1.status can be null (due to the outer join) so, at least, you
should
also consider to add : OR table1.Task_Status IS NULL in the WHERE
clause
and OR table1.Task_Type IS NULL in the HAVING clause. Note also
that
your HAVING clause criteria could be moved into your WHERE clause
(and
doing
so, may make the execution time shorter)..


Vanderghast, Access MVP


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"));

 

[Michel Walsh]

In theory the JOIN is made before the WHERE clause, so the sub-query will
produce a smaller table before the join is ever involved, so, ending with a
smaller set after the join and so, less records will be ever implied in the
WHERE clause. BUT, the sub-query is likely to make any index not usable for
the join (since it is a virtual table, a such virtual table has no index).
So, in the end, only testing with real data may tell which one would be the
most efficient. Have also to consider easiness of maintenance when execution
time of the various alternatives is roughly similar.

As far as GROUPING on fields we don't SELECT, indeed, that may produce what
appear as two different rows, even WITHOUT any join at all. I mean, you can
have the same situation with a single table:

SELECT f1, COUNT(*)
FROM table
GROUP BY f1, f2


as example, can produce:

f1 COUNT(*)
Joe Blow 23
Joe Blow 16
Mary Jones 5
Mary Jones 51
Mary Jones 16


because of f2, not selected, which, one selected, could self-explain the
previous result:


f1 COUNT(*) f2
Joe Blow 23 CA
Joe Blow 16 OR
Mary Jones 5 UT
Mary Jones 51 CA
Mary Jones 16 OR


The OP defined his SELECT and GROUP clauses as, I assumed, was fitting for
his problem (and, again, is not something directly related to JOIN), so I
simply kept what he posted.



Vanderghast, Access MVP

Michael,

I understood the issue of the NULLs, although I've never done it that way
(always used the sub-query). I wonder what the query plan would look like
for the two, and which would be more efficient.

My point is that if the user (we'll say he is USER #2 and his name is Joe
Blow, and that his boss is Bob) is mapped to Assigned_To in multiple
records (lets say Task_Type = "Picking" in one record, and "Replenish" in
another record in table1), then the SQL you have generated would result in
something like the following, because you included the Task_Type in the
GroupBy clause, but didn't include it in the SELECT clause:

PM User Name CountOfTask_ID
Bob 2 Joe Blow 1
Bob 2 Joe Blow 1

Dale

A null is not equal to another null, but is not DISTINCT from another null
either (equality and DISTINCT are two different concepts, in SQL): that
means that unless there is a bug, for a GROUP made of a single field,
multiple records with a null will make only one group, not one group for
each of the multiple null. Also, if there is a matching USER, the left
join won't generate a NULL for any field associated to table1. This leaves
the case where table1 has INITIALLY a null value for one of its field (not
involved in the join), in particular, for Task_type, which can, in this
case, lead to possible ambiguity, indeed.


users.name=Joe Blow, table1.task_id = null

can thus mean that Joe, in table Users, is NOT in table1, or that he is
in table1, but associated to as null task_id.

If you insert

table1.Assigned_to


in the GROUP By clause, the field we join ON:

ON USERS.USER = table1.ASSIGNED_TO

then you can REMOVE THE AMBIGUITY :


users.Name = Joe Blow, table1.Assigned_to = null, table1.task_id =
null

is a record without match in table1


users.Name = Joe Blow, table1.Assigned_to = Joe Blow,
table1.task_id = null

is a record with an initial null for task_id.




Vanderghast, Access MVP

Michael,

Won't adding the Task_Type to the Group By clause result in
(potentially)
multiple rows for each PM/User, and no way to distinquish which
Task_Type the
count applies to (since the SELECT clause does not contain a Task_Type
field?

--
Dale

email address is invalid
Please reply to newsgroup only.



:

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 (((USERS.PM)=[Enter Supervisor])
AND ((table1.TASK_TYPE)="PICKING"
Or (table1.TASK_TYPE)="REPLENISH")
OR table1.Task_Type IS NULL)
AND (((table1.TASK_STATUS)="AVL"
Or (table1.TASK_STATUS)="ASN")
OR Table1.Task_Status IS NULL)

GROUP BY USERS.PM, USERS.USER, USERS.NAME, table1.TASK_TYPE



Note that the sub-query in the FROM clause (a virtual table) is also a
valid
approach too.



Vanderghast, Access MVP


So if I understand you correctly, I am to add " Or Is Null" to the
criteria
for task_status and task_type and change the totaling for task type
tp a
"WHERE" function.
I tried this however the query has been running for >5 minutes and
still
has
not returned a result.

:

As mentioned by Dale, your WHERE clause (and your HAVING clause)
undo the
job made by the outer join. Indeed, after the join is made, values
under
table1.status can be null (due to the outer join) so, at least, you
should
also consider to add : OR table1.Task_Status IS NULL in the WHERE
clause
and OR table1.Task_Type IS NULL in the HAVING clause. Note
also
that
your HAVING clause criteria could be moved into your WHERE clause
(and
doing
so, may make the execution time shorter)..


Vanderghast, Access MVP


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"));