Query problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My db has 81,000 records, when i break my queries up, they work. BUt if i use
a subquery even if i'm as restrictive as possible in the subquery, it never
finishes. The progressbar reaches 100%, but it never displays the recordset.
I actually thought it was a simple query. Is there anything i can do?

Thanks!
 
Do you have an index on QuestionID? At a minimum you need an index on it,
but you should also have an index on Response.

Using Access, it is often faster to break the process into two queries.
QueryA:
SELECT QuestionID, Response
FROM TheTable
GROUP BY QuestionID, Response
HAVING Count(Response) = 10

Using that saved query
SELECT QuestionID, RuleID
FROM TheTable INNER JOIN QueryA
ON TheTable.QuestionID = QueryA.QuestionID
 
I don't think it is cartesian. So are you saying this shouldn't have happen?

select distinct patientno , lastname, firstname, sex, dob, race, loc, docid,
age, dos, count(patientno) as Visits
from charts
where Patientno in
(Select patientno
from charts
where DOS >= 2005-10-01 and age >=40)
group by patientno, lastname, firstname, sex, dob, race, loc, docid, age,
dos
having count(patientno)>=3
 
Back
Top