Corrupt Table?

[rjr]

I have a very simple select query that returns many fields, but only one is given a criteria. It is represented in SQL as follows.

SELECT Main.PropType, Main.CompNo, [Main]![Section] & [Main]![Township] & [Main]![Range] AS Expr3, Main.Grantor, Main.Grantee, [Main]![Month of Transaction] & "/" & [Main]![Year of Transaction] AS Expr4, Format([Price],"$#,##0&quot AS Expr2, Format([Main]![Price]/[Main]![Number of Units],"$#,##0.00&quot AS Expr1, Main.Financing, Main.Legal, Main.[OR BK], Main.[OR PG], Main.[Improvements Description], Main.[Site Description], Main.Density, Main.Verification, Format([PGI],"$#,##0&quot AS Expr10, Format([V&C],"$#,##0&quot AS Expr15, Format([EGI],"$#,##0&quot AS Expr16, Format([Expenses],"$#,##0&quot AS Expr17, Format([NOI],"$#,##0&quot AS Expr18, " " AS Expr14, Format([Main]![NOI]/[Main]![Price],"#.00%&quot AS Expr11, [Main]![Price]/[Main]![EGI] AS Expr12, Main.EDR, Format([Main]![Expenses]/[Main]![EGI],"#%&quot AS Expr13, Main.Comments, Format([Main]![PGI]/[Main]![Number of Units],"$#,##0.00&quot AS Expr5, Format([Main]![V&C]/[Main]![Number of Units],"$#,###.00&quot AS Expr6, Format([Main]![EGI]/[Main]![Number of Units],"$#,##0.00&quot AS Expr7, Format([Main]![Expenses]/[Main]![Number of Units],"$#,###.00&quot AS Expr8, Format([Main]![NOI]/[Main]![Number of Units],"$#,##0.00&quot AS Expr9, Main.Location, Main.Name, Main.[Price Cont], Main.[Date Cont], Main.[OR BK/PG Cont], Main.[Type of Units], Main.[Price/Unit Cont]
FROM Main
WHERE (((Main.PropType)=15));

PropType can be any one of dozens of Number, Long Interger values. For some reason 15 comes up with an empty result, while others, 8, 12, 10 come up with the expected result - those records in which the criteria value of the query is the same as the Proptype field of the record.

I created a new query on the same table (Comps_be.mdb) with only the PropType field in the query and got the same result. For some reason the Criteria value 30 returns all the records with the value of 46, and no others. What gives? I Compacted and Repaired, and got the same results.

Is this indicative of a corrupt table? If so, absent a backup what is the best way to try to save the data in a new table, and leave the corruption behind?

Thanks in advance

 

[thornmastr]

There are two generally accepted ways to clean a corrupted table. The first, and the one that consistently fails for me, is to simply copy the table to a new table name. Delete the old table, and then rename the new table to what it is supposed to be. There are people who swear by that method, and I swear at those people constantly. But it is something you should try. If it works for you, use that method.

What works for me is to create a new database and copy the corrupted table into the new database. Then go back to the original database and delete the bad table. Then import the table from the new database. That seems to almost always work for me. When it does not I know I have a serious corruption issue and there are other methods for those situations; but try the above methods. One of them will probably work for you.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com