Need to make query run faster...any ideas?

[bhipwell via AccessMonster.com]

Hello,

This is for a Employee Benefit Database.

I have a query referencing 17 tables. 2 of the tables just provide general
information, employer data and class data. The other 15 tables contain class
information for each type of available employee benefit (health, dental, life,
etc.).

The 15 tables contain a field (HealthInfoID, DentalInfoID, etc.) which
references other tables with specific plan information. Since each employee
benefit can have multiple plan options, those indexes create every
combination resulting in 49,152 rows.

Each of the 15 tables has a field called ...Offered. All I need to know is
whether a benefit for a particular class (equal to a class on a specified
form) is offered ("Yes").

Any thoughts? I can't seem to find any way not to have the query compute
every combination.

Design issue?
Code to run faster?
What about "chaining" queries?

Thanks in advance! Here is the SQL:

SELECT tblEmployer.CoCompanyID, tblClass.CoClassName, tblEmployer.CoName,
tblEmployer.CoLogo, tblEmployer.CoStreet, tblEmployer.CoCity, tblEmployer.
CoState, tblEmployer.CoZip, tblEmployer.CoPhone, tblClassHEAL.HEALOffered,
tblClassDEN1.DEN1Offered, tblClassVISI.VISIOffered, tblClassLIFE.LIFEOffered,
tblClassDLIF.DLIFOffered, tblClassLIFX.LxOffered, tblClassLIFS.LsOffered,
tblClassLIFC.LcOffered, tblClassSTD.STDOffered, tblClassLTD.LTDOffered,
tblClassVSTD.VSTDOffered, tblClassVLTD.VLTDOffered, tblClassHSA.
HsClassOffered, tblEmployer.CoPOPOffered, tblClassFSA.FSAClassOffered,
tblClassSUPP.SUPPClassOffered
FROM (((((((((((((tblClass INNER JOIN ((tblEmployer INNER JOIN tblClassHEAL
ON tblEmployer.CoCompanyID = tblClassHEAL.CoCompanyID) INNER JOIN
tblClassDEN1 ON tblEmployer.CoCompanyID = tblClassDEN1.CoCompanyID) ON
(tblEmployer.CoCompanyID = tblClass.CoCompanyID) AND (tblClass.ClClassID =
tblClassDEN1.ClClassID) AND (tblClass.ClClassID = tblClassHEAL.ClClassID))
INNER JOIN tblClassVISI ON (tblEmployer.CoCompanyID = tblClassVISI.
CoCompanyID) AND (tblClass.ClClassID = tblClassVISI.ClClassID)) INNER JOIN
tblClassDLIF ON (tblEmployer.CoCompanyID = tblClassDLIF.CoCompanyID) AND
(tblClass.ClClassID = tblClassDLIF.ClClassID)) INNER JOIN tblClassLIFE ON
(tblEmployer.CoCompanyID = tblClassLIFE.CoCompanyID) AND (tblClass.ClClassID
= tblClassLIFE.ClClassID)) INNER JOIN tblClassLIFX ON (tblEmployer.
CoCompanyID = tblClassLIFX.CoCompanyID) AND (tblClass.ClClassID =
tblClassLIFX.ClClassID)) INNER JOIN tblClassLIFS ON (tblClass.ClClassID =
tblClassLIFS.ClClassID) AND (tblEmployer.CoCompanyID = tblClassLIFS.
CoCompanyID)) INNER JOIN tblClassLIFC ON (tblEmployer.CoCompanyID =
tblClassLIFC.CoCompanyID) AND (tblClass.ClClassID = tblClassLIFC.ClClassID))
INNER JOIN tblClassSTD ON (tblEmployer.CoCompanyID = tblClassSTD.CoCompanyID)
AND (tblClass.ClClassID = tblClassSTD.ClClassID)) INNER JOIN tblClassLTD ON
(tblEmployer.CoCompanyID = tblClassLTD.CoCompanyID) AND (tblClass.ClClassID =
tblClassLTD.ClClassID)) INNER JOIN tblClassVSTD ON (tblEmployer.CoCompanyID =
tblClassVSTD.CoCompanyID) AND (tblClass.ClClassID = tblClassVSTD.ClClassID))
INNER JOIN tblClassVLTD ON (tblEmployer.CoCompanyID = tblClassVLTD.
CoCompanyID) AND (tblClass.ClClassID = tblClassVLTD.ClClassID)) INNER JOIN
tblClassHSA ON (tblEmployer.CoCompanyID = tblClassHSA.CoCompanyID) AND
(tblClass.ClClassID = tblClassHSA.ClClassID)) INNER JOIN tblClassFSA ON
(tblEmployer.CoCompanyID = tblClassFSA.CoCompanyID) AND (tblClass.ClClassID =
tblClassFSA.ClClassID)) INNER JOIN tblClassSUPP ON (tblEmployer.CoCompanyID =
tblClassSUPP.CoCompanyID) AND (tblClass.ClClassID = tblClassSUPP.ClClassID)
WHERE (((tblEmployer.CoCompanyID)=[Forms]![FrmEmployerMain]![CoCompanyID])
AND ((tblClass.CoClassName)=[Forms]![FrmEnrollmentForms]![ClassForReports]));

 

