A query is 23 lines, click sort and then only 11 lines are shown?

G

Gueritol

I have a short query with a parameter.
When I run it, Access returns 23 lines, if I press the sort button, then it
only shows 11?.
If in the query I specify the sort it still returns 23 lines, and again if I
sort it gives me 11?

BTW 11 records is the correct result, and 23 is some misteriously dupped
records (well misterious to me so far)
 
J

Jerry Whittle

Please post the SQL and a small sample of the data.

One thought: Are you sure that you are sorting and not actually filtering by
selection?
 
G

Gueritol

This is the query result when I run it
Detail_Node_ID Level Detail_Node_Code Detail_Node_Description Master_Flow_ID_A Master_Flow_ID_B
21 2 Tr10 Transit 10 3 3
36 2 Co04 Conduit 04 3 3
36 2 Co04 Conduit 04 3 3
36 2 Co04 Conduit 04 3 3
43 2 Tr15 Transit 15 3 3
45 2 Co08 Conduit 08 3 3
45 2 Co08 Conduit 08 3 3
52 2 Co11 Conduit 11 3 3
52 2 Co11 Conduit 11 3 3
53 2 Co12 Conduit 12 3 3
53 2 Co12 Conduit 12 3 3

After I press sort (100% sure :D) on any column it immediately displays this:

Detail_Node_ID Level Detail_Node_Code Detail_Node_Description Master_Flow_ID_A Master_Flow_ID_B
36 2 Co04 Conduit 04 3 3
45 2 Co08 Conduit 08 3 3
52 2 Co11 Conduit 11 3 3
53 2 Co12 Conduit 12 3 3
21 2 Tr10 Transit 10 3 3
43 2 Tr15 Transit 15 3 3

I'm totally lost?!?!
 
J

John Spencer

I remember seeing this in one of my queries once upon a time.

I never did figure out the problem, but if I recall correctly I was able to
get consistent (and correct) returns by adding DistinctRow to the Select
clause. (Of course, DistinctRow probably forces some type of internal
sorting to occur to make sure you get distinct rows)
 
M

Marshall Barton

Gueritol said:
I have a short query with a parameter.
When I run it, Access returns 23 lines, if I press the sort button, then it
only shows 11?.
If in the query I specify the sort it still returns 23 lines, and again if I
sort it gives me 11?

BTW 11 records is the correct result, and 23 is some misteriously dupped
records (well misterious to me so far)
Click to expand...


Take a look at http://allenbrowne.com/ser-47.html
 
G

Gueritol

I did the same ... but OK ... that is a band-aid, not a cure ...

Thanks for the help
 
G

Gueritol

This work great ...

There were two tables involved, one linked to a lot of tables, and the other
to just 3 ...

I started (of course) with the 3 link table, and presto! it was Ok.

Thanks.
 
D

david epsom dot com dot au

Yes, clicking sort does that. It is the same as grouping your
results, or using the Distinct Row/Distinct Value options.

Yes, it is not same as specifying the sort order in the query:
it is the same specifying Distinct as well as specifying the
sort order.

It is not terribly mysterious, but it is unexpected for most
people,

1) guru developers don't see this behaviour because well
designed databases don't usually have mysterious duplicates

And of course

2) beginning developers don't usually see it because by
the time they notice it they probably are guru developers


:~)

(david)
 
G

Gueritol

David,

This is not a desing flaw. It was providing good results up to a certain
moment.

It is an indexing-bug...somehow the index in one (or more) of the tables in
the DB got corrupted.

I followed the advise of Marshall and now is working ... tough work ... but
.... OK.

Basically unlink from linked tables, delete the indexes of a table, copy to
another table, delete the old table, relink with the old-links ... rinse and
repeat with all tables and voila! ... it's now OK.
 
Top