I'm trying to add another field, "Count(*) AS regCount", to the statement
below from a table called "sessionSignUpList" where the "sessionIDs" from
each table are equal. But, it's not working the ways I've tried. I'm using
an Access db which will be converted to SQL when I get things all worked
out.


	SELECT SL.sessionID, SL.classID, SL.dateStart, SL.dateEnd FROM
sessionList SL 
	WHERE (SL.dateStart>#3/6/03# And SL.dateStart<#1/23/04#) ORDER BY
SL.classID, SL.dateStart

I get an error when I try to do this

	SELECT SL.sessionID, SL.classID, SL.dateStart, SL.dateEnd,
SL.sessionSize, COUNT(*) AS regCount 
	FROM sessionList SL, sessionSignUpList SSL WHERE
(SL.dateStart>#3/6/03# And SL.dateStart<#1/23/04#) 
	AND SSL.sessionID = SL.sessionID ORDER BY SL.classID, SL.dateStart;


	Microsoft JET Database Engine error '80040e21' 

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

	/idtest/ss/gb_newReg.asp, line 64 


Thanks for any help,

Rich


This communication is for use by the intended recipient and contains 
information that may be privileged, confidential or copyrighted under
applicable law.  If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited.  Please notify the sender
by return e-mail and delete this e-mail from your system.  Unless
explicitly and conspicuously designated as "E-Contract Intended",
this e-mail does not constitute a contract offer, a contract amendment,
or an acceptance of a contract offer.  This e-mail does not constitute
a consent to the use of sender's contact information for direct marketing
purposes or for transfers of data to third parties.

 Francais Deutsch Italiano  Espanol  Portugues  Japanese  Chinese  Korean

            http://www.DuPont.com/corp/email_disclaimer.html

You need to add the other fields to a GROUP BY statement, as per the error
message.

For example if you want to turn

Field1    Field2
a             -
a             -
a             -
b            -

into:

Field1    Count(Field2)
a             3
b             1

then you need to use: GROUP BY Field1, which groups all the records by the
values in Field1, and calculates the corresponding counts for each value in
Field1.

I think what you want to do is (you need to put in your field from table b
into the statement, I also took the liberty of using ISO style dates, and 4
digit years, which you should be using)

SELECT
    a.sessionID,
    a.classID,
    a.dateStart,
    a.dateEnd,
    a.sessionSize,
    COUNT(b.someFieldHere) AS regCount
FROM
    sessionList AS SL
INNER JOIN
    sessionSignUpList AS SSL
ON
    a.SessionID = b.SessionID
WHERE
    (
        a.dateStart>#2003/06/03#
    AND
        a.dateStart<#2004/01/23#
    )
GROUP BY
     a.sessionID,
    a.classID,
    a.dateStart,
    a.dateEnd,
    a.sessionSize
ORDER BY
    a.classID,
    a.dateStart;

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Azinger, Richard" <richard.azinger@p...>
Subject: [asp_databases] Getting Count from one table along with other
information from another


:
: I'm trying to add another field, "Count(*) AS regCount", to the statement
: below from a table called "sessionSignUpList" where the "sessionIDs" from
: each table are equal. But, it's not working the ways I've tried. I'm using
: an Access db which will be converted to SQL when I get things all worked
: out.
:
:
: SELECT SL.sessionID, SL.classID, SL.dateStart, SL.dateEnd FROM
: sessionList SL
: WHERE (SL.dateStart>#3/6/03# And SL.dateStart<#1/23/04#) ORDER BY
: SL.classID, SL.dateStart
:
: I get an error when I try to do this
:
: SELECT SL.sessionID, SL.classID, SL.dateStart, SL.dateEnd,
: SL.sessionSize, COUNT(*) AS regCount
: FROM sessionList SL, sessionSignUpList SSL WHERE
: (SL.dateStart>#3/6/03# And SL.dateStart<#1/23/04#)
: AND SSL.sessionID = SL.sessionID ORDER BY SL.classID, SL.dateStart;
:
:
: Microsoft JET Database Engine error '80040e21'
:
: You tried to execute a query that does not include the specified
: expression 'sessionID' as part of an aggregate function.
:
: /idtest/ss/gb_newReg.asp, line 64
.