Verrrrrrry slow Query due to dcount

N

NeoFax

I have a query that uses a Dcount to calculate whether a field has a
duplicate value. This allows me to divide a corresponding field by
the count. (Long story because our engineering department is hard
headed) However, this causes the query to take approximately 15
minutes to run. I would like to use a query within the query that
takes the info and runs a query to count the duplicates and then with
this info run the normal select query. Here is the SQL for my query
currently:

SELECT qryStationJC_Summary.[HELO#], qryStationJC_Summary.STAGE,
qryStationJC_Summary.OP, qryStationJC_Summary.[JOB CARD NUMBER] AS JC,
qryStationJC_Summary.DESCRIPTION AS JCName, qryStationJC_Summary.STAT,
qryZPP_MECAPPMergeJCLevel.SAP_Hours AS TotalSAP,
qryStationJC_Summary.SumOfHOURS AS TotalAsg, qryStationJC_Summary.
[SumOfHRS WKD] AS TotalMECAPPErnd, tblStatusOrder.SortOrder AS Status,
tblStageBuilder.STATION, tblStageBuilder.Description,
tblStageBuilder.SortOrder, tblStageBuilder.StgPN, DCount
("[OP]","qrySAPvsMECAPPHrsJCLvl","[Helo#]=" & [qryStationJC_Summary]!
[Helo#] & " and [OP]=" & [qryStationJC_Summary]![OP] & " and [STAGE]
='" & [qryStationJC_Summary]![STAGE] & "'") AS OpCount
FROM ((qryStationJC_Summary LEFT JOIN qryZPP_MECAPPMergeJCLevel ON
(qryStationJC_Summary.[JOB CARD NUMBER] = qryZPP_MECAPPMergeJCLevel.
[JOB CARD NUMBER]) AND (qryStationJC_Summary.OP =
qryZPP_MECAPPMergeJCLevel.OP) AND (qryStationJC_Summary.STAGE =
qryZPP_MECAPPMergeJCLevel.[DAY/]) AND (qryStationJC_Summary.[HELO#] =
qryZPP_MECAPPMergeJCLevel.[HELO#])) INNER JOIN tblStageBuilder ON
qryStationJC_Summary.STAGE = tblStageBuilder.STAGE) INNER JOIN
tblStatusOrder ON qryStationJC_Summary.STAT =
tblStatusOrder.StatusCode
ORDER BY qryStationJC_Summary.[HELO#], qryStationJC_Summary.STAGE,
qryStationJC_Summary.OP;
 
M

Michel Walsh

Try to make a query to replace your DCount:

DCount
("[OP]","qrySAPvsMECAPPHrsJCLvl","[Helo#]=" & [qryStationJC_Summary]!
[Helo#] & " and [OP]=" & [qryStationJC_Summary]![OP] & " and [STAGE]
='" & [qryStationJC_Summary]![STAGE] & "'") AS OpCount


become


newQuery.opCount



with newQuery the saved query:


SELECT COUNT(op) AS opCount, [hello#], op, stage
FROM qrySAPvsMECAPPHrsJCLvl
GROUP BY [hello#], op, stage



and also add that newQuery as table into your main query, adding joins to
hello#, op and stage fields, as required.




Vanderghast, Access MVP
 
N

NeoFax

Try to make a query to replace your DCount:

DCount
("[OP]","qrySAPvsMECAPPHrsJCLvl","[Helo#]=" & [qryStationJC_Summary]!
[Helo#] & " and [OP]=" & [qryStationJC_Summary]![OP] & " and [STAGE]
='" & [qryStationJC_Summary]![STAGE] & "'") AS OpCount

become

newQuery.opCount

with newQuery the saved query:

SELECT COUNT(op) AS opCount, [hello#], op, stage
FROM qrySAPvsMECAPPHrsJCLvl
GROUP BY [hello#], op, stage

and also add that newQuery as table into your main query, adding joins to
hello#, op and stage fields, as required.

Vanderghast, Access MVP




I have a query that uses a Dcount to calculate whether a field has a
duplicate value.  This allows me to divide a corresponding field by
the count. (Long story because our engineering department is hard
headed)  However, this causes the query to take approximately 15
minutes to run.  I would like to use a query within the query that
takes the info and runs a query to count the duplicates and then with
this info run the normal select query.  Here is the SQL for my query
currently:
Click to expand...
SELECT qryStationJC_Summary.[HELO#], qryStationJC_Summary.STAGE,
qryStationJC_Summary.OP, qryStationJC_Summary.[JOB CARD NUMBER] AS JC,
qryStationJC_Summary.DESCRIPTION AS JCName, qryStationJC_Summary.STAT,
qryZPP_MECAPPMergeJCLevel.SAP_Hours AS TotalSAP,
qryStationJC_Summary.SumOfHOURS AS TotalAsg, qryStationJC_Summary.
[SumOfHRS WKD] AS TotalMECAPPErnd, tblStatusOrder.SortOrder AS Status,
tblStageBuilder.STATION, tblStageBuilder.Description,
tblStageBuilder.SortOrder, tblStageBuilder.StgPN, DCount
("[OP]","qrySAPvsMECAPPHrsJCLvl","[Helo#]=" & [qryStationJC_Summary]!
[Helo#] & " and [OP]=" & [qryStationJC_Summary]![OP] & " and [STAGE]
='" & [qryStationJC_Summary]![STAGE] & "'") AS OpCount
FROM ((qryStationJC_Summary LEFT JOIN qryZPP_MECAPPMergeJCLevel ON
(qryStationJC_Summary.[JOB CARD NUMBER] = qryZPP_MECAPPMergeJCLevel.
[JOB CARD NUMBER]) AND (qryStationJC_Summary.OP =
qryZPP_MECAPPMergeJCLevel.OP) AND (qryStationJC_Summary.STAGE =
qryZPP_MECAPPMergeJCLevel.[DAY/]) AND (qryStationJC_Summary.[HELO#] =
qryZPP_MECAPPMergeJCLevel.[HELO#])) INNER JOIN tblStageBuilder ON
qryStationJC_Summary.STAGE = tblStageBuilder.STAGE) INNER JOIN
tblStatusOrder ON qryStationJC_Summary.STAT =
tblStatusOrder.StatusCode
ORDER BY qryStationJC_Summary.[HELO#], qryStationJC_Summary.STAGE,
qryStationJC_Summary.OP;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks! I guess, I always choose the hard way of doing things ;^) It
worked perfectly. Only had to change the qry it was based on as I was
getting a circular reference.
 
Top