sort table on criteria

[sleepy head]

Hi

I have the following table I would like to sort:

tbl_example_unsorted
r2 w2 s3 s4 s5
r2 b2 b2 m2 s2
r1 w1 b1 m1 s1

to:

tbl_example_sorted
r1 w1 b1 m1 s1
r2 b2 b2 m2 s2
r2 w2 s3 s4 s5

The sort is based on the critera that rows that HAVE different field
value types (e.g., w1/b1 are different but s3/s4 are same type) and so
are listed first.This is what i have tried to do using the advanced
filter option. The results I get is that those rows that DO NOT HAVE
different field value types are not listed at all and so I get:

tbl_example_incomplete
r1 w1 b1 m1 s1

Any help would be appreciated.

cheers
sleepy head

 

[Steve Schapel]

Sleepy Head,

Well, Filtering and Sorting are two very different sorts of processes.
You are talking about the order in which the records are presented, so a
Filter is not applicable.

Now, it is not really clear what you are expecting. In the example you
gave, if you Sort the data based on the first column, so you have:
r1
r2
r2
.... then this already determines to some extent the order seen in the
other columns. The r1 row *must* be the first row displayed, so the
first row will be:
r1 w1 b1 m1 s1
.... because the w1-b1-m1-s1 values form part of the same record as the
r1. If you sort Ascending on the first column, it is *impossible* to
have any other result than this.

Then, because there are 2 rows both wth r2, then the next column you
apply a sort to will determine the order of these 2 rows, but as
mentioned above, this can not affect the first row. So if you do the
secondary sort on the second column, you will get exactly what your
example showed, since b2 comes before w2.

After that, sorting on any of the other columns will make no difference
whatsoever, as the sorting on the first 2 columns has completely
determined the display of the whole data set. Whether columns have
"different field value types" doesn't come into the picture at all.

So, if I am missing the poit here somewhere, can you maybe give another
example of where you are not getting the expected result.

 

[newsnet customer]

Steve Schapel,

I didn't mean to say filtering. I meant to say sort, which is what the post
is about.

I do not want to sort individual columns. I want to sort the table such
that, "rows that HAVE different field value types (e.g., w1/b1 are different
but s3/s4 are same type) are listed first.This is what i have tried to do
using the advanced sort option. The results I get is that those rows that DO
NOT HAVE different field value types are not listed at all and so I get:"

tbl_example_incomplete
r1 w1 b1 m1 s1

but i want this:

tbl_example_sorted
r1 w1 b1 m1 s1
r2 b2 b2 m2 s2
r2 w2 s3 s4 s5

from what I can see, you can only sort "ascending and decending", which is
not what I want. So I left that option blank and put instead a criteria. And
obviously the criteria works like a filter, so I'm stuck. Am i being clear?
Im really trying to explain this as best I can.

Any help appreciated from anyone in the know.

Cheers
SH

 

[Guest]

It sounds like you need to add columns based on an IIF statement like --
Sort2: IIF(Left([C2],1)=Left([C3],1),0,1)
Where C2 is the second column field and C3 the third. Your output grid
would look like --
C1 Sort2 Sort3 Sort4 C2 C3 C4 C5

 

[BruceM]

You could not sort columns even if you wanted to, except maybe with some
sort of elaborate code or forumula.

The meaning of "rows that have different field value types" is unclear.
What is a "field value type"? I can only guess that "w" is a prefix that
means some category of something or other, while "b" is a prefix for a
different category.

If you want to sort the records you would do well to make a query. Your
options will be limited to ascending and descending, but there is often a
way to come up with a way to contrive a custom sort order.

Since you have not identified your fields I will call them Field1 through
Field5. It seems that you have sorted by Field1 (r1, r2), but it is unclear
what you expect after that. As things stand in your example, sorting by
Field1, then by Field3, 4, or 5 will yield the results you show.