Wrong Number of Rows returned by Subquery with Top Clause (1 Viewer)

[Martin Beney]

I have a strange 'bug'.

I wrote a query to return the TOP 32 items for a key. Works fine:-

SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=1 GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;


I then plugged this into a query in the WHERE clause using In (Select Top .....; [changing the =1 condition to pick up the correct id from the emcompassing query]:-

SELECT [my Disks Coefficients].[my Disk ID], [my Disks Coefficients].[thier Disk ID], [my Disks Coefficients].coefficient FROM [my Disks Coefficients] WHERE [my Disks Coefficients].[thier Disk ID] In (SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=[my Disks Coefficients].[my Disk ID] GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;

This runs fine but the the number of items returned is for each key is less than if I run the Top query by itself for each key seperately? So when I run it stand-alone for ID 1 I get 127 rows (there are many equal coefficients); when I run it as a sub-query I get only 121 rows for ID 1!

Anyone any ideas?

Martin

 

[neileg]

I don't understand your use of aliases, but I have no idea if this has anything to do with the problem.

 

[Martin Beney]

Hi Neil,
The aliases are needed to get the ID from the encompassing query into the subquery's criteria, otherwise you can't reference the outer query from withing the subquery - does that make sense?
Martin

 

[neileg]

This bit doesn't seem right:
[my Disks Coefficients sub].[my Disk ID]

The alias is valid for the SQL statement and doesn't need the table identifier.

I think if I was doing this, I would have joined the two queries rather than using the WHERE....IN subquery.

 

[Martin Beney]

Hi Neil,
Can't see how I could join the two queries.
What I have is a whole lot too much data for each set of [My Disk ID] & [Thier Disk ID]. The query is trying to get the lowest 32 coeffiecients with their associated [Thier Disk ID] for each [My Disk ID].

It therefore calculates the set of [Their Disk ID]'s with the lowest coefficients and then only keeps those items (sort of like doing a Top 32 on each [my Disk ID] set.

Hope I'm getting the idea over.

Cheers, Martin

 

[neileg]

You're getting your idea over, but I'm failing to get mine over! My excuse is that its my first day back at work from being sick. Brain obviously not up to scratch.

Any chance of posting a stripped out version of you db so I can tinker?

 

[Martin Beney]

Neil,
Will try and strip down a copy of the db...... A day or two maybe....
Martin

 

[Martin Beney]

Neil,
Have created a stripped down db but still over 1MB so can't upload.

But more interestingly may have found the problem (bug I think). To set up the mini DB I imported the main table (which I had addd a lot more rows to) and then deleted all except [my Disk ID]<=10. Fine so far. At this point I ran a test. For ID 1, a standalone Top 16 gave 21 rows. In the main query it gave 20 rows. In a summary of the main query, just counting the rows it gave 21. Very odd.....

Then I compressed the DB and tried it again. Everything worked fine!

This looks like a real bug in M/S Access. Any thoughts where to go now! Compressing the DB is no great shakes but it does mess up any serious automation.

All the best
Martin

 

[neileg]

Glad it's working. You can set the db to compact on close, so fully automated.