Strange Query Behavior

[Vel]

I have a query that is exhibiting odd behavior. Specifically, I can view the
query in design mode or datasheet mode, and it takes just a second or two to
run. Further, other queries which include this query run fine. However,
whenever I try to do anything else with the query, or another query which
includes it, I have problems. For instance, reports or forms bound to the
query hang and won't open for viewing or printing. I tried to change the
query to a Make Table query as a workaround, but it won't actually make the
table, it just freezes until I interrupt via Ctrl+Break.

 

[Klatuu]

Post the SQL of the query, please.

 

[Duane Hookom]

When you state "it takes just a second or two to run" are you going to the
last record in the query?

There may be something in the query like a calculation or expression that
causes an issue.

 

[Vel]

SELECT tblScheduler.ClientID, tblScheduler.DateOf, tblClientIns.HasDed,
qryProcExpected.Cost, tblScheduler.Notes, tblClientIns.DedMet,
tblScheduler.ProviderID, tblScheduler.ProcID, tblClientIns.InsuranceOrder,
tblScheduler.ActivityID AS ActType, tblClientIns.CoverageID,
tblClientIns.InsuranceID, tblScheduler.TimeIn, qryProcExpected.Expected,
qryProcCovered.percentage, qryProcCovered.Dollar
FROM qryProcExpected INNER JOIN ((tblClientIns INNER JOIN tblScheduler ON
tblClientIns.clientID = tblScheduler.ClientID) INNER JOIN qryProcCovered ON
(tblClientIns.CoverageID = qryProcCovered.CoverageID) AND
(tblScheduler.ProcID = qryProcCovered.ProcID)) ON (qryProcExpected.ProcID =
tblScheduler.ProcID) AND (qryProcExpected.InsuranceID =
tblClientIns.insuranceID)
WHERE (((tblScheduler.DateOf) Between [forms]![frmMain]![pg2]![txtstart] And
[forms]![frmmain]![pg2]![txtend]));


NOTE: I realize the design of this query is odd (with multiple joins), but
it is used to determine the expected copay amount for our clients depending
upon their insurance agency (which modifies the base price), the procedure
being billed (which determines the base price), and their insurance coverage
type (which determines the percentage or dollar amount they pay based on
other factors).

Also, the reports were working fine up until yesterday evening. I've tried
rebuilding the query and all underlying queries from scratch to see if that
fixed the issue, but it did not.

 

[Vel]

Yes. I can open it up in datasheet view and immediately scroll (or jump) to
the last record. Generally we only view a single day at a time, in which
case we have fewer than 300 records returned. That's why I'm so stumped.
Its a bizarre issue, very much unlike anything I've run across before.

 

[Klatuu]

If everything was working and then it suddenly stopped working, there is a
good chance you have some corruption going on. I don't see anything in the
SQL that indicates a problem.
Is your database split?
If so, does each user have his own copy of the front end on his computer?
How many users?
Have you don't a compact and repair?

 

[Vel]

The database is split. There are generally 3-7 independent front-end users,
each with their own copy of the front-end. I did a compact and repair on the
back-end yesterday morning before anyone logged on. I regularly compact and
repair the front ends and redistribute. I also recently rebuilt a
relationship from the client table to the schedule and activity table that
had inexplicably disappeared. There were no conflicts when I built the
relationships, and they had been in place previously, so I'm not sure how
they were deleted.

 

[Klatuu]

If you had a relationship suddenly disappear, that is even more indication
that some corruption has taken place.

Make backups of your files, and do a decompile to see if that will
straighten it out.

 

[Vel]

I've never decompiled before, but I followed some instructions I found that
said to run Access with the /decompile flag then just open a module and
recompile. However, my backend has no modules to compile, so maybe I'm
misunderstanding. I did open both the front and backend with the /decompile
flag, but I'm still having the same issue.

Currently, my relationships are all on the backend. Should they be on my
front end?

If you had a relationship suddenly disappear, that is even more indication
that some corruption has taken place.

Make backups of your files, and do a decompile to see if that will
straighten it out.

 

[Klatuu]

relationships should be in the back end.
Have you deleted the query and written it from scratch?

 

[Vel]

Yes, but I haven't yet deleted and rebuilt each of the underlying queries.
I suppose that will be my next step. I suppose my problem could be in one of
the underlying queries, though they don't seem to be exhibiting the same
bizarre behavior if I build test reports based on them.

In case it helps clear anything up:

SQL qryProcCovered
SELECT qryProcCoverage.ProcID, qryProcCoverage.CoverageID,
tblCovered.Percentage, tblCovered.Dollar
FROM qryProcCoverage LEFT JOIN tblCovered ON (qryProcCoverage.ProcID =
tblCovered.ProcID) AND (qryProcCoverage.CoverageID = tblCovered.CoverageID);


SQL qryProcExpected
SELECT qryProcInsurance.InsuranceID, qryProcInsurance.ProcID,
qryProcInsurance.Units, qryProcInsurance.Cost, qryProcInsurance.ProcCode,
qryProcInsurance.Entered, qryProcInsurance.MinsReq, qryProcInsurance.Type,
qryProcInsurance.Variable, qryProcInsurance.ScheDesc,
qryProcInsurance.DVRDesc, qryProcInsurance.DVRCode,
qryProcInsurance.TestingDesc, tblExpected.Expected
FROM tblExpected RIGHT JOIN qryProcInsurance ON (tblExpected.ProcID =
qryProcInsurance.ProcID) AND (tblExpected.InsuranceID =
qryProcInsurance.InsuranceID);

SQL qryProcInsurance (basis for qryProcExpected)
SELECT tblProcedure.ProcID, tblInsurance.InsuranceID, tblProcedure.Units,
tblProcedure.Cost, tblProcedure.ProcCode, tblProcedure.Entered,
tblProcedure.MinsReq, tblProcedure.Type, tblProcedure.Variable,
tblProcedure.ScheDesc, tblProcedure.DVRDesc, tblProcedure.DVRCode,
tblProcedure.TestingDesc
FROM tblProcedure, tblInsurance;

SQL qryProcCoverage (basis for qryProcCovered)
SELECT tblProcedure.ProcID, tblCoverage.CoverageID
FROM tblProcedure, tblCoverage;

 

[Vel]

Rebuilding the underlying queries fixed the problem. Huzzah!

Thanks for all your help.

 

[Vel]

Scratch that. I had altered the report that caused me to become aware of the
issue temporarily to refer to a table with manually copy/pasted records. I
forgot to change it back before I tested.

 

[Vel]

Not sure if this means anything, since I don't understand what's going on,
but if I include the totals when in query view, it bogs the query down just
like when I change the query type from a select query or when I try to use
the query as the basis for a form or report.