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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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?
 
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?!?!
 
The SQL:

PARAMETERS [Level] Short;
SELECT qry_00_Level_Select_A.Detail_Node_ID, qry_00_Level_Select_A.Level,
qry_00_Level_Select_A.Detail_Node_Code,
qry_00_Level_Select_A.Detail_Node_Description,
qry_00_Level_Select_A.Master_Flow_ID_A, Node_B.Master_Flow_ID AS
Master_Flow_ID_B
FROM qry_00_Level_Select_A LEFT JOIN qry_00_Flow AS Node_B ON
qry_00_Level_Select_A.Detail_Node_ID = Node_B.Node_B_ID;
 
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)
 
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
 
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.
 
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)
 
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.
 
Back
Top