slow query doesn't return all records

[Daniel]

Hi,

I've been writing a query which is subsequently used as a source in an
update query (so it has to be updateable).

When I run the (slow, ~10 minute) query, I get the wrong number of
records returned (e.g. 1687 instead of 1688). After determining which
record(s) was missing, I added another statement to the WHERE clause
to select that record (e.g. 'AND EL.ID = 7657') and lo-and-behold, the
record shows up as the (only) result. It seems inconsistent between
one run and the next (or possibly between one compile of the query and
the next) as to which record(s) are skipped.

Has anyone experienced something like this before? It's the second
time in the last week which I've had this happen (on similar queries),
and it's somewhat disturbing.

Here's my query without the additional clause:

SELECT EL.ID, EL.rating, EL.voltage, EL.equip_no
FROM qryElecEQItems AS EL
LEFT JOIN qryPossibleVoltages AS qPV ON (EL.metricPower <=
qPV.rating AND nz(EL.ctrllerType, 1) = qPV.mtrTypeID)
WHERE isnull(EL.motor_tag) = false
AND EL.voltage_overridden = false
AND EL.metricPower > 0
AND EL.elecManualAddn = false
AND qPV.voltage = (SELECT min(q1.voltage) FROM qryPossibleVoltages
AS q1 WHERE q1.mtrTypeID = nz(EL.ctrllerType, 1) AND q1.rating >=
EL.metricPower)
AND qPV.rating = (SELECT min(q1.rating) FROM qryPossibleVoltages AS
q1 WHERE q1.mtrTypeID = nz(EL.ctrllerType, 1) AND q1.rating >=
EL.metricPower)

and the clause I add to return only the missing record:
AND EL.ID = 7657

Thank you for any insight,
Daniel

 

[Guest]

You have queries built on queries with a few subqueries also built on queries
thown in. No wonder it's slow and possibly confused by the missing record.

Is there any way to gather up the data in one query going directly to the
tables with proper joins? Then you might be able to do some indexing plus
find out why the one record isn't showing up.

Other than that, you may have a corrupt database, but I really don't think
so. Also it could just be a case of the tables not being properly normalized
causing inconsistant results and complicated queries to extract the data.

 

[Daniel]

I'm using split fe/be mdb setup.

I can accept that it's slow - that makes sense. What I am more
concerned about is the inconsistent and incorrect results that are
returned. There is no visible sign that it is returning bad data -
which is a big problem! Do you think it's because I have several
layers of queries? At which point does Access 'become' unreliable -
is a clue that a query takes e.g. longer than 5 minutes or longer than
1 minute to run...? Or is it more like '3 layers of queries might
cause Access (or JET) to fail silently'?

I have tried to use proper normalization techniques, but the design is
complicated and I'm sure that some of it is not optimum - but that
shouldn't in itself be a source of inconsistent results from the SAME
query, should it?

Thank you,
Daniel