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

 

[PHV]

A starting point:
SELECT P.people_id, people_name, country_id
FROM people P LEFT JOIN calls C ON P.people_id=C.people_id
WHERE C.people_id Is Null

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

 

[aw23]

Thanks that worked. I'm having the same problem thought that I had with mine. The rs is so large that the system crashes. When I run it, the program crashes and when I go through it with the debugger I see that it works so I think there are just too many. Is there anything I can do?

rs.Open "SELECT P.people_id, people_name, country_id
FROM people P LEFT JOIN calls C ON P.people_id=C.people_id
WHERE C.people_id Is Null", getConnection(), adOpenStatic
'now need to put all companies into listbox
While Not rs.EOF
'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
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

 

[aw23]

how do I add another criteria? I have a repID in the people table. I want to add the Rep table and where the repID = a given name

rs.Open "select people_id,people_name, country_id FROM Rep INNER JOIN people ON rep.rep_ID = people.repID WHERE (((rep.EFirst_Name)='" & srchUser & "'))", getConnection(), adOpenStatic

 

[aw23]

Maybe it would work if I would stop at like 1000? Is there a way to numerate the rs?
Loop through the rs until I get to the 1000th record

 

[aw23]

I did the following:
if rs.absoluteposition=500 then exit sub

and I got my first 500 records. Is there any other way to do it?

 

[PHV]

To avoid multiple Open of recordset you may consider something like this:
rs.Open "SELECT P.people_id, people_name, P.country_id, nc" _
& " FROM ((people P INNER JOIN c ON P.country_id=c.c)" _
& " INNER JOIN Rep R ON P.repID=R.rep_ID)" _
& " LEFT JOIN calls L ON P.people_id=L.people_id" _
& " WHERE R.EFirst_Name='" & srchUser & "'" _
& " AND L.people_id Is Null", getConnection(), adOpenStatic

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

 

[aw23]

Thanks, I just used 2 recordsets in the end, I think it makes the most sense. And I stopped the search after 500 records found.

 

[aw23]

I have another question. I have the following loop:

Do Until rs.EOF
If rs.AbsolutePosition = 500 Then Exit Sub
If IsNull(rs("country_id")) Then
rs.MoveNext
Exit Do
End If

'get the country
countryRS.Open "select nc from c where c= " & rs("country_id"), getConnection(), adOpenStatic
'add itm to listbox
ListRes.AddItem (rs("people_id") & ";" & rs("people_name") & ";" & rs("country_id") & ";" & countryRS("nc"))
countryRS.Close
Set countryRS = Nothing
rs.MoveNext
Loop

What I want to do is if the country is null, I want to move to the next record and continue the loop. How can I do this? If there is no country then I get an error, so I want it to just skip that one and move on. Now it will just exit the loop.

 

[aw23]

Forget it I got it. Sorry I wasn't thinking. I just added it to the query, and c.country_id is not null

 

[aw23]

Ahh! I think I am getting caught in my query. Can someone please look at this and tell me what the problem is? My error is "No value given for one or more required parameters"

Here is my code:

sql = "SELECT c.people_ID, c.people_Name, c.Country_ID, a.On_Date, c.people_Rep FROM people AS c LEFT JOIN Calls AS a ON c.people_ID = a.people_ID WHERE c.Country_ID Is Not Null And (a.people_ID Is Null Or DateDiff('d', [a].[On_Date], Date) > 90)"

What I am trying to do is get all records where there is no call or the last call was more than 90 days ago. By the way I put this in the query builder and it works. It also works without the 90 day addition.
Also, the next part of th code is:

sql = sql & " and c.people_rep=" & rs("employees_id")


THANKS!

 

[PHV]

And (a.people_ID Is Null Or a.On_Date < Date() - 90)

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

 

[aw23]

Thanks, that worked except that I have several duplicates in my rs. Do you know why it would do that?

(By the way I gave you a second star but I don't think it worked because I don't think you can give more than one to the same person on the same post!)

 

[aw23]

I also noticed that it's not working. It brings up all calls that were done before 90 days. I don't want that. I want a person that hasn't been called in the last 90 days. In other words the last call what > 90 days ago (or never)

 

[PHV]

sql = "SELECT c.people_ID, c.people_Name, c.Country_ID, Max(a.On_Date) As LastDate, c.people_Rep" _
& " FROM people AS c LEFT JOIN Calls AS a ON c.people_ID = a.people_ID" _
& " WHERE c.Country_ID Is Not Null And a.people_ID Is Null" _
& " GROUP BY c.people_ID, c.people_Name, c.Country_ID, c.people_Rep" _
& " HAVING Max(a.On_Date) < Date() - 90)"

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

 

[aw23]

Thanks, I tried that and now I got no results!

 

[PHV]

Sorry:
sql = "SELECT c.people_ID, c.people_Name, c.Country_ID, Max(a.On_Date) As LastDate, c.people_Rep" _
& " FROM people AS c LEFT JOIN Calls AS a ON c.people_ID = a.people_ID" _
& " WHERE c.Country_ID Is Not Null" _
& " GROUP BY c.people_ID, c.people_Name, c.Country_ID, c.people_Rep, a.people_ID" _
& " HAVING (a.people_ID Is Null Or Max(a.On_Date) < Date() - 90))"

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

 

[aw23]

Thanks!!!!
It looks like that really worked.

 

[aw23]

OK, now I'm tyring to alphabatize it. So I added ORDER BY c.people_Name and I got the wrong results. SoI took it out and I added it to my last statement which I add on later -
sql = sql & " and c.people_rep=" & rs("employees_id") & " ORDER BY c.people_Name"

It looks like I am getting the right rs but it's not alphabatized. Any idea why?
Thanks

 

[aw23]

I'm sorry it does work.