Query Freezes The Access

[Ilya]

Hi everyone:

The following text in quotes is the query copied from the
Access Query Disigner. It includes one linked table
_crewcurs and two other non-linked tables. In my
developmennt PC with Access 2000, Widows 2000 it works OK.
In my client PC with Access 2000 (9.0.44402 SR-1) Windows
2000 it freezes the Access data base withot any error.

"SELECT [_crewcurs].cltno, [_crewcurs].category,
tblCATEGORYLIST.CATNAME AS CATEGORYNAME, fna.corrname,
tblCATEGORYLIST.CATSORT, Sum([_crewcurs].end_value) AS
[Value], [_crewcurs].totendval
FROM fna INNER JOIN (_crewcurs INNER JOIN tblCATEGORYLIST
ON [_crewcurs].category = tblCATEGORYLIST.CATEGORY) ON
fna.cltno = [_crewcurs].cltno
GROUP BY [_crewcurs].cltno, [_crewcurs].category,
tblCATEGORYLIST.CATNAME, fna.corrname,
tblCATEGORYLIST.CATSORT, [_crewcurs].totendval
HAVING ((([_crewcurs].cltno)=Trim([Forms]![CUSTOM REPORTS]!
[TxtCltno])));"

What may be the problem, how to find the problem?.
Thanks in advance for help.
Ilya

 

[Ken Snell]

Just a guess, but based on my own experience with totals queries that have
lots of records, the PC where it "freezes" may not have the "oomph" to run
the query quickly enough (memory, processor speed, etc.).

Try changing the HAVING clause to a WHERE clause so that the filtering is
done before the grouping of the records. This should greatly decrease the
time needed to run the query and eliminate the "freezing" problem that
you're seeing:

SELECT [_crewcurs].cltno, [_crewcurs].category,
tblCATEGORYLIST.CATNAME AS CATEGORYNAME, fna.corrname,
tblCATEGORYLIST.CATSORT, Sum([_crewcurs].end_value) AS
[Value], [_crewcurs].totendval
FROM fna INNER JOIN (_crewcurs INNER JOIN tblCATEGORYLIST
ON [_crewcurs].category = tblCATEGORYLIST.CATEGORY) ON
fna.cltno = [_crewcurs].cltno
WHERE [_crewcurs].cltno=Trim([Forms]![CUSTOM REPORTS]![TxtCltno])
GROUP BY [_crewcurs].cltno, [_crewcurs].category,
tblCATEGORYLIST.CATNAME, fna.corrname,
tblCATEGORYLIST.CATSORT, [_crewcurs].totendval;