Getting a total from aquery

[Guest]

Hi

I currnelty have the following query

SELECT Employee1.INumber, Employee1.Name, Employee1.WC,
Count(WorkShopEmployee.WorkShopID) AS CountOfWorkShopID, Employee1.WC
FROM Employee1 INNER JOIN WorkShopEmployee ON Employee1.INumber =
WorkShopEmployee.INumber
WHERE (((Employee1.WC)=[Forms]![WC Entry Form2]![WC]))
GROUP BY Employee1.INumber, Employee1.Name, Employee1.WC;

This basically gives a count of how many workshops each emoyee in a WC has
attended.

I need now to get a total of all the workshops attended by the empoyees. I
have been struggling with this for a while and Iam just wondering if anyone
can help,

Thanks
Fiachra

 

[Guest]

Try this --
SELECT Employee1.INumber, Employee1.Name, Employee1.WC,
Count(WorkShopEmployee.WorkShopID) AS CountOfWorkShopID
FROM Employee1 LEFT JOIN WorkShopEmployee ON Employee1.INumber =
WorkShopEmployee.INumber
GROUP BY Employee1.INumber, Employee1.Name, Employee1.WC;

 

[Guest]

Hi Karl

sorry I might not have been that clear in what I was looking for, I have it
so it already count up how many workshops each employee went to, what I want
to do now is give a totol of the number of workshops that was attended by all
the employees added together so it would of be
the sum of Count(WorkShopEmployee.WorkShopID)
but I cannot get this to work I get an error subqueries cannot be used in
the expression

Try this --
SELECT Employee1.INumber, Employee1.Name, Employee1.WC,
Count(WorkShopEmployee.WorkShopID) AS CountOfWorkShopID
FROM Employee1 LEFT JOIN WorkShopEmployee ON Employee1.INumber =
WorkShopEmployee.INumber
GROUP BY Employee1.INumber, Employee1.Name, Employee1.WC;

Hi

I currnelty have the following query

SELECT Employee1.INumber, Employee1.Name, Employee1.WC,
Count(WorkShopEmployee.WorkShopID) AS CountOfWorkShopID, Employee1.WC
FROM Employee1 INNER JOIN WorkShopEmployee ON Employee1.INumber =
WorkShopEmployee.INumber
WHERE (((Employee1.WC)=[Forms]![WC Entry Form2]![WC]))
GROUP BY Employee1.INumber, Employee1.Name, Employee1.WC;

This basically gives a count of how many workshops each emoyee in a WC has
attended.

I need now to get a total of all the workshops attended by the empoyees. I
have been struggling with this for a while and Iam just wondering if anyone
can help,

Thanks
Fiachra

 

[Guest]

Somewhere along the way I got lost. What do you want the results to look
like?

Hi Karl

sorry I might not have been that clear in what I was looking for, I have it
so it already count up how many workshops each employee went to, what I want
to do now is give a totol of the number of workshops that was attended by all
the employees added together so it would of be
the sum of Count(WorkShopEmployee.WorkShopID)
but I cannot get this to work I get an error subqueries cannot be used in
the expression

Try this --
SELECT Employee1.INumber, Employee1.Name, Employee1.WC,
Count(WorkShopEmployee.WorkShopID) AS CountOfWorkShopID
FROM Employee1 LEFT JOIN WorkShopEmployee ON Employee1.INumber =
WorkShopEmployee.INumber
GROUP BY Employee1.INumber, Employee1.Name, Employee1.WC;

Hi

I currnelty have the following query

SELECT Employee1.INumber, Employee1.Name, Employee1.WC,
Count(WorkShopEmployee.WorkShopID) AS CountOfWorkShopID, Employee1.WC
FROM Employee1 INNER JOIN WorkShopEmployee ON Employee1.INumber =
WorkShopEmployee.INumber
WHERE (((Employee1.WC)=[Forms]![WC Entry Form2]![WC]))
GROUP BY Employee1.INumber, Employee1.Name, Employee1.WC;

This basically gives a count of how many workshops each emoyee in a WC has
attended.

I need now to get a total of all the workshops attended by the empoyees. I
have been struggling with this for a while and Iam just wondering if anyone
can help,

Thanks
Fiachra