Huge Query 1

[aw23]

I have a huge database called People with names and contact information. Then I have another one called Calls with every call to these people recorded. I have the the id from people and then the date of the call etc.
I need to do a query which will select all people that have never been called or that haven't been called in the last 90 days. There is no indication of calls in the poeple database.
This is what I have so far and it is obviously not good because it is just huge...

'need to get all people ID's
rs.Open "select people_id,people_name, country_id from people", getConnection(), adOpenStatic
'loop through all id's and see if there is a call for that person in the callstable
While Not rs.EOF
callRS.Open "select * from calls where people_id=" & rs("people_id"), getConnection(), adOpenStatic
'if rs is empty then add it to the list
If (callRS.BOF) And (callRS.RecordCount = 0) Then
'get the country
countryRS.Open "select nc from c where c= " & rs("country_id"), getConnection(), adOpenStatic
ListRes.AddItem (rs("people_id") & ";" & rs("people_name") & ";" & rs("country_id") & ";" & countryRS("nc"))
countryRS.Close
Set countryRS = Nothing
End If
callRS.Close
Set callRS= Nothing
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

 

[aw23]

I need to add another criteria to the query. I need to add
AND people_status='Active' I tried adding it in several places in the query and I got errors. Where can I add it?

Thanks

 

[Genimuse]

If people_status is in your people table then I would think changing the WHERE part to:

 & " WHERE c.Country_ID Is Not Null [COLOR=blue]AND c.people_status = 'Active'" _

should work. You have to tell the query what table your field is supposed to be in sometimes, even when it's inambiguous it seems.

 

[aw23]

Thanks so much. That seems to have worked.

 

[Genimuse]

Heh, forgot to close my color tag. Glad you figured out what I meant.

 & " WHERE c.Country_ID Is Not Null [COLOR=blue]AND c.people_status = 'Active'[/color]" _

 

[aw23]

Yeah I got it! Thanks