SQL Returns Different Results When Loaded Into Recordset

[Hansgen]

What I am trying to do:
I have a header table and a detail table. Both tables have a boolean column. I want to update the boolean in the header to be false if all of the matching detail records are false, or true if any of the detail records are true. I have tried many approaches with similar results. What is included here is just one of them.

The problem in a nutshell:
My VBA code opens a DAO recordset based on an SQL (see below). If I run the SQL in a query window, it returns the correct results. However, the same query loads all records into the recordset, not just the matching ones.

The code:
This bit of code just includes a debug statment where there would be a query to update the header table. The SQL returned by the first debug statement will return the correct records if run in a query window. The second debug statement prints a row for each record in the table rather than just those returned by the query as I would expect. The boolean column in the header table is named "qualify". The query finds associate records where the sum of the values of qualify = 0, or false.

I would appreciate any help understanding why this is behaving this way, or suggestions for a better way to do this.

Thank you in advance.

------------------------------------------------------
'Select associate IDs from the detail table where all
'booleans are false.
sRollexc = "select tbltempreport.assoc_id from tbltempreport where rpt_id = " & Chr(34) & sRptID & Chr(34) & " " & _
"group by tbltempreport.assoc_id having sum(qualify) = 0"

Debug.Print sRollexc

Set rstRollexc = db.OpenRecordset(sRollexc)

With rstRollexc
If .RecordCount Then
.MoveFirst
Do Until .EOF
'Print the contents of the recordset.
Debug.Print .Fields("assoc_id")
.MoveNext
Loop
End If
End With

------------------------------------------------------

 

[Golom]

Grouping by "tbltempreport.assoc_id" doesn't guarantee you one record ... it guarabtees you one record for each "tbltempreport.assoc_id". If there are multiple different values of "tbltempreport.assoc_id" that have the same "rep_id" value then you will see a record for each of them.

It appears that "rep_id" is the matching field between the header and detail tables so try retrieving and grouping by that field.

sRollexc = "Select [red]rpt_id[/red] " & _
           "From tbltempreport " & _
           "Where rpt_id = '" & sRptID & "' " & _
           "Group By [red]rpt_id[/red] " & _
           "Having SUM(qualify) = 0"

 

[Hansgen]

Thanks Golom. The query is intended to return one record for each assoc_id and not a single record.

As it turns out, I found the issue shortly before I received your reponse, although I had been working on it for a couple of days. I was getting different records when running the SQL in the query window versus through VBA, not because of some quirk of Access/VBA, but because of the timing of events in the code; the records were being affected by other code later in the program. I need to change the order of execution and my problem should be solved. So in short, it was operator (me) error.

Thanks again.