Need to Count by two different criteria in same Totals query

  • Thread starter Thread starter Steve Vincent
  • Start date Start date
S

Steve Vincent

I have a Totals query (see SQL below) that Counts how many people are SIGNED
UP (where "S" is in the [Status] field) for each class. This is the basis
for a "Number of Signups/Openings per Class" report. Now I want to add how
many people are on the WAITING LIST (where "WL" is in the [Status] field) for
each class, to my query or report.

I'm having trouble setting two different criteria for the same field (the
[Status] field -- one for "Signed Up", where "S" is in the [Status] field,
and one for "Waiing List" where "WL" is in the [Status] field). I have tried
adding the [Status] field twice to my query, but that didn't seem to work.

In searching this discussion group, one person suggested adding the same
table twice to the query, but I have three tables in my query (Employees,
Signups and Classes), so that didn't seem to work (I tried adding the Signups
table twice...). My latest attempt was to create two different Totals
queries, one to count "Signed Up" and the other to count "Waiting List", but
when I add these two queries as the data for a new query, the query results
add a "Waiting List" entry (a "1") for each record where there are any
signups, even if there is nobody on the Waiting list for that class. So, the
query results are not accurate. The separate Totals queries run just fine,
and yield the results I want (number of signups, and number of wait-listed
people), but combing these two is throwing me for a loop.

I'm thinking about creating a sub-report just to add the "Wait list" numbers
to each class-record in my report, but that seems like a lot of work for just
one piece of data that I should be able to get on the query level. But at
this point, I don't really care if the number crunching is done in the query
on on the report.

Any ideas? Your help would be GREATLY appreciated. Thanks in advance,

Steve


SELECT [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], Count([Signups Table].[Employee
ID]) AS [CountOfEmployee ID], [Classes Table].Location, [Classes
Table].[Class ID], [Classes Table].[Class Status], [Classes Table].[Size
Limit], IIf(Count([signups table]![employee id])=[size
limit],"Full",IIf(Count([signups table]![employee id])>[size
limit],"Overbooked","")) AS [Size Status]
FROM [Employees Table] INNER JOIN ([Classes Table] INNER JOIN [Signups
Table] ON [Classes Table].[Class ID] = [Signups Table].[Class ID]) ON
[Employees Table].[Employee ID] = [Signups Table].[Employee ID]
WHERE ((([Signups Table].Status)="s"))
GROUP BY [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], [Classes Table].Location,
[Classes Table].[Class ID], [Classes Table].[Class Status], [Classes
Table].[Size Limit], [Classes Table].[Class ID]
ORDER BY [Classes Table].Date, [Classes Table].[System Time];
 
Remove the WHERE statement and replace - Count([Signups Table].[Employee ID])
AS [CountOfEmployee ID], with --
Sum(IIF([Signups Table].[Status]="s", 1, 0)) AS [Signed_Up],
Sum(IIF([Signups Table].[Status]="WL", 1, 0) AS [Waiing_List],


Steve Vincent said:
I have a Totals query (see SQL below) that Counts how many people are SIGNED
UP (where "S" is in the [Status] field) for each class. This is the basis
for a "Number of Signups/Openings per Class" report. Now I want to add how
many people are on the WAITING LIST (where "WL" is in the [Status] field) for
each class, to my query or report.

I'm having trouble setting two different criteria for the same field (the
[Status] field -- one for "Signed Up", where "S" is in the [Status] field,
and one for "Waiing List" where "WL" is in the [Status] field). I have tried
adding the [Status] field twice to my query, but that didn't seem to work.

In searching this discussion group, one person suggested adding the same
table twice to the query, but I have three tables in my query (Employees,
Signups and Classes), so that didn't seem to work (I tried adding the Signups
table twice...). My latest attempt was to create two different Totals
queries, one to count "Signed Up" and the other to count "Waiting List", but
when I add these two queries as the data for a new query, the query results
add a "Waiting List" entry (a "1") for each record where there are any
signups, even if there is nobody on the Waiting list for that class. So, the
query results are not accurate. The separate Totals queries run just fine,
and yield the results I want (number of signups, and number of wait-listed
people), but combing these two is throwing me for a loop.

I'm thinking about creating a sub-report just to add the "Wait list" numbers
to each class-record in my report, but that seems like a lot of work for just
one piece of data that I should be able to get on the query level. But at
this point, I don't really care if the number crunching is done in the query
on on the report.

Any ideas? Your help would be GREATLY appreciated. Thanks in advance,

Steve


SELECT [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], Count([Signups Table].[Employee
ID]) AS [CountOfEmployee ID], [Classes Table].Location, [Classes
Table].[Class ID], [Classes Table].[Class Status], [Classes Table].[Size
Limit], IIf(Count([signups table]![employee id])=[size
limit],"Full",IIf(Count([signups table]![employee id])>[size
limit],"Overbooked","")) AS [Size Status]
FROM [Employees Table] INNER JOIN ([Classes Table] INNER JOIN [Signups
Table] ON [Classes Table].[Class ID] = [Signups Table].[Class ID]) ON
[Employees Table].[Employee ID] = [Signups Table].[Employee ID]
WHERE ((([Signups Table].Status)="s"))
GROUP BY [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], [Classes Table].Location,
[Classes Table].[Class ID], [Classes Table].[Class Status], [Classes
Table].[Size Limit], [Classes Table].[Class ID]
ORDER BY [Classes Table].Date, [Classes Table].[System Time];
Click to expand...
 
