Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <font color=448800>strSQL = "SELECT Categories, CRCGType, MemberType, FullName FROM tblOutlookContacts Group BY Categories, CRCGType, MemberType"</font color=448800>

    I get this error:

    <font color=red>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.</font color=red>

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

    <font color=blue>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. </font color=blue>

    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... <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    well, I figured the limitation for Group By is by design and I'll admit it makes sense (not that i have much choice) <img src=/S/bash.gif border=0 alt=bash width=35 height=39> ; 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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •