How many classes, for average attendance per class 1

[topwaya]

I need to determine how many CLASSES there were between certain dates so I can get an average attendance per class. I can't figure out how to go about it. Here is my current query:

SELECT qryGrpEx.GExLoc, tblGrpExCls.ClsNames, Format([GExD],"dddd") AS [Day], qryGrpEx.GExTme, Count(qryGrpEx.PID) AS [Total Attendance]
FROM qryGrpEx INNER JOIN tblGrpExCls ON qryGrpEx.GExCls = tblGrpExCls.ClsCode
WHERE (((qryGrpEx.GExD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY qryGrpEx.GExLoc, tblGrpExCls.ClsNames, Format([GExD],"dddd"), qryGrpEx.GExTme
ORDER BY qryGrpEx.GExLoc;

GExD = Date of class
GExTme = Time of class
GExCls = Name of class
GExLoc = Location of class

Any help is appreciated! Thank you!!!

 

[PHV]

To have a count per day:
SELECT Format([GExD],"dddd") AS [Day], Count(PID) AS [Total Attendance]
FROM qryGrpEx
WHERE GExD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]
GROUP BY [GExD];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thank you for your quick response!!

I already have my query giving me total attendance for the various classes, and I need to keep that.

Do I need to make a different query to figure out the number of classes?

Also, I can't figure out classes by DAY because there are different classes on the same day at different times and at different locations.

Each individual class has a unique combination of:
GExD = Date of class
GExTme = Time of class
GExCls = Name of class
GExLoc = Location of class

Thanks for any help!!

 

[PHV]

Something like this ?
SELECT Format([GExD],"dddd") AS [Day], Count(*) AS [number of classes]
FROM (
SELECT DISTINCT GExD, GExTme, GExCls,GExLoc FROM qryGrpEx
WHERE GExD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]
) AS D
GROUP BY [GExD];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thanks very much for your help.

Here is my current query:

SELECT D.GExLoc, D.GExCls, Format([GExD],"dddd") AS [Day], D.GExTme, Count(*) AS [numberofclasses]
FROM [SELECT DISTINCT GExD, GExTme, GExCls,GExLoc FROM qryGrpEx
WHERE GExD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]
]. AS D
GROUP BY D.GExLoc, D.GExCls, D.GExTme, D.GExD;

It displays:

GExLoc GExCls Day GRPEXTme numberofclasses
Bartow BodyPump Saturday 12:00:00 PM 1
Bartow BodyPump Saturday 12:00:00 PM 1
Bartow BodyPump Saturday 12:00:00 PM 1
Riverview BodyPump Saturday 12:00:00 PM 1
Riverview Step Friday 12:00:00 PM 1
Riverview Step Friday 12:00:00 PM 1
Riverview Step Friday 5:00:00 PM 1
Riverview Walk/Run Saturday 12:00:00 PM 1

I need it to display:

GExLoc GExCls Day GRPEXTme numberofclasses
Bartow BodyPump Saturday 12:00:00 PM 3
Riverview BodyPump Saturday 12:00:00 PM 1
Riverview Step Friday 12:00:00 PM 2
Riverview Step Friday 5:00:00 PM 1
Riverview Walk/Run Saturday 12:00:00 PM 1

Thanks for all your help!

 

[PHV]

And this ?
GROUP BY D.GExLoc, D.GExCls, [highlight]D.GExD, D.GExTme[/highlight];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Sorry - I'm a newbie, not sure what I'm doing.... thank you for your patience.

I can't just say:
Saturday has 3 classes.

I need to know that
Bartow's Step Class on Saturday at 12noon had 3 classes between these dates.
and
Riverview's BodyPump Class on Saturday at 12noon had 1 class between these dates.
and
Riverview's Step Class on Friday at 12noon had 2 classes between these dates.

When I try to remove the GExD (date) from the GROUP BY, I get an aggregate function error, when I leave it in it lists all the classes instead of grouping them without the date of the class.

Am I completely off base? I don't know - but I do appreciate all your help, this forum has been invaluable!

 

[PHV]

