wrong order when sorting join - query result

[Yonatan Maman]

Hi Im using access 2000. and I have a strange problem.

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

[Peter Russell]

Yonatan Maman previously wrote:

Hi Im using access 2000. and I have a strange problem.

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
:-)

As I understand it, Jet SQL does not support sorting on memo fields. So
the real question is why it worked at all.

Looks like you'll have to find/create a different sort field.

Regards

Peter Russell

[Yonatan Maman]

pr******@russellscott.co.uk (Peter Russell) wrote in message news:<me**********************@russellscott.btinte rnet.com>...

Yonatan Maman previously wrote:

Hi Im using access 2000. and I have a strange problem.

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
:-)

As I understand it, Jet SQL does not support sorting on memo fields. So
the real question is why it worked at all.

Looks like you'll have to find/create a different sort field.

Regards

Peter Russell

10x for the answer (I didnt know that) - but any wy I need to store
String values (longer than 255 chars) - what are my options?

Yonatan

[Peter Russell]

Yonatan Maman previously wrote:

pr******@russellscott.co.uk (Peter Russell) wrote in message
news:<me**********************@russellscott.btinte rnet.com>...

Yonatan Maman previously wrote:

Hi Im using access 2000. and I have a strange problem.

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
:-)

As I understand it, Jet SQL does not support sorting on memo fields.
So the real question is why it worked at all.

Looks like you'll have to find/create a different sort field.

Regards

Peter Russell

10x for the answer (I didnt know that) - but any wy I need to store
String values (longer than 255 chars) - what are my options?

Yonatan

You can store whatever you want, obviously.
But are you saying you really need to SORT on more than 255 characters?

I would just create an extra field in my query containing the first 'n'
characters from the memo field, and sort on that.

Regards

Peter Russell

[Yonatan Maman]

pr******@russellscott.co.uk (Peter Russell) wrote in message news:<me**********************@russellscott.btinte rnet.com>...

Yonatan Maman previously wrote:

pr******@russellscott.co.uk (Peter Russell) wrote in message
news:<me**********************@russellscott.btinte rnet.com>...

Yonatan Maman previously wrote:

> Hi Im using access 2000. and I have a strange problem.
>
> 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
> :-)
>

As I understand it, Jet SQL does not support sorting on memo fields.
So the real question is why it worked at all.

Looks like you'll have to find/create a different sort field.

Regards

Peter Russell

10x for the answer (I didnt know that) - but any wy I need to store
String values (longer than 255 chars) - what are my options?

Yonatan

You can store whatever you want, obviously.
But are you saying you really need to SORT on more than 255 characters?

I would just create an extra field in my query containing the first 'n'
characters from the memo field, and sort on that.

Regards

Peter Russell

10x, but I still cant understand why it works without the join?
I also check that if I remove some of the records ( both tables with
1K records) it work OK even with join - how come ???
Yonatan