Help Designing a Query to Count Classes

[Robert T]

Hello:

tblClasses
-----------

I thought designing this query would be easy, but it's turning out to be
harder than I anticipated. Obviously I'm doing something wrong.

We need to count the number of classes between 2 dates [Class_Date] field.
That's the easy part. Then we have to count the number of classes in each
category, based on the [Class_Status] field, that were

"Completed"
"Cancelled"
"Resecheduled"
"Cancelled - No Registrants"
"Cancelled - No Attendees"

The above are all of the possible choices for the [Class_Status] field. Can
someone please point me in the right direction.

Thanks

 

[John Spencer]

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status

In design view
-- Add your table
-- Add Class_Status field TWICE and Class_Date field once
-- Select View: Totals from the Menu
-- Change GROUP by under one Class_Status field to COUNT
-- Change GROUP BY under Class_Date to WHERE
-- Enter date range criteria under Class_Date

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Robert T]

Hi John:

Thanks for the prompt response. That query works as designed, it gives us
the total number of classes for each status. That's what we want, but there's
still a problem.

I can design a query that gives us the total number of classes during that
time frame. Or, I can design a query that gives us the total number of
classes for each status, which is the one you designed.

However, the part I cannot figure out is how to include the total number of
classes in the SAME query. In other words, take the query you designed and
also calculate the total number of ALL classes during that time frame.

Any suggestions?

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status

In design view
-- Add your table
-- Add Class_Status field TWICE and Class_Date field once
-- Select View: Totals from the Menu
-- Change GROUP by under one Class_Status field to COUNT
-- Change GROUP BY under Class_Date to WHERE
-- Enter date range criteria under Class_Date

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello:

tblClasses
-----------

I thought designing this query would be easy, but it's turning out to be
harder than I anticipated. Obviously I'm doing something wrong.

We need to count the number of classes between 2 dates [Class_Date] field.
That's the easy part. Then we have to count the number of classes in each
category, based on the [Class_Status] field, that were

"Completed"
"Cancelled"
"Resecheduled"
"Cancelled - No Registrants"
"Cancelled - No Attendees"

The above are all of the possible choices for the [Class_Status] field. Can
someone please point me in the right direction.

Thanks

 

[scubadiver]

Is this the kind of thing you are after?

Completed 15 72
Cancelled 18 72
Resecheduled 12 72
Cancelled - No Registrants 13 72
Cancelled - No Attendees 14 72

If this is the case, I think DSum would work.

Hi John:

Thanks for the prompt response. That query works as designed, it gives us
the total number of classes for each status. That's what we want, but there's
still a problem.

I can design a query that gives us the total number of classes during that
time frame. Or, I can design a query that gives us the total number of
classes for each status, which is the one you designed.

However, the part I cannot figure out is how to include the total number of
classes in the SAME query. In other words, take the query you designed and
also calculate the total number of ALL classes during that time frame.

Any suggestions?

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status

In design view
-- Add your table
-- Add Class_Status field TWICE and Class_Date field once
-- Select View: Totals from the Menu
-- Change GROUP by under one Class_Status field to COUNT
-- Change GROUP BY under Class_Date to WHERE
-- Enter date range criteria under Class_Date

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello:

tblClasses
-----------

I thought designing this query would be easy, but it's turning out to be
harder than I anticipated. Obviously I'm doing something wrong.

We need to count the number of classes between 2 dates [Class_Date] field.
That's the easy part. Then we have to count the number of classes in each
category, based on the [Class_Status] field, that were

"Completed"
"Cancelled"
"Resecheduled"
"Cancelled - No Registrants"
"Cancelled - No Attendees"

The above are all of the possible choices for the [Class_Status] field. Can
someone please point me in the right direction.

Thanks

 

[Robert T]

Scubadriver:

Except for the fact that I only need the total once, that's exactly what we
need.

So I can use DSum to create a calculated field in the same query John
designed?

Thanks,
Robert

Is this the kind of thing you are after?

Completed 15 72
Cancelled 18 72
Resecheduled 12 72
Cancelled - No Registrants 13 72
Cancelled - No Attendees 14 72

If this is the case, I think DSum would work.

Hi John:

Thanks for the prompt response. That query works as designed, it gives us
the total number of classes for each status. That's what we want, but there's
still a problem.

I can design a query that gives us the total number of classes during that
time frame. Or, I can design a query that gives us the total number of
classes for each status, which is the one you designed.

However, the part I cannot figure out is how to include the total number of
classes in the SAME query. In other words, take the query you designed and
also calculate the total number of ALL classes during that time frame.

Any suggestions?

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status

In design view
-- Add your table
-- Add Class_Status field TWICE and Class_Date field once
-- Select View: Totals from the Menu
-- Change GROUP by under one Class_Status field to COUNT
-- Change GROUP BY under Class_Date to WHERE
-- Enter date range criteria under Class_Date

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Robert T wrote:
Hello:

tblClasses
-----------

I thought designing this query would be easy, but it's turning out to be
harder than I anticipated. Obviously I'm doing something wrong.

We need to count the number of classes between 2 dates [Class_Date] field.
That's the easy part. Then we have to count the number of classes in each
category, based on the [Class_Status] field, that were

"Completed"
"Cancelled"
"Resecheduled"
"Cancelled - No Registrants"
"Cancelled - No Attendees"

The above are all of the possible choices for the [Class_Status] field. Can
someone please point me in the right direction.

Thanks

 

[scubadiver]

Calculated columns don't work like that unfortunately.

Scubadriver:

Except for the fact that I only need the total once, that's exactly what we
need.

So I can use DSum to create a calculated field in the same query John
designed?

