Query returns incorrect results

  • Thread starter Thread starter GuiherGeek
  • Start date Start date
G

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
 
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
 
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
 
Back
Top