[Brendan Reynolds]

Do all the join fields participate in relationships? If you so, Access will
have created indexes on the foreign key fields when the relationship was
defined. If not, ensure that indexes exist on all of the fields used in
joins. Also ensure that indexes exist on the fields in the WHERE clause ...
tblEmployer.CoCompanyID and tblClass.CoClassName.
--
Brendan Reynolds

Hello,

This is for a Employee Benefit Database.

I have a query referencing 17 tables. 2 of the tables just provide
general
information, employer data and class data. The other 15 tables contain
class
information for each type of available employee benefit (health, dental,
life,
etc.).

The 15 tables contain a field (HealthInfoID, DentalInfoID, etc.) which
references other tables with specific plan information. Since each
employee
benefit can have multiple plan options, those indexes create every
combination resulting in 49,152 rows.

Each of the 15 tables has a field called ...Offered. All I need to know
is
whether a benefit for a particular class (equal to a class on a specified
form) is offered ("Yes").

Any thoughts? I can't seem to find any way not to have the query compute
every combination.

Design issue?
Code to run faster?
What about "chaining" queries?

Thanks in advance! Here is the SQL:

SELECT tblEmployer.CoCompanyID, tblClass.CoClassName, tblEmployer.CoName,
tblEmployer.CoLogo, tblEmployer.CoStreet, tblEmployer.CoCity, tblEmployer.
CoState, tblEmployer.CoZip, tblEmployer.CoPhone, tblClassHEAL.HEALOffered,
tblClassDEN1.DEN1Offered, tblClassVISI.VISIOffered,
tblClassLIFE.LIFEOffered,
tblClassDLIF.DLIFOffered, tblClassLIFX.LxOffered, tblClassLIFS.LsOffered,
tblClassLIFC.LcOffered, tblClassSTD.STDOffered, tblClassLTD.LTDOffered,
tblClassVSTD.VSTDOffered, tblClassVLTD.VLTDOffered, tblClassHSA.
HsClassOffered, tblEmployer.CoPOPOffered, tblClassFSA.FSAClassOffered,
tblClassSUPP.SUPPClassOffered
FROM (((((((((((((tblClass INNER JOIN ((tblEmployer INNER JOIN
tblClassHEAL
ON tblEmployer.CoCompanyID = tblClassHEAL.CoCompanyID) INNER JOIN
tblClassDEN1 ON tblEmployer.CoCompanyID = tblClassDEN1.CoCompanyID) ON
(tblEmployer.CoCompanyID = tblClass.CoCompanyID) AND (tblClass.ClClassID =
tblClassDEN1.ClClassID) AND (tblClass.ClClassID = tblClassHEAL.ClClassID))
INNER JOIN tblClassVISI ON (tblEmployer.CoCompanyID = tblClassVISI.
CoCompanyID) AND (tblClass.ClClassID = tblClassVISI.ClClassID)) INNER JOIN
tblClassDLIF ON (tblEmployer.CoCompanyID = tblClassDLIF.CoCompanyID) AND
(tblClass.ClClassID = tblClassDLIF.ClClassID)) INNER JOIN tblClassLIFE ON
(tblEmployer.CoCompanyID = tblClassLIFE.CoCompanyID) AND
(tblClass.ClClassID
= tblClassLIFE.ClClassID)) INNER JOIN tblClassLIFX ON (tblEmployer.
CoCompanyID = tblClassLIFX.CoCompanyID) AND (tblClass.ClClassID =
tblClassLIFX.ClClassID)) INNER JOIN tblClassLIFS ON (tblClass.ClClassID =
tblClassLIFS.ClClassID) AND (tblEmployer.CoCompanyID = tblClassLIFS.
CoCompanyID)) INNER JOIN tblClassLIFC ON (tblEmployer.CoCompanyID =
tblClassLIFC.CoCompanyID) AND (tblClass.ClClassID =
tblClassLIFC.ClClassID))
INNER JOIN tblClassSTD ON (tblEmployer.CoCompanyID =
tblClassSTD.CoCompanyID)
AND (tblClass.ClClassID = tblClassSTD.ClClassID)) INNER JOIN tblClassLTD
ON
(tblEmployer.CoCompanyID = tblClassLTD.CoCompanyID) AND
(tblClass.ClClassID =
tblClassLTD.ClClassID)) INNER JOIN tblClassVSTD ON
(tblEmployer.CoCompanyID =
tblClassVSTD.CoCompanyID) AND (tblClass.ClClassID =
tblClassVSTD.ClClassID))
INNER JOIN tblClassVLTD ON (tblEmployer.CoCompanyID = tblClassVLTD.
CoCompanyID) AND (tblClass.ClClassID = tblClassVLTD.ClClassID)) INNER JOIN
tblClassHSA ON (tblEmployer.CoCompanyID = tblClassHSA.CoCompanyID) AND
(tblClass.ClClassID = tblClassHSA.ClClassID)) INNER JOIN tblClassFSA ON
(tblEmployer.CoCompanyID = tblClassFSA.CoCompanyID) AND
(tblClass.ClClassID =
tblClassFSA.ClClassID)) INNER JOIN tblClassSUPP ON
(tblEmployer.CoCompanyID =
tblClassSUPP.CoCompanyID) AND (tblClass.ClClassID =
tblClassSUPP.ClClassID)
WHERE (((tblEmployer.CoCompanyID)=[Forms]![FrmEmployerMain]![CoCompanyID])
AND
((tblClass.CoClassName)=[Forms]![FrmEnrollmentForms]![ClassForReports]));

 