Thanks,
Robert

Is this the kind of thing you are after?

Completed 15 72
Cancelled 18 72
Resecheduled 12 72
Cancelled - No Registrants 13 72
Cancelled - No Attendees 14 72

If this is the case, I think DSum would work.

Hi John:

Thanks for the prompt response. That query works as designed, it gives us
the total number of classes for each status. That's what we want, but there's
still a problem.

I can design a query that gives us the total number of classes during that
time frame. Or, I can design a query that gives us the total number of
classes for each status, which is the one you designed.

However, the part I cannot figure out is how to include the total number of
classes in the SAME query. In other words, take the query you designed and
also calculate the total number of ALL classes during that time frame.

Any suggestions?



:

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status

In design view
-- Add your table
-- Add Class_Status field TWICE and Class_Date field once
-- Select View: Totals from the Menu
-- Change GROUP by under one Class_Status field to COUNT
-- Change GROUP BY under Class_Date to WHERE
-- Enter date range criteria under Class_Date

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Robert T wrote:
Hello:

tblClasses
-----------

I thought designing this query would be easy, but it's turning out to be
harder than I anticipated. Obviously I'm doing something wrong.

We need to count the number of classes between 2 dates [Class_Date] field.
That's the easy part. Then we have to count the number of classes in each
category, based on the [Class_Status] field, that were

"Completed"
"Cancelled"
"Resecheduled"
"Cancelled - No Registrants"
"Cancelled - No Attendees"

The above are all of the possible choices for the [Class_Status] field. Can
someone please point me in the right direction.

Thanks

 

[Bob Barrows [MVP]]

I would probably use a union query if there was some reason not to use a
calculated field in a report:

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status
Union
SELECT 'All Classes' as Class_Status,
Count(*) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#

Scubadriver:

Except for the fact that I only need the total once, that's exactly
what we
need.

So I can use DSum to create a calculated field in the same query John
designed?

Thanks,
Robert

Is this the kind of thing you are after?

Completed 15 72
Cancelled 18 72
Resecheduled 12 72
Cancelled - No Registrants 13 72
Cancelled - No Attendees 14 72

If this is the case, I think DSum would work.

Hi John:

Thanks for the prompt response. That query works as designed, it
gives us
the total number of classes for each status. That's what we want,
but there's still a problem.

I can design a query that gives us the total number of classes
during that time frame. Or, I can design a query that gives us the
total number of
classes for each status, which is the one you designed.

However, the part I cannot figure out is how to include the total
number of classes in the SAME query. In other words, take the query
you designed and also calculate the total number of ALL classes
during that time frame.

Any suggestions?



:

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status

In design view
-- Add your table
-- Add Class_Status field TWICE and Class_Date field once
-- Select View: Totals from the Menu
-- Change GROUP by under one Class_Status field to COUNT
-- Change GROUP BY under Class_Date to WHERE
-- Enter date range criteria under Class_Date

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Robert T wrote:
Hello:

tblClasses
-----------

I thought designing this query would be easy, but it's turning
out to be harder than I anticipated. Obviously I'm doing
something wrong.

We need to count the number of classes between 2 dates
[Class_Date] field. That's the easy part. Then we have to count
the number of classes in each category, based on the
[Class_Status] field, that were

"Completed"
"Cancelled"
"Resecheduled"
"Cancelled - No Registrants"
"Cancelled - No Attendees"

The above are all of the possible choices for the [Class_Status]
field. Can someone please point me in the right direction.

Thanks

 

[Robert T]

Bob:

Now that's what I call creative thinking.

Thanks so much,
Robert

I would probably use a union query if there was some reason not to use a
calculated field in a report:

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status
Union
SELECT 'All Classes' as Class_Status,
Count(*) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#

Scubadriver:

Except for the fact that I only need the total once, that's exactly
what we
need.

So I can use DSum to create a calculated field in the same query John
designed?

Thanks,
Robert

Is this the kind of thing you are after?

Completed 15 72
Cancelled 18 72
Resecheduled 12 72
Cancelled - No Registrants 13 72
Cancelled - No Attendees 14 72

If this is the case, I think DSum would work.

:

Hi John:

Thanks for the prompt response. That query works as designed, it
gives us
the total number of classes for each status. That's what we want,
but there's still a problem.

I can design a query that gives us the total number of classes
during that time frame. Or, I can design a query that gives us the
total number of
classes for each status, which is the one you designed.

However, the part I cannot figure out is how to include the total
number of classes in the SAME query. In other words, take the query
you designed and also calculate the total number of ALL classes
during that time frame.

Any suggestions?



:

SELECT Class_Status, Count(Class_Status) as TheCount
FROM [ClassesTable]
WHERE Class_Date Between #1/1/2007# and #12/31/2008#
GROUP BY Class_Status

In design view
-- Add your table
-- Add Class_Status field TWICE and Class_Date field once
-- Select View: Totals from the Menu
-- Change GROUP by under one Class_Status field to COUNT
-- Change GROUP BY under Class_Date to WHERE
-- Enter date range criteria under Class_Date

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Robert T wrote:
Hello:

tblClasses
-----------

I thought designing this query would be easy, but it's turning
out to be harder than I anticipated. Obviously I'm doing
something wrong.

We need to count the number of classes between 2 dates
[Class_Date] field. That's the easy part. Then we have to count
the number of classes in each category, based on the
[Class_Status] field, that were

"Completed"
"Cancelled"
"Resecheduled"
"Cancelled - No Registrants"
"Cancelled - No Attendees"

The above are all of the possible choices for the [Class_Status]
field. Can someone please point me in the right direction.

Thanks

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"