wrong result order of a query included join

[Yonatan Maman]

Hi Im using access(xp) 2002-SP1. and I have a strange problem - wrong
result order of a query included join

when I execute a query on TABLE_A (TABLE_A contains 2 colums: "id"
INTEGER and "name" MEMO)
Query1:
"select id, name from TABLE_A ORDER BY name" . I get my results OK


when I extend the query and make a join with TABLE_B (conatins 2
coulmd "ref_to_tableA" INTEGER and "value" INTEGER)
Query2:

"select id, name from TABLE_A, TABLE_B where ref_to_tableA=id ORDER BY
name"

I get the same results but nor ordered!!! it looks like the data is
ordered in 'CHUNKS'. for example I get

id name
---- -----
1 d
4 d1
5 d2
3 a
2 aa
6 c
7 c1

TABLE_A has somthing about 35K records.
TABLE_B has somthing about 2M records.

when I get the bad results from Query2 and try to resort it by right
click on the 'name' column and choose sort, It doest work either.

another point (dont know if it is important) the 'name' columns is
unix-file-name so its values are /usr/bin/.....

any help will be appriciated.

10x

Yonatan

 

[Steve Schapel]

Yonatan,

Are the entries in the "name" field ever more than 255 characters in
length? If not, try changing it to a Text data type. If it needs to
be Memo, try a calculated field in the query which you would use for
sorting, e.g. Sorter: Mid([name],10,x)
(where x is however many characters you need to ensure a correct sort)

By the way, the word "name" has a special meaning in Access, so it is
not a good idea to use it as a fieldname.

- Steve Schapel, Microsoft Access MVP