Using Group By in SQL (Access 2000 Win 2000 SR-1)

Topic

New Reply

This is more of a SQL/asp question as that is where I am getting the problem but I thought Access people would know what’s going on here.

I am trying to pull some results from Access to a webpage via asp and thought it would be nice to use the Group By function to create blocks of data in a way similar to an Access report. If I run something like this:

strSQL = “SELECT Categories, CRCGType, MemberType, FullName FROM tblOutlookContacts Group BY Categories, CRCGType, MemberType”

I get this error:

Microsoft JET Database Engine error ‘80040e21’

You tried to execute a query that does not include the specified expression ‘FullName’ as part of an aggregate function.

I looked up a trouble-shooting website and got this advice:

If you are using an aggregate function (e.g. SUM, COUNT, MAX), then any other column in the SELECT list must also be in the GROUP BY clause. This is so that the database knows how to organize results.

Which makes NO SENSE to me because it seems perfectly logical to want to group a recordset by a some criteria but not ever one of them! After all, in access’ reports, you can group by certain headers on a report and the ‘body’ information will show all the records that match the grouped criteria.

For example, I’m trying to group the recordset to show all the contact information available (for example, the first names, addresses, etc.) for each group category or, as another way of saying it, show all contact info where Categories, CRCGType and MemberType are the same. So why is SQL requiring that EVERY piece of data in the recordset be grouped?

I am boggled by this…

Reply | Quote

Replies

A report is not a query.

The requirement is perfectly logical. Each field in a Totals query must either be used to group on (so that a separate record is returned for each unique combination of values of the Group By fields) or you must specify some way to aggregate the field – return the sum, count, minimum, maximum or one of the other aggregation functions (so that NOT each unique value is returned). If you neither specify Group By, nor an aggregation function, SQL can’t process the field – it simply doesn’t know what to do with it.

If you want a separate record for each FullName, you must use Group By on FullName too; if you want to return an arbitrary FullName from the records with the same Categories, CRCGType and MemberType, use First (or Last); if you want to use the first name (in alphabetic order), sort on FullName and specify Minimum.

Reply | Quote

well, I figured the limitation for Group By is by design and I’ll admit it makes sense (not that i have much choice) ; what’s frustrating is I want to do what a Report does without using Access Reports. If GroupBy doesn’t do it, I wonder what does…

I don’t want a separate record for each contact contained in a grouping, but a list of all contacts that are contained in a grouping. I could do a SQL for each possible grouping but that would be highly tedious and difficult to maintain.

Reply | Quote

Since you are using this in ASP, you should be able to use nested loops – an outer loop through the records grouped on Categories, CRCGType and MemberType (but not on FullName), and an inner loop through the contacts within the group (open a second recordset with a WHERE condition based on the values of Categories, CRCGType and MemberType from the first recordset). You don’t have to write out the SQL for each group.

Reply | Quote

yup, that’s right. I got caught up in using some GetRows code that wasn’t all that useful and decided to look again at the Access Report format and thought ‘Hey! GroupBy!’ BUT, I did find a method for porting asp results to an rtf file so things are going ok.

Reply | Quote

Well I finished up the project and learned a few things in the process. lesson no. 1: you can implement the kind of group and sort Access Reports does in the background in asp using the MSDataShape provider. In fact, Access uses DataShape in the background in doing this sort of thing. also, you can do all the programming stuff in .asp and have the whole shebang output to an .rtf file and from there go back to Word to complete the results. What you end up getting is a powerful and flexible method for doing Access-style grouping off a single db table. Very cool! I can get the kind of GroupBy function I wanted using a DataShape SQL string. All that’s needed to do this is an installed and functioning webserver so as to run .asp code. and, of course, some work in the area of .rtf markup for the output file.

Reply | Quote