[Bob Barrows [MVP]]

Hello,

This is for a Employee Benefit Database.

I have a query referencing 17 tables. 2 of the tables just provide
general information, employer data and class data. The other 15
tables contain class information for each type of available employee
benefit (health, dental, life, etc.).

The 15 tables contain a field (HealthInfoID, DentalInfoID, etc.) which
references other tables with specific plan information. Since each
employee benefit can have multiple plan options, those indexes create
every combination resulting in 49,152 rows.

Each of the 15 tables has a field called ...Offered. All I need to
know is whether a benefit for a particular class (equal to a class on
a specified form) is offered ("Yes").

WHERE
(((tblEmployer.CoCompanyID)=[Forms]![FrmEmployerMain]![CoCompanyID])
AND
((tblClass.CoClassName)=[Forms]![FrmEnrollmentForms]![ClassForReports]));

Then why don't you have a filter to only retrieve the records where
Offered=True?

 

[bhipwell via AccessMonster.com]

Thanks for the reply Bob,

Then why don't you have a filter to only retrieve the records where
Offered=True?

There could be three health plans, all of which offered would = "Yes". Same
for dental, life, etc. What is happening is the query is giving me every
possible combination of plans available.

For example, if there are 3 health plans and 3 dental plans, I get 6 rows of
data. Add 2 life plans, now I have 12 rows of data, and so on and so forth
through all 15 types of products.

And each plan (the 3 health, 3 dental, 2 life, etc.) could all be offered to
the class on the form. I need the query to not come up with every possible
combination.

Thanks again!

 

[bhipwell via AccessMonster.com]

Thanks Brenden,

Do all the join fields participate in relationships?
Also ensure that indexes exist on the fields in the WHERE clause ...

All the indices are correct and join fields are in good order.

What is happening is there may be 3 health plans available to the class on
the form. And, there may be 3 dental plans available to the class as well.
This results in 6 rows of data on the query.

Add another line of coverage with 2 plans, the rows go to 12. Add another
line with 3 lines of coverage, the rows of data grow to 36. So on and so on
through 15 tables.

The query is coming up with every possible combination of plans available. I
don't need this information, but can't figure out a way around it. The
answer I need is whether Health (or dental, life, etc) is offered to the
particular group, regardless if there are 3 health plans or not.

Thoughts?

Thanks again. BH

 

[John W. Vinson]

What is happening is there may be 3 health plans available to the class on
the form. And, there may be 3 dental plans available to the class as well.
This results in 6 rows of data on the query.

Add another line of coverage with 2 plans, the rows go to 12. Add another
line with 3 lines of coverage, the rows of data grow to 36. So on and so on
through 15 tables.

The query is coming up with every possible combination of plans available. I
don't need this information, but can't figure out a way around it. The
answer I need is whether Health (or dental, life, etc) is offered to the
particular group, regardless if there are 3 health plans or not.

That's precisely what a Query is designed to do. It's just not what you need
in this case!

I'd suggest a Form with separate subforms - one for health, one for dental,
one for life, etc.; use a Report with Subreports for printing.

 

[bhipwell via AccessMonster.com]

Hi John,

Thanks for the reply. Finally found my solution and it has to do with
normalizing my tables. I am rebuilding the database now to normalize and
missed a step. Basically, I need a separate table indicating whether or not
a type of product (health, dental, etc) is needed, not by specific plan name
(health plan A, health plan B, etc.)

Thanks again. Although the forum doesn't always give you answer, it does
give you the means to walk the right path to the answer.

Thanks again (for this reply and many others you have posted in the past)

B