By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Using GroupBy, Min, Max, and Count in the same Query

P: n/a
I have noticed something odd on one of my queries. Maybe someone can
help or explain.

I am using the GroupBy function on all my rows except for pulling "Min"
StartDate, "Max" EndDate, and "Min" StartTime and "Max" StartTime.

Problem is though, when I also pull "Count" on the Enrolment column, in
the same query, I notice the Enrolment count is double or triple what
it is suppose to be. It looks like I can use either Min/Max, or Count,
but not both in the same query. Is this an SQL rule, or an Access
limitation? Is there a way around this?

Marcus
******

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
There is no such limitation. You can use any and all
aggregation functions in one query. Could you post the
query? Could you be querying two or more tables with an
incomplete join?

Kevin
Marcus<to*******@yahoo.ca> 4/12/2005 6:23:39 AM >>>

I have noticed something odd on one of my queries. Maybe
someone can
help or explain.

I am using the GroupBy function on all my rows except for
pulling "Min"
StartDate, "Max" EndDate, and "Min" StartTime and "Max"
StartTime.

Problem is though, when I also pull "Count" on the
Enrolment column, in
the same query, I notice the Enrolment count is double or
triple what
it is suppose to be. It looks like I can use either
Min/Max, or Count,
but not both in the same query. Is this an SQL rule, or an
Access
limitation? Is there a way around this?

Marcus
******

Nov 13 '05 #2

P: n/a
Luckily enough I was able to solve the problem. I simply created two
queries: one query with the enrolment column (using an aggregate
"count"), which excluded the date/time table; and the other query
pulling the start date, end date, and start time, and end time (using
the "Min" and "Max" aggregates), excluding the enrolment column.

I then created a third query which joined the first two queries via a
section_id column. I then extracted the date,time, and enrolment
columns in the new query.

All worked out well.

Marcus
*********

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.