Multiple field Table Sort

[John W. Vinson]

Yikes, my bad -- the 'Acct No' was sorting correctly after all!

Since I am using transferred data, I just had entered the data differently
for each city at different times by, for example, placing a period after the
state in some instances which made the same city appear higher up.

That's a relief...

and a good example of the advantages of normalization. If you had a table of
States and a table of Cities with referential integrity enforced you would
have consistant values for the state and the city.

It appears that your table structure has lots of repeating fields (EA1 through
EA15, TOT1 through TOT15, etc.). Ideally there should be several one to many
relationships here... and if TOT means "total" these fields should probably be
calculated on the fly rather than stored. Is this table intentionally
denormalized? Or have you considered normalizing it?

John W. Vinson [MVP]

 

[Bob Quintal]

Bob,

If you select the column in the query design grid, you can use the
little bar at the top of the column to drag it to another position
within the grid. Either that, or delete the existing column, and
re-insert in the position you want it. I don't think there is any
reason to have the same field represented in two columns.

I know how to move a column. One can also drag a column in datasheet
view, and save the query so that the column order is retained.

The original poster wanted a multi-column sort, with the layout of
columns differing from the sort sequence.

In order to do this, once can code the SQL as

SELECT foo, bar, baz from SomeTable
ORDER BY baz,foo, bar

In the designer, one adds the columns:
foo, bar, baz, foo, bar

then sets to ascending on the last three columns, and unchecks the
show: checkbox on the two rightmost columns.

Result: SQL equal to that created above from the designer.

 

[Tom Ventouris]

Now that you say it, no.

It's just a carry over from my early days when I did not realise that the
order of fields in the query will also affect the order in which they are
sorted.
Some habits just don't die.
JayC - please strike the last remark.

Just wondering, Tom, do you see an advantage of this compared with just
sorting in your query on both the separate fields?

--
Steve Schapel, Microsoft Access MVP

Base your form on a query.
Create an expression in the query: Somename:[City]&[Name}
Sort by this expression.