Question on Group By

  • Thread starter Thread starter John Quinn
  • Start date Start date
J

John Quinn

Can you use an * for all the fields, or do you have to specify each field you
want to include in the query?

I get an error, does not include the specified expression when I show the
fields seperately?

Help will be appreciated.

Thanks

Q
 
John said:
Can you use an * for all the fields, or do you have to specify each field you
want to include in the query?

I get an error, does not include the specified expression when I show the
fields seperately?
Click to expand...


Yes, you can, but it's better if you specify each field.

You said the error refers to some expression,but you didn't
post the query with the expression so it's unclear what you
are really asking about.

If you are trying to group by a calculated field, then you
probably need to use Group By (or Expression?) for the
calculated field.
 
Set rstFedCl = db.OpenRecordset("SELECT * FROM FederalClass WHERE FedClass =
' ' Group BY ST")

The error I get is on the very first field. There are 127 Fields in the
record, I will be using all but 9 fields.

Thanks for Getting Back to Me.
 
As I posted elsewhere. YOU cannot use * to refer all fields in an
aggregate query. YOU must list each and every field when you are doing
an aggregate and in the GRoup by clause you have to list each specific
field that you are NOT applying an aggreate function (Avg, Sum, Count,
First, Last, ...) against.

For what you have posted you might as well use DISTINCT since you are
obviously not applying any aggregate function. Distinct should return
the same result as group by when you group by every field. AND it just
may be considerably faster.

SELECT DISTINCT *
FROM FederalClass
WHERE FedClass = ' '
ORDER BY ST

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

Thanks, DISTINCT works great. I was under the impression an
aggragate function was only when you use a MAX Function.

Thanks Again.
 
John said:
. I was under the impression an
aggragate function was only when you use a MAX Function.
Click to expand...

There are lots of aggregate functions besides Max (e.g.
Count, Sum, Min, Avg, ...) and you can create new ones if
the built in function don't do what you need.

You do not need to use Group By when the query's result set
is a single record with a single field. E.g.
SELECT Sum(amount) FROM sometable

But, as John said. since that's not the kind of thing you
are doing, you should not use GROUP BY
 
Back
Top