By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

wrong order when sorting join - query result

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.