problem with criteria in query

  • Thread starter Thread starter Brooke Williams
  • Start date Start date
B

Brooke Williams

I have a table that lists all employees and what days off they have and
whether they are full or part time. I have two queries that each count the
number of people with Monday, Tuesday, etc off, one for full time and one
for part time. That is all working fine. I have included the SQL for the
full time query. Where I am having a problem is that I want to calculate
percentages. I need the number of people off from the queries to have
criteria, but the total number of employees [count of name], to not have the
restrictions. Any ideas?

SELECT Count([EE info].Name) AS CountOfName, Count([EE info].Monday) AS
CountOfMonday, Count([EE info].Tuesday) AS CountOfTuesday, Count([EE
info].Wednesday) AS CountOfWednesday, Count([EE info].Thursday) AS
CountOfThursday, Count([EE info].Friday) AS CountOfFriday, Count([EE
info].Monday2) AS CountOfMonday2, Count([EE info].Tuesday2) AS
CountOfTuesday2, Count([EE info].Wednesday2) AS CountOfWednesday2, Count([EE
info].Thursday2) AS CountOfThursday2, Count([EE info].Friday2) AS
CountOfFriday2, [EE info].WS, [EE info].Dept
FROM [EE info]
GROUP BY [EE info].WS, [EE info].Dept;
HAVING ((([EE info].WS)="f")) OR ((([EE info].WS)="g"));
 
Brooke Williams said:
I have a table that lists all employees and what days off they have and
whether they are full or part time. I have two queries that each count the
number of people with Monday, Tuesday, etc off, one for full time and one
for part time. That is all working fine. I have included the SQL for the
full time query. Where I am having a problem is that I want to calculate
percentages. I need the number of people off from the queries to have
criteria, but the total number of employees [count of name], to not have the
restrictions. Any ideas?

SELECT Count([EE info].Name) AS CountOfName, Count([EE info].Monday) AS
CountOfMonday, Count([EE info].Tuesday) AS CountOfTuesday, Count([EE
info].Wednesday) AS CountOfWednesday, Count([EE info].Thursday) AS
CountOfThursday, Count([EE info].Friday) AS CountOfFriday, Count([EE
info].Monday2) AS CountOfMonday2, Count([EE info].Tuesday2) AS
CountOfTuesday2, Count([EE info].Wednesday2) AS CountOfWednesday2, Count([EE
info].Thursday2) AS CountOfThursday2, Count([EE info].Friday2) AS
CountOfFriday2, [EE info].WS, [EE info].Dept
FROM [EE info]
GROUP BY [EE info].WS, [EE info].Dept;
HAVING ((([EE info].WS)="f")) OR ((([EE info].WS)="g"));
Click to expand...
Hi Brooke,

You can do "anything" in a subquery as long as it is
wrapped in an aggregate function, for example:

SELECT
Max(SELECT Count(E.Name) FROM [EE info] As E) As TotalEmp,
Count([EE info].Name) AS TotalEmpWSDept,
Count([EE info].Monday) AS CountOfMonday,
Count([EE info].Tuesday) AS CountOfTuesday,
Count([EE info].Wednesday) AS CountOfWednesday,
Count([EE info].Thursday) AS CountOfThursday,
Count([EE info].Friday) AS CountOfFriday,
Count([EE info].Monday2) AS CountOfMonday2,
Count([EE info].Tuesday2) AS CountOfTuesday2,
Count([EE info].Wednesday2) AS CountOfWednesday2,
Count([EE info].Thursday2) AS CountOfThursday2,
Count([EE info].Friday2) AS CountOfFriday2,
[EE info].WS,
[EE info].Dept
FROM [EE info]
GROUP BY [EE info].WS, [EE info].Dept;
HAVING ((([EE info].WS)="f")) OR ((([EE info].WS)="g"));

Whether we used "Max" or something else, like "First",
was irrelevant since the subquery returns only one value.

Using this same technique, you also could get total
full time and total parttime employees.

It might also be a good idea to change the field name
from "Name" to some non-reserved word like "EmpName".
Somewhere down the line, using "Name" will come back
to bite you. Also, if you are not too far into your development,
I have never seen a tablename that needed a space to be
discerned -- use an underline if you must or "EEInfo"
Having to always use brackets because you have a space
in the table name may also come back to bite you also
(not meaning you are "wrong," just a suggestion)

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Back
Top