Karl,

Thank you so much for your reply... I'm hoping it will work. When I made
the changes you recommend, I got the following error:

Syntax error (missing operator) in query expression 'Sum(IIF([Signups
Table].[Status]="WL", 1, 0) AS [Waiing_List], [Classes Table].Location,
[Classes Table].[Class ID], [Classes Table].[Class Status], [Classes
Table].[Size Limit], IIf(Count([signups table]![employee id])=[size
limit],"Full",IIf(Count([sign'.

I'm trying to troubleshoot it here... but so far haven't found the typo or
operator.


KARL DEWEY said:
Remove the WHERE statement and replace - Count([Signups Table].[Employee ID])
AS [CountOfEmployee ID], with --
Sum(IIF([Signups Table].[Status]="s", 1, 0)) AS [Signed_Up],
Sum(IIF([Signups Table].[Status]="WL", 1, 0) AS [Waiing_List],


Steve Vincent said:
I have a Totals query (see SQL below) that Counts how many people are SIGNED
UP (where "S" is in the [Status] field) for each class. This is the basis
for a "Number of Signups/Openings per Class" report. Now I want to add how
many people are on the WAITING LIST (where "WL" is in the [Status] field) for
each class, to my query or report.

I'm having trouble setting two different criteria for the same field (the
[Status] field -- one for "Signed Up", where "S" is in the [Status] field,
and one for "Waiing List" where "WL" is in the [Status] field). I have tried
adding the [Status] field twice to my query, but that didn't seem to work.

In searching this discussion group, one person suggested adding the same
table twice to the query, but I have three tables in my query (Employees,
Signups and Classes), so that didn't seem to work (I tried adding the Signups
table twice...). My latest attempt was to create two different Totals
queries, one to count "Signed Up" and the other to count "Waiting List", but
when I add these two queries as the data for a new query, the query results
add a "Waiting List" entry (a "1") for each record where there are any
signups, even if there is nobody on the Waiting list for that class. So, the
query results are not accurate. The separate Totals queries run just fine,
and yield the results I want (number of signups, and number of wait-listed
people), but combing these two is throwing me for a loop.

I'm thinking about creating a sub-report just to add the "Wait list" numbers
to each class-record in my report, but that seems like a lot of work for just
one piece of data that I should be able to get on the query level. But at
this point, I don't really care if the number crunching is done in the query
on on the report.

Any ideas? Your help would be GREATLY appreciated. Thanks in advance,

Steve


SELECT [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], Count([Signups Table].[Employee
ID]) AS [CountOfEmployee ID], [Classes Table].Location, [Classes
Table].[Class ID], [Classes Table].[Class Status], [Classes Table].[Size
Limit], IIf(Count([signups table]![employee id])=[size
limit],"Full",IIf(Count([signups table]![employee id])>[size
limit],"Overbooked","")) AS [Size Status]
FROM [Employees Table] INNER JOIN ([Classes Table] INNER JOIN [Signups
Table] ON [Classes Table].[Class ID] = [Signups Table].[Class ID]) ON
[Employees Table].[Employee ID] = [Signups Table].[Employee ID]
WHERE ((([Signups Table].Status)="s"))
GROUP BY [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], [Classes Table].Location,
[Classes Table].[Class ID], [Classes Table].[Class Status], [Classes
Table].[Size Limit], [Classes Table].[Class ID]
ORDER BY [Classes Table].Date, [Classes Table].[System Time];
Click to expand...
Click to expand...
 
Left off second parenthsis --
Sum(IIF([Signups Table].[Status]="WL", 1, 0)) AS [Waiing_List],


KARL DEWEY said:
Remove the WHERE statement and replace - Count([Signups Table].[Employee ID])
AS [CountOfEmployee ID], with --
Sum(IIF([Signups Table].[Status]="s", 1, 0)) AS [Signed_Up],
Sum(IIF([Signups Table].[Status]="WL", 1, 0) AS [Waiing_List],


Steve Vincent said:
I have a Totals query (see SQL below) that Counts how many people are SIGNED
UP (where "S" is in the [Status] field) for each class. This is the basis
for a "Number of Signups/Openings per Class" report. Now I want to add how
many people are on the WAITING LIST (where "WL" is in the [Status] field) for
each class, to my query or report.

I'm having trouble setting two different criteria for the same field (the
[Status] field -- one for "Signed Up", where "S" is in the [Status] field,
and one for "Waiing List" where "WL" is in the [Status] field). I have tried
adding the [Status] field twice to my query, but that didn't seem to work.

In searching this discussion group, one person suggested adding the same
table twice to the query, but I have three tables in my query (Employees,
Signups and Classes), so that didn't seem to work (I tried adding the Signups
table twice...). My latest attempt was to create two different Totals
queries, one to count "Signed Up" and the other to count "Waiting List", but
when I add these two queries as the data for a new query, the query results
add a "Waiting List" entry (a "1") for each record where there are any
signups, even if there is nobody on the Waiting list for that class. So, the
query results are not accurate. The separate Totals queries run just fine,
and yield the results I want (number of signups, and number of wait-listed
people), but combing these two is throwing me for a loop.

I'm thinking about creating a sub-report just to add the "Wait list" numbers
to each class-record in my report, but that seems like a lot of work for just
one piece of data that I should be able to get on the query level. But at
this point, I don't really care if the number crunching is done in the query
on on the report.

Any ideas? Your help would be GREATLY appreciated. Thanks in advance,

Steve


SELECT [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], Count([Signups Table].[Employee
ID]) AS [CountOfEmployee ID], [Classes Table].Location, [Classes
Table].[Class ID], [Classes Table].[Class Status], [Classes Table].[Size
Limit], IIf(Count([signups table]![employee id])=[size
limit],"Full",IIf(Count([signups table]![employee id])>[size
limit],"Overbooked","")) AS [Size Status]
FROM [Employees Table] INNER JOIN ([Classes Table] INNER JOIN [Signups
Table] ON [Classes Table].[Class ID] = [Signups Table].[Class ID]) ON
[Employees Table].[Employee ID] = [Signups Table].[Employee ID]
WHERE ((([Signups Table].Status)="s"))
GROUP BY [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], [Classes Table].Location,
[Classes Table].[Class ID], [Classes Table].[Class Status], [Classes
Table].[Size Limit], [Classes Table].[Class ID]
ORDER BY [Classes Table].Date, [Classes Table].[System Time];
Click to expand...
Click to expand...
 
Karl,

Made the correction, and it works like a charm! Your very fast help is
greatly appreciated -- thank you!!

Steve

KARL DEWEY said:
Left off second parenthsis --
Sum(IIF([Signups Table].[Status]="WL", 1, 0)) AS [Waiing_List],


KARL DEWEY said:
Remove the WHERE statement and replace - Count([Signups Table].[Employee ID])
AS [CountOfEmployee ID], with --
Sum(IIF([Signups Table].[Status]="s", 1, 0)) AS [Signed_Up],
Sum(IIF([Signups Table].[Status]="WL", 1, 0) AS [Waiing_List],


Steve Vincent said:
I have a Totals query (see SQL below) that Counts how many people are SIGNED
UP (where "S" is in the [Status] field) for each class. This is the basis
for a "Number of Signups/Openings per Class" report. Now I want to add how
many people are on the WAITING LIST (where "WL" is in the [Status] field) for
each class, to my query or report.

I'm having trouble setting two different criteria for the same field (the
[Status] field -- one for "Signed Up", where "S" is in the [Status] field,
and one for "Waiing List" where "WL" is in the [Status] field). I have tried
adding the [Status] field twice to my query, but that didn't seem to work.

In searching this discussion group, one person suggested adding the same
table twice to the query, but I have three tables in my query (Employees,
Signups and Classes), so that didn't seem to work (I tried adding the Signups
table twice...). My latest attempt was to create two different Totals
queries, one to count "Signed Up" and the other to count "Waiting List", but
when I add these two queries as the data for a new query, the query results
add a "Waiting List" entry (a "1") for each record where there are any
signups, even if there is nobody on the Waiting list for that class. So, the
query results are not accurate. The separate Totals queries run just fine,
and yield the results I want (number of signups, and number of wait-listed
people), but combing these two is throwing me for a loop.

I'm thinking about creating a sub-report just to add the "Wait list" numbers
to each class-record in my report, but that seems like a lot of work for just
one piece of data that I should be able to get on the query level. But at
this point, I don't really care if the number crunching is done in the query
on on the report.

Any ideas? Your help would be GREATLY appreciated. Thanks in advance,

Steve


SELECT [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], Count([Signups Table].[Employee
ID]) AS [CountOfEmployee ID], [Classes Table].Location, [Classes
Table].[Class ID], [Classes Table].[Class Status], [Classes Table].[Size
Limit], IIf(Count([signups table]![employee id])=[size
limit],"Full",IIf(Count([signups table]![employee id])>[size
limit],"Overbooked","")) AS [Size Status]
FROM [Employees Table] INNER JOIN ([Classes Table] INNER JOIN [Signups
Table] ON [Classes Table].[Class ID] = [Signups Table].[Class ID]) ON
[Employees Table].[Employee ID] = [Signups Table].[Employee ID]
WHERE ((([Signups Table].Status)="s"))
GROUP BY [Classes Table].[Class Name], [Classes Table].Date, [Classes
Table].Time, [Classes Table].[System Time], [Classes Table].Location,
[Classes Table].[Class ID], [Classes Table].[Class Status], [Classes
Table].[Size Limit], [Classes Table].[Class ID]
ORDER BY [Classes Table].Date, [Classes Table].[System Time];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top