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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|