Ranking query running EXTREMELY slow

Hello,

I have a ranking query which is based off of two other
queries. Inside I rank employee hours by overall then by
staff level. Everything works except for the fact that it
is really slow, even sorting the query takes a few
minutes... Does anybody have any suggestions? Here is one
of the lines of code:
StfLvlHoursrank: (Select Count(*) from
qry_Employee_AllHours_Sums where [TotalHours]
[AllHoursrank].[TotalHours] and
[StaffLevel]=[AllHoursrank].[StaffLevel])+1

I'm not sure if I should consildate queries, but I have had
more dependent queries than this before.

Any help would be greatly appreciated

Hi,

That results in a SELECT query in the SELECT clause... and the "sub-query"
is itself based on a ... query, qry_Employee_...

I would try to move that into a JOIN, I mean, bring the qry_Employee_... in
the upper part, give it a decent alias to avoid all that ugly typing, add
the criteria through WHERE criteria,ie, allhoursrank.TotalHours with, as
criteria, nicealias.TotalHours, also nicealias.StaffLevel with the
criteria =AllHoursrank.StaffLevel (or make that an inner join), THEN, make a
GROUP with all the required fields, and add COUNT(*) in the main SELECT
clause.:

------------------------
SELECT f1, f2, f3, ..., COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel

WHERE a.TotalHours q.TotalHours

GROUP BY f1, f2, f3, ...
---------------------------


or even faster:

-----------------------------
SELECT a.pk, LAST(a.f1), LAST(a.f2), LAST(a.f3), ..., COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel
AND a.TotalHours q.TotalHours

GROUP BY a.pk
------------------------------


where pk is the primary key of table AllHoursRank. Can use FIRST, MIN, or
MAX, instead of LAST, in this particular case, since all the records of
table aliased "a", of a given group would be, at most, for a given field,
the same duplicated values.




Hoping it may help,
Vanderghast, Access MVP

"Sean G." wrote in message
...
Hello,

I have a ranking query which is based off of two other
queries. Inside I rank employee hours by overall then by
staff level. Everything works except for the fact that it
is really slow, even sorting the query takes a few
minutes... Does anybody have any suggestions? Here is one
of the lines of code:
StfLvlHoursrank: (Select Count(*) from
qry_Employee_AllHours_Sums where [TotalHours]
[AllHoursrank].[TotalHours] and
[StaffLevel]=[AllHoursrank].[StaffLevel])+1

I'm not sure if I should consildate queries, but I have had
more dependent queries than this before.

Any help would be greatly appreciated

Thank you very much for your response, I will try that!


-----Original Message-----
Hi,

That results in a SELECT query in the SELECT clause... and
the "sub-query"
is itself based on a ... query, qry_Employee_...

I would try to move that into a JOIN, I mean, bring the
qry_Employee_... in
the upper part, give it a decent alias to avoid all that
ugly typing, add
the criteria through WHERE criteria,ie,
allhoursrank.TotalHours with, as
criteria, nicealias.TotalHours, also
nicealias.StaffLevel with the
criteria =AllHoursrank.StaffLevel (or make that an inner
join), THEN, make a
GROUP with all the required fields, and add COUNT(*) in
the main SELECT
clause.:

------------------------
SELECT f1, f2, f3, ..., COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel

WHERE a.TotalHours q.TotalHours

GROUP BY f1, f2, f3, ...
---------------------------


or even faster:

-----------------------------
SELECT a.pk, LAST(a.f1), LAST(a.f2), LAST(a.f3), ...,
COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel
AND a.TotalHours q.TotalHours

GROUP BY a.pk
------------------------------


where pk is the primary key of table AllHoursRank. Can use
FIRST, MIN, or
MAX, instead of LAST, in this particular case, since all
the records of
table aliased "a", of a given group would be, at most, for
a given field,
the same duplicated values.




Hoping it may help,
Vanderghast, Access MVP

"Sean G." wrote in
message
...
Hello,

I have a ranking query which is based off of two other
queries. Inside I rank employee hours by overall then by
staff level. Everything works except for the fact that it
is really slow, even sorting the query takes a few
minutes... Does anybody have any suggestions? Here is one
of the lines of code:
StfLvlHoursrank: (Select Count(*) from
qry_Employee_AllHours_Sums where [TotalHours]
[AllHoursrank].[TotalHours] and
[StaffLevel]=[AllHoursrank].[StaffLevel])+1

I'm not sure if I should consildate queries, but I have had
more dependent queries than this before.

Any help would be greatly appreciated


.