Bizarre and Inconsistent Error in an Aggregate Query (1 Viewer)

[jumnhy]

Hello all,

Wanted to share something that I'm running into in the hopes that one of our experts has run into it before.

I had seen inconsistent result sets out of a query I've been tweaking today. Sometimes 3640 results, sometimes 247520 results. Those are precise numbers, and I can't figure out what is causing the difference. Same query.

Copied the query via a Copy/Paste option in the Object Navigator. Run the copy. 3640 results. Run the original, 247520 results.

Some screenshots for clarity:
Copy, and results:

Original, and results:
(Cropped out the Employee Names that are the first column)


Copy, and results:

(Cropped out first column)


I'm really at a loss for why this is happening.

I don't really expect anyone to be able to troubleshoot my specific problem, but if anyone has seen a similar situation in their years of DBA work, I'd love to see some light shed on this $%&*# as it's becoming frustrating.


Editing to add:

I just noticed that the qry1 filter (that has the enormous resultset) has a "Filter" toggle that's not grayed out as it is on the copy. There's no active filter, though when I click the toggle, I get a prompt:

I should note that the table that these are being pulled from does in fact have a lookup field on "Employee" that is pulling in the FK from an Employees table elsewhere.

I'm still lost but will keep picking at threads--I hope someone can chime in here.


Edit 2:

I think I have a fix. In the Properties pane for qry1, there was a "filter" present that was filtering ON the lookup in the table.
This did not copy (or perhaps I inadvertently created it by clicking one of the filter dropdowns in the UI while working after making the copy?) into the duplicate query.

My (ongoing) confusion centers on the fact that aside from it trying to filter on an expression that's a lookup... Why would this happen? I would expect to cause it to run slowly, but not to end up with some weird matrix arithmetic that blows up the result set in this way.

I'm also eager to understand this so that I don't inadvertently do it again. Even with this "filter" toggled off, I never meant for any filter that I applied to be this persistent. I was able to just delete the filter from the Properties entry and now its gone, so that's good, at least...

 

[plog]

Why isn't the 2nd column of the queries called 'Process"?

Also, it sure seems like there are duplicates in both queries on Employee/Process--of course we can't actually see the Employee value to verify that. Are you sure both sets of data are from the query?

 

[jumnhy]

Hi Plog,

Good question. I would prefer the second column of the query display as process, but for whatever reason, the caption from tblTrainingRecords is used instead.

Unfortunately, I can't recreate the bug for further inspection at this point-- after I deleted the filter from the properties tag, replacing the filter doesn't bring the problem back (which is both a good thing and a scary thing, so...)


However, I can say conclusively that there are no duplicates in the smaller recordset (Copy of qry1). The difference in size of the recordset is an even multiple (247520/3640 = 68) but I'm not sure what there are 68 of that would cause the duplication.

Sorry, this is an impossible question, and I don't expect much, I'm just hoping that someone has seen something akin to this before. If not, I'll close the thread in a few days.

 

[Minty]

68 Employees by any chance?
68 SOP's

Either way, I would remove the lookup field from the table, they really mess things up, and can obscure issues sometimes.

 

[jumnhy]

That was my initial thought too... but no, 160+ employees, 27 SOPs. Weirdness.

I'll talk to the "powers that be" with respect to the lookup. I've found it immensely annoying on a number of occasions, but I didn't build this database, so I'm not sure where it's plugged in that removing it will "cause problems". Definitely in favor of axing it though--giant PITA because it's unclear where Access uses the lookup and where it supplies the underlying value, especially for any sort of expressions. Yicky.