A sum and a Count in the same Query

[LittleSmudge]

I have a Linking table that has three ForeignKey fields
( All three together make a conbined PK )

The three fields are
ListRef
PersonRef
KeywordRef


I need the total number of records in the table where KeywordRef = {data}
Ie( SELECT Count(Keywordref) FROM tblListKeyWords WHERE KeyWordRef = 'data' )

I also need the number of different PersonRef values in the above list

Ie ( SELECT Count(PersonRef) As CofP FROM tblListKeyWords WHERE KeyWordRef = 'data' GROUP BY PersonRef )


Ideally I'd like to combine these in a single shot - but whatever I try doesn't seem to want to work


I've got to

Dim rst As ADODB.Recordset
On Error GoTo Err_UpdateCounters
Set rst = New ADODB.Recordset

rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.ActiveConnection = CurrentProject.Connection

rst.Open "SELECT Count(RecordRef) As CountOfUsers, " _
       & "Sum(NoOfUsers) As CountOfPlaces " _
       & "FROM (SELECT RecordRef, Count(RecordRef) As NoOfUsers " _
             & "FROM tblListKeyword WHERE KeyWordRef = '" & strData & "' " _
             & "GROUP By RecordRef) As tmp "

If rst.EOF Then

At the rst.EOF line I get an error message < Operation is not allowed when the object is closed >


Can anyone see what I'm doing wrong - or suggest an easier path ?

I think this is a '.. .. wood for the trees' problem, I've been staring at it for too long.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

For some reason the code above now works find !

- put it down to operator error !



If anyone can see a simple, more logical way of doing it I'm always up for advice, but the 'problem' has gone away.


G.

 

[Darrylles]

Hi,

From the SQL complexity, I assume you can debug. Just in case however: right-click the 'rst.OPEN' line and choose 'Toggle Breakpoint' on.

Run the code - it'll stop at this line. Press F8 and it'll run it and stop at 'IF rst.EOF...'.

Hover your mouse-pointer over your 'Countof...' variables, this will display their contents. Any value in there?

I don't understand why you are checking for rst.EOF. After filling the recordset - you are not moving the record pointer anywhere, it's still pointing to the resultant single record.

Here, I would be checking that a result had been returned. i.e. IF(CountOfUsers <> 0) and (CountOfPlaces <>0) THEN...


Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[PHV]

< Operation is not allowed when the object is closed >
Seems that the rst.Open failed

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Sorry Darrylles - the rst.EOF was a bit of a side line.

I dropped in the
If rst.EOF Then
End If

just so that I had a dummy line to slap a Break Point onto.


All the usual Hover Over debuging failed because the rst.OPEN failed ( as PHV pointed out )

I narrowed it down to a problem with the ADODB.Connection in the end and the 'actual problem' is fixed.

But from a distance - is this a reasonable way of going about it ?
Or is there a simpler way that I'm just not seeing ?



G.