Strange query behavior

[peteg12345]

I have found a couple of strange behaviors that I cannot explain.

I have a MS Access 2003 database that holds all my queries with the
tables linked to another MS Access 2003 database.

With some of my queries (relatively straightforward -- inner join, no
grouping) when I first run it I generally get an outlandish number of
rows returned (around 500). As soon as I click on one of the column
headers and sort it, I get the correct number of rows. I can't repeat
it EVERY time, but at least 50% of the time this is true.

Secondly, when I drag this stored query into a new query it invariably
shows me the incorrect number of rows, even after I try to sort is as
above.

What the heck is going on here?

TIA

PEte

 

[MGFoster]

I have found a couple of strange behaviors that I cannot explain.

I have a MS Access 2003 database that holds all my queries with the
tables linked to another MS Access 2003 database.

With some of my queries (relatively straightforward -- inner join, no
grouping) when I first run it I generally get an outlandish number of
rows returned (around 500). As soon as I click on one of the column
headers and sort it, I get the correct number of rows. I can't repeat
it EVERY time, but at least 50% of the time this is true.

Secondly, when I drag this stored query into a new query it invariably
shows me the incorrect number of rows, even after I try to sort is as
above.

What the heck is going on here?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably a bad index or corrupt DB. Some things to try:

1. Compact & Repair the db.
2. Recreate the query. Make a new QueryDef & copy the old SQL into
Notepad. Then re-copy from Notepad to the new QueryDef's SQL View (just
in case there are any weird [corrupted] characters in the SQL).
2. Transfer all data from the main table in the affected query to a new
table. Delete the old table and restore relationships to the new table.
Name the new table the old table's name.
3. *Drastic* - Move all the data into a new DB w/ new tables & the same
relationships as the old DB.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSab5RoechKqOuFEgEQLe9QCfaVecSeGLxzK/npPIcsXnN7JPraUAnRgZ
1ztsr1Yuo8b9eRlYsWWBTuon
=MTQO
-----END PGP SIGNATURE-----

 

[peteg12345]

Thanks for your suggestions. I tried all of them, but the behavior
continued.
Finally, I migrated the tables to MySQL, and re-created the queries as
well.
Lo and behold, it was cockpit error.
IOW, I screwed up the joins - there was a self-join involved. Now it's
fixed.
Thanks again,

Pete