I have a report that is based off of query #3. Query #3 consolidates the
data from Queries #1 & #2. Queries 1 & 2 each run in about 10 seconds, but
query 3 takes 8 minutes to run. Query #1 is as follows (#2 is similar):
SELECT Data.LogNo, Data.Clock, Data.YrsPos, Data.Date, Data.Reported,
Data.Recordable, Data.Start, Data.Time, Data.What, Data.Happen,
BodyLoc.BodyLocDesc, BodyPart.BodyPartDec, Caught.CaughtDesc,
CaughtBe.CaughtBeDesc, Class.ClassDesc, Cond.CondDesc,
ContactBy.ContactByDesc, ContactW.ContactWDesc, Exposure.ExposureDesc,
FallFoot.FallFootDesc, FallFrom.FallFromDesc, Nature.NatureDesc,
OverEx.OverExDesc, Practice.PracticeDesc, StruckAg.StruckAgDesc,
StruckBy.StruckByDesc, Data.SafetySys, BodyLoc_1.BodyLocDesc,
Practice_1.PracticeDesc, BodyPart_1.BodyPartDec, Cond_1.CondDesc,
Data.Complete
FROM (((((StruckBy INNER JOIN (StruckAg INNER JOIN (Practice INNER JOIN
(OverEx INNER JOIN (Nature INNER JOIN (FallFrom INNER JOIN (FallFoot INNER
JOIN (ContactW INNER JOIN (Cond INNER JOIN (BodyPart INNER JOIN (CaughtBe
INNER JOIN (Caught INNER JOIN (BodyLoc INNER JOIN (ContactBy INNER JOIN
(Class INNER JOIN Data ON Class.ClassID = Data.Class) ON
ContactBy.ContactByID = Data.ContactBy) ON BodyLoc.BodyLocID = Data.BodyLoc)
ON Caught.CaughtID = Data.Caught) ON CaughtBe.CaughtBeID = Data.CaughtBe) ON
BodyPart.BodyPartID = Data.BodyPart) ON Cond.CondID = Data.Cond) ON
ContactW.ContactWID = Data.ContactW) ON FallFoot.FallFootID = Data.FallFoot)
ON FallFrom.FallFromID = Data.FallFrom) ON Nature.NatureId = Data.Nature) ON
OverEx.OverExID = Data.OverEx) ON Practice.PracticeID = Data.Practice) ON
StruckAg.StruckAgID = Data.StruckAg) ON StruckBy.StruckByID = Data.StruckBy)
INNER JOIN Exposure ON Data.Exposure = Exposure.ExposureID) INNER JOIN Cond
AS Cond_1 ON Data.Cond2 = Cond_1.CondID) INNER JOIN Practice AS Practice_1 ON
Data.Practice2 = Practice_1.PracticeID) INNER JOIN BodyPart AS BodyPart_1 ON
Data.BodyPart2 = BodyPart_1.BodyPartID) INNER JOIN BodyLoc AS BodyLoc_1 ON
Data.BodyLoc2 = BodyLoc_1.BodyLocID
WHERE (((Trim([Data].[LogNo]))=Trim([forms]![frmDataEdit]![cboLogNo])))
Query #3:
SELECT qryEditPrint1.*, qryEditPrint2.*
FROM qryEditPrint1 INNER JOIN qryEditPrint2 ON qryEditPrint1.Clock =
qryEditPrint2.Clock;
The database is a Access 2K FE(mde)/BE setup on a network drive. I am using
this to print reports on injuries so all 50 fields are needed in the report.
Any advice on how to improve performance with Access? Thanks in advance.
Jeff Boyce - 12 May 2005 15:04 GMT
Mike
The first thing to check on slow queries is that fields used for selection
criteria and for joins are indexed in their underlying tables. If you've
used the Relationship window to set relationships between tables, you've
covered the indexing for the "join" fields.
I don't know your data structure, so I don't know if this is relevant...
Another approach might be to do your selection in smaller, initial queries,
build a "main" report on that (limited) information, and build subreport(s)
on the related data.

Signature
Good luck
Jeff Boyce
<Access MVP>
> I have a report that is based off of query #3. Query #3 consolidates the
> data from Queries #1 & #2. Queries 1 & 2 each run in about 10 seconds, but
[quoted text clipped - 37 lines]
> this to print reports on injuries so all 50 fields are needed in the report.
> Any advice on how to improve performance with Access? Thanks in advance.
Mike - 13 May 2005 13:30 GMT
Jeff,
Thanks for the advice, subreports did the trick. Can you tell me where to
get information on how access is either processing the queries or the
reports. I have several databases similar to this one either existing or in
development and it would be helpful to know how to troubleshoot these
problems. None of the documentation that I have seen gives good
troubleshooting advice on whether these types of problems are query or report
related, or whether I have managed to hit a limitation of Access. I would
never have thought to try subreports to fix the problem. Thanks again.
Mike
> Mike
>
[quoted text clipped - 61 lines]
> report.
> > Any advice on how to improve performance with Access? Thanks in advance.
Jeff Boyce - 14 May 2005 13:57 GMT
Mike
I don't recall anything specific, but Jeff Conrad has an extensive list of
resources which might contain what you're looking for. See:
http://www.ltcomputerdesigns.com/JCReferences.html#Books

Signature
Jeff Conrad
Access Junkie
Bend, Oregon
Good luck!
Jeff Boyce
<Access MVP>