I suggested to replace this:
GROUP BY D.GExLoc, D.GExCls, D.GExTme, D.GExD;
By this:
GROUP BY D.GExLoc, D.GExCls, D.GExD, D.GExTme;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thank you for your patience.

I have:

SELECT D.GExLoc, D.GExCls, Format([GExD],"dddd") AS [Day], D.GExD, D.GExTme, Count(*) AS [number of classes]
FROM [SELECT DISTINCT GExD, GExTme, GExCls,GExLoc FROM qryGrpEx
WHERE GExD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]
]. AS D
GROUP BY D.GExLoc, D.GExCls, D.GExD, D.GExTme;

And it displays:

GExLoc GExCls Day GRPEX Date GRP EX Time number of classes
Bartow Body Pump Saturday 1 /1 /2005 12:00:00 PM 1
Bartow Body Pump Saturday 1 /8 /2005 12:00:00 PM 1
Bartow Body Pump Saturday 1 /15/2005 12:00:00 PM 1
Riverview Body Pump Saturday 1 /1 /2005 12:00:00 PM 1
Riverview Step Friday 1 /7 /2005 5:00:00 PM 1
Riverview Step Friday 1 /14/2005 12:00:00 PM 1
Riverview Step Friday 1 /21/2005 12:00:00 PM 1
Riverview Walk / Run Saturday 1 /1 /2005 12:00:00 PM 1

When I take out GExD (because I believe the actual DATE is messing me up) I get a "You tried execute a query that does not include the specified expression "Format([GExD],"dddd")" as part of an aggregate function.

I want to know the DAY based on the DATE, but I don't want to group by DATE. It is clear as mud to me

Thanks for your patience and help!!

 

[PHV]

And this ?
SELECT D.GExLoc, D.GExCls, Format([GExD],"dddd") AS [Day], D.GExTme, Count(*) AS [number of classes]
FROM [SELECT DISTINCT GExD, GExTme, GExCls,GExLoc FROM qryGrpEx
WHERE GExD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]
]. AS D
GROUP BY D.GExLoc, D.GExCls, Format([GExD],"dddd"), D.GExTme;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

I get it, that's what it meant - I have to write the aggregate function in the GROUP BY. Cool - thanks so much it works like charm! YEAH!!!

SELECT D.GExLoc, D.GExCls, Format([GExD],"dddd") AS [Day], D.GExTme, Count(*) AS [number of classes]
FROM (SELECT DISTINCT GExD, GExTme, GExCls,GExLoc FROM qryGrpEx
WHERE GExD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]) AS D
GROUP BY D.GExLoc, D.GExCls, Format([GExD],"dddd"), D.GExTme;

Now, I'm trying to also display the Total Attendance in the same query. Currently it is displayed in the qryGrpEx query as:

SELECT qryGrpEx.GExLoc, qryGrpEx.GExCls, Format([GExD],"dddd") AS [Day], qryGrpEx.GExTme, Count(qryGrpEx.PID) AS [Total Attendance]
FROM qryGrpEx
WHERE (((qryGrpEx.GExD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY qryGrpEx.GExLoc, qryGrpEx.GExCls, Format([GExD],"dddd"), qryGrpEx.GExTme
ORDER BY qryGrpEx.GExLoc;

Do I need to two queries? Or can I have just one for the total classes as well as the total attendance? I've tried to combine the queries... but I end up getting the same numbers for both total classes and total attendance.

Thanks so much for all your help!!!!!!!!!!

 

[PHV]

Perhaps this ?
SELECT D.GExLoc, D.GExCls, Format([GExD],"dddd") AS [Day], D.GExTme, Count(*) AS [number of classes], Sum(totPID) AS [Total Attendance]
FROM (SELECT GExLoc,GExCls,GExD,GExTme,Count(PID) AS totPID FROM qryGrpEx
WHERE GExD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]
GROUP BY GExLoc,GExCls,GExD,GExTme) AS D
GROUP BY D.GExLoc, D.GExCls, Format([GExD],"dddd"), D.GExTme;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

You are a genius!
I kinda sorta understand what you did, but not exactly - but it certainly works!!!!

THANKS MUCH!!!