If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help Designing a Query to Count Classes
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 |
#2
|
|||
|
|||
Help Designing a Query to Count Classes
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 |
#3
|
|||
|
|||
Help Designing a Query to Count Classes
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? "John Spencer" wrote: 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 |
#4
|
|||
|
|||
Help Designing a Query to Count Classes
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. "Robert T" wrote: 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? "John Spencer" wrote: 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 |
#5
|
|||
|
|||
Help Designing a Query to Count Classes
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 "scubadiver" wrote: 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. "Robert T" wrote: 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? "John Spencer" wrote: 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 |
#6
|
|||
|
|||
Help Designing a Query to Count Classes
Calculated columns don't work like that unfortunately. "Robert T" wrote: 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 "scubadiver" wrote: 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. "Robert T" wrote: 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? "John Spencer" wrote: 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 |
#7
|
|||
|
|||
Help Designing a Query to Count Classes
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# Robert T wrote: 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 "scubadiver" wrote: 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. "Robert T" wrote: 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? "John Spencer" wrote: 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" |
#8
|
|||
|
|||
Help Designing a Query to Count Classes
Bob:
Now that's what I call creative thinking. Thanks so much, Robert "Bob Barrows [MVP]" wrote: 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# Robert T wrote: 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 "scubadiver" wrote: 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. "Robert T" wrote: 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? "John Spencer" wrote: 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" |
Thread Tools | |
Display Modes | |
|
|