A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

access compact database changes results



 
 
Thread Tools Display Modes
  #1  
Old March 23rd, 2007, 06:43 AM posted to microsoft.public.access.queries
Martin Beney
external usenet poster
 
Posts: 1
Default access compact database changes results

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
  #2  
Old March 23rd, 2007, 07:36 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default access compact database changes results

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.

--
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" 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


  #3  
Old March 23rd, 2007, 07:58 AM posted to microsoft.public.access.queries
Martin Beney[_2_]
external usenet poster
 
Posts: 3
Default access compact database changes results

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.

--
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" 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



  #4  
Old March 23rd, 2007, 09:15 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default access compact database changes results

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


  #5  
Old March 23rd, 2007, 09:29 AM posted to microsoft.public.access.queries
Martin Beney[_2_]
external usenet poster
 
Posts: 3
Default access compact database changes results

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



  #6  
Old March 23rd, 2007, 01:53 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default access compact database changes results

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



  #7  
Old March 23rd, 2007, 06:42 PM posted to microsoft.public.access.queries
Martin Beney[_2_]
external usenet poster
 
Posts: 3
Default access compact database changes results

Hi Guys,
Think I have solved the problem. Its was me miss checking the data!

On double checking the test database I had set up the problem seems to have
'disappeared'. The problem was down to the order of the returned records (I
had specified any) and for some reason the last few rows contained the
'missing' data totally out of sequence with the rest of the data that
appeared to be sorted.

Sorry!

And thanks,
Martin

"Jerry Whittle" wrote:

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:59 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.