Query returns incorrect results

[GuiherGeek]

I have an extremely complex web of queries that is returning incorrect results.

Query A relies on a left outer join of two other queries to produce a
dataset, and it provides the desired result. Query B attempts to summarize
Query A using GROUP BY on some of the non-null fields, but for reasons I
cannot explain, its dropping some records. The dropped records **tend** to
be ones with null values from the first query, but not **all** of them get
dropped. I've triple-checked by null handling, but haven't been able to fix
it. As a final test, I wrapped Query A in a make table query and saved the
output, then executed Query B against the table - Voila!! All of the records
are handled correctly! What's even more strange is that when Query B was
originally written, it produced the correct results, but at some point it
seems to have "broken" even though it wasn't changed. I've tried recreating
it by copying the SQL from a backup of the DB, but no dice.

For a variety of reasons (mainly additional complexity and DB growth) I
don't want to save and throw away the temp table each time I run the query
set. Any ideas how to fix it?

Rob

 

[Jeff Boyce]

Rob

I've seen this type of behavior when a query uses a parameter for a
selection criterion, but does not explicitly declare that parameter.

Any chance any of your queries use selection criterion parameters?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GuiherGeek]

No parameters, but as a side note, the original queries in the stack use
linked tables which point to another Access DB. I've spent a ton of time
digging through KB articles, but can't find anything similar