I would be very interested in seeing the SQL statement for this query. Please
post it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Martin Beney" wrote:

Hi Allen,
The table isn't dirty because it will happen even if the query is run
directly after openning the DB!
Martin

"Allen Browne" wrote:

No, that doesn't sound right.

The only reason a subquery/query should produce *fewer* results than
specified in the TOP clause would be if fewer records are available. (In
Access, it can produce more records than it should if there is a tie.)

Is there any chance that the last record(s) you expect in the subquery have
not yet been saved? For example, if the form is still open and the record is
still dirty?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Martin Beney" wrote in message
...
Hi Allen,
Thanks for ideas.
The DB is damaged as far as I can tell because i can recreate the problem
reliably (compact, add more records, run query - get wrong results etc.).
The Top 32 clause does indeed return variable numbers of records as it
returns the top 31 plus all equal items to number 32. This isn't a
problem. I
can run the subquery as a standalone query on a single item set and ALWAYS
get the same number of records consitently.
Its only, before a compact, that the main outer query returns the wrong
number of records, also consistently and repeatably. It appears (???) that
the last 'few' items in the Top 32 list are dropped when used inside a
subquery?

All very odd.
Martin

"Allen Browne" wrote:

There are a couple of possilitities here, Martin.

It is possible that you have a damaged index. The compact/repair repairs
the
index, so afterwards the results are correct. If this is the case, it
should
be a one-off, i.e. not repeatable after you add/edit/delete records.

The other possibility is that your query is not unambiguous. For example,
if
the ORDER BY clause in the subquery is not adequate to define the sorting
completely, the TOP 32 may vary depending on other factors. If this is
the
case, the solution is to improve the SQL statement.

"Martin Beney" Martin wrote in message
news I have a database that I have added records to. If I run a query that
uses
a
subquery (where xxx in (select top 32..... that uses a top clause I
get
different results before versus after compacting the database.

Before the compact the results are wrong with some rows missing. After
the
compact they are correct.

Anyone any ideas?
Martin