Multiple field Table Sort

[jayC]

I am using Access 2007 and am unable to find specific instructions on how
to sort a table by more than one field, I would like to, for example, sort
records by city and then within each city by name.

I know I can do it with a query or report but would like to be able to work
on the records in a specific order.

I have tried advanced filter/sort but it didn t seem to work.

Many Thanks.

 

[Steve Schapel]

Jay,

Using a query would be the way to handle it. It is generally a good
idea to regard Tables as being background data storage, and not for
human consumption, and use the other tools available, as applicable,
whenever you need to see or work with the data.

 

[jayC]

Jay,

Using a query would be the way to handle it. It is generally a good
idea to regard Tables as being background data storage, and not for
human consumption, and use the other tools available, as applicable,
whenever you need to see or work with the data.

I find it confusing you call it "not for human consumption" since the table
will have the actual records that I wish to modify via the attached form.

It is my understanding that a query is mostly used to look at records and
not to actually modify them --or is there a way to call up the records
using the query or some other method?

Thanks

 

[Steve Schapel]

Jay,

The principle avenue for working with data is via a form. The Record
Source for a form can be a table or a query (in the form of a saved
query object, or an SQL statement). This gives you a lot more power
that working directly in the tables themselves. For example, you can
control the appearance (formatting) of the data, you can work with data
from more than one related table at once, you can work with a subset of
the data, and... you can sort the data on multiple fields.

If you have a form based directly on a table, if you look at the
Properties of the form you will see that there is a property called
'Order By' which you can use to sort the form's data accordong to more
than one field. I prefer to control it in the form's Record Source.

 

[John W. Vinson]

It is my understanding that a query is mostly used to look at records and
not to actually modify them --or is there a way to call up the records
using the query or some other method?

Your understanding is incorrect, perhaps derived from experience with other
software where this is true.

It's perfectly routine to base a Form (an appropriate tool for user
interaction) on a sorted Query. As noted upthread, a Table should be
considered an unordered data repository; your original premise that "I need to
sort the table so the user can see it in order" is simply incorrect. For the
user to see the records in order, you would use a Query.


John W. Vinson [MVP]

 

[jayC]

How does one format the listing of the field names in properties of the
form?

Under 'Order by," I put: city,name .

The cities were in order but the names within the cities were random.

Thanks

 

[Steve Schapel]

I'm not really sure, Jay. I have never done it. As mentioned before, I
think there are better ways. Still, I would put the field names in []s,
and especially with name, which is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not really be used as the name of
a field or control.

So, try it like this:
[city], [name]

And then, I think you need to put code in there, using for example the
form's Load event, like this:
Me.OrderByOn = True

Let us know how that goes.

 

[Tom Ventouris]

Probably clumsy, but here's a method that works for me:

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

By the way, "Name" should not be used as a field name, it is a reserved
word.

 

[Steve Schapel]

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.

 

[John W. Vinson]

How does one format the listing of the field names in properties of the
form?

Under 'Order by," I put: city,name .

The cities were in order but the names within the cities were random.

Is the City a Lookup Field in your table design? If so, it's sorting by the
meaningless, numeric city ID. If it is a Lookup field, you may want to base
the form on a Query joining the main table to the city table; include the city
name from the Cities lookup table and sort by that.

You can sort the records on the form in two ways - one would be to put

[City];[Name]

in the form's OrderBy property, and - in code, you can't do it manually - set
the form's OrderByOn property to True.

Probably better, and certainly simpler, create a Query exposing all the fields
that you want to see and sort by. Put the sort fields left to right in the
query grid and put Ascending (or Descending as appropriate) on the Sort row of
the query grid. Base the form on this Query and it will respect the sort
order.

Sorting data in Reports is a bit different - include all the fields you want
to sort by in the query and use the Report's Sorting and Grouping dialog to
specify the sort order.

John W. Vinson [MVP]

 

[Bob Quintal]

I created a simple query, however the test sorts based on more
than field that I'm creating are not flying.

In the lower pane of the design view of the query, "Customer Name"
is a column to the left of "City," so apparently I will have to
rearrange the fields in the table so that "Customer Name" appears
to the right of "City", unless there is another way of doing it.

Yes, Virginia, there is a Santa Claus.

Remove the Ascending statement from the Sort: row under the customer
name field.
Add the customer name column a second time to the query grid, to the
right of your city column. Put the Ascending in this second copy of
the column. Uncheck the box under this copy from the Show: row.

However, when I attempted to sort it by "City", then "Account
Number" (after changing the sort row under their respective
columns to "ascending"), the data were still not appearing
correctly in query datasheet view, even though it is a field that
appears to the right of "City". The data sorted correctly under
"City" but was random under "Account Number."

Thanks.

Is account number a text column? Do you have a format applied to
this column in the table? was it sorting on City, Customer, the
naccount number?

 

[jayC]

I created a simple query, however the test sorts based on more than field
that I'm creating are not flying.

In the lower pane of the design view of the query, "Customer Name" is a
column to the left of "City," so apparently I will have to rearrange the
fields in the table so that "Customer Name" appears to the right of "City",
unless there is another way of doing it.

However, when I attempted to sort it by "City", then "Account Number" (after
changing the sort row under their respective columns to "ascending"), the
data were still not appearing correctly in query datasheet view, even though
it is a field that appears to the right of "City". The data sorted
correctly under "City" but was random under "Account Number."

Thanks.

 

[John W. Vinson]

I created a simple query, however the test sorts based on more than field
that I'm creating are not flying.

In the lower pane of the design view of the query, "Customer Name" is a
column to the left of "City," so apparently I will have to rearrange the
fields in the table so that "Customer Name" appears to the right of "City",
unless there is another way of doing it.

That is indeed the way to do it. Fields sort left to right. That has no effect
on how you display the data on a Form or Report though.

However, when I attempted to sort it by "City", then "Account Number" (after
changing the sort row under their respective columns to "ascending"), the
data were still not appearing correctly in query datasheet view, even though
it is a field that appears to the right of "City". The data sorted
correctly under "City" but was random under "Account Number."

Please use View... SQL to switch the view of the query into SQL view. Copy and
paste the SQL text to a message here. Something is not coming across in our
explanations and/or your description; the SQL *is* the real query (the grid is
just a tool to make it easier to build the query).

John W. Vinson [MVP]

 

[Steve Schapel]

Jay,

In the lower pane of the design view of the query, "Customer Name" is a
column to the left of "City," so apparently I will have to rearrange the
fields in the table so that "Customer Name" appears to the right of
"City", unless there is another way of doing it.

No, you don't need to touch the table. You can place the fields in the
query design drid in any7 order you like, regardless of their order in
the table.

However, when I attempted to sort it by "City", then "Account Number"
(after changing the sort row under their respective columns to
"ascending"), the data were still not appearing correctly in query
datasheet view, even though it is a field that appears to the right of
"City". The data sorted correctly under "City" but was random under
"Account Number."

Could it be that Account Number is a Text data type, and you are
expecting it to sort numerically?

 

[Steve Schapel]

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.

 

[jayC]

SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME],
[W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION,
[W208-02JAN].[ACCT NO], [W208-02JAN].[JOB LOCATION], [W208-02JAN].PRICE,
[W208-02JAN].[CUST NO], [W208-02JAN].QTY1, [W208-02JAN].DES1,
[W208-02JAN].EA1, [W208-02JAN].TOT1, [W208-02JAN].QTY2, [W208-02JAN].DES2,
[W208-02JAN].EA2, [W208-02JAN].TOT2, [W208-02JAN].QTY3, [W208-02JAN].DES3,
[W208-02JAN].EA3, [W208-02JAN].TOT3, [W208-02JAN].QTY4, [W208-02JAN].DES4,
[W208-02JAN].EA4, [W208-02JAN].TOT4, [W208-02JAN].QTY5, [W208-02JAN].DES5,
[W208-02JAN].EA5, [W208-02JAN].TOT5, [W208-02JAN].QTY6, [W208-02JAN].DES6,
[W208-02JAN].TOT15, [W208-02JAN].EA6, [W208-02JAN].QTY7, [W208-02JAN].DES7,
[W208-02JAN].EA7, [W208-02JAN].TOT7, [W208-02JAN].QTY8, [W208-02JAN].DES8,
[W208-02JAN].EA8, [W208-02JAN].TOT8, [W208-02JAN].QTY9, [W208-02JAN].DES9,
[W208-02JAN].EA9, [W208-02JAN].TOT9, [W208-02JAN].QTY10, [W208-02JAN].DES10,
[W208-02JAN].EA10, [W208-02JAN].TOT10, [W208-02JAN].QTY11,
[W208-02JAN].DES11, [W208-02JAN].EA11, [W208-02JAN].TOT11,
[W208-02JAN].QTY12, [W208-02JAN].DES12, [W208-02JAN].EA12,
[W208-02JAN].TOT12, [W208-02JAN].QTY13, [W208-02JAN].DES13,
[W208-02JAN].EA13, [W208-02JAN].TOT13, [W208-02JAN].QTY14,
[W208-02JAN].DES14, [W208-02JAN].EA14, [W208-02JAN].TOT14,
[W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15, [W208-02JAN].TOT6
FROM [W208-02JAN]
ORDER BY [W208-02JAN].CITY;

 

[jayC]

I didn t notice that sorting under 'acct no' was turned off. Here is the
SQL text with it turned on.

The data in datasheet view still is not sorted for 'acct no' within each
city.

SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME],
[W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION,
[W208-02JAN].[ACCT NO], [W208-02JAN].[JOB LOCATION], [W208-02JAN].PRICE,
[W208-02JAN].[CUST NO], [W208-02JAN].QTY1, [W208-02JAN].DES1,
[W208-02JAN].EA1, [W208-02JAN].TOT1, [W208-02JAN].QTY2, [W208-02JAN].DES2,
[W208-02JAN].EA2, [W208-02JAN].TOT2, [W208-02JAN].QTY3, [W208-02JAN].DES3,
[W208-02JAN].EA3, [W208-02JAN].TOT3, [W208-02JAN].QTY4, [W208-02JAN].DES4,
[W208-02JAN].EA4, [W208-02JAN].TOT4, [W208-02JAN].QTY5, [W208-02JAN].DES5,
[W208-02JAN].EA5, [W208-02JAN].TOT5, [W208-02JAN].QTY6, [W208-02JAN].DES6,
[W208-02JAN].TOT15, [W208-02JAN].EA6, [W208-02JAN].QTY7, [W208-02JAN].DES7,
[W208-02JAN].EA7, [W208-02JAN].TOT7, [W208-02JAN].QTY8, [W208-02JAN].DES8,
[W208-02JAN].EA8, [W208-02JAN].TOT8, [W208-02JAN].QTY9, [W208-02JAN].DES9,
[W208-02JAN].EA9, [W208-02JAN].TOT9, [W208-02JAN].QTY10, [W208-02JAN].DES10,
[W208-02JAN].EA10, [W208-02JAN].TOT10, [W208-02JAN].QTY11,
[W208-02JAN].DES11, [W208-02JAN].EA11, [W208-02JAN].TOT11,
[W208-02JAN].QTY12, [W208-02JAN].DES12, [W208-02JAN].EA12,
[W208-02JAN].TOT12, [W208-02JAN].QTY13, [W208-02JAN].DES13,
[W208-02JAN].EA13, [W208-02JAN].TOT13, [W208-02JAN].QTY14,
[W208-02JAN].DES14, [W208-02JAN].EA14, [W208-02JAN].TOT14,
[W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15, [W208-02JAN].TOT6
FROM [W208-02JAN]
ORDER BY [W208-02JAN].CITY, [W208-02JAN].[ACCT NO];

SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME],
[W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION,
[W208-02JAN].[ACCT NO], [W208-02JAN].[JOB LOCATION], [W208-02JAN].PRICE,
[W208-02JAN].[CUST NO], [W208-02JAN].QTY1, [W208-02JAN].DES1,
[W208-02JAN].EA1, [W208-02JAN].TOT1, [W208-02JAN].QTY2, [W208-02JAN].DES2,
[W208-02JAN].EA2, [W208-02JAN].TOT2, [W208-02JAN].QTY3, [W208-02JAN].DES3,
[W208-02JAN].EA3, [W208-02JAN].TOT3, [W208-02JAN].QTY4, [W208-02JAN].DES4,
[W208-02JAN].EA4, [W208-02JAN].TOT4, [W208-02JAN].QTY5, [W208-02JAN].DES5,
[W208-02JAN].EA5, [W208-02JAN].TOT5, [W208-02JAN].QTY6, [W208-02JAN].DES6,
[W208-02JAN].TOT15, [W208-02JAN].EA6, [W208-02JAN].QTY7,
[W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].TOT7, [W208-02JAN].QTY8,
[W208-02JAN].DES8, [W208-02JAN].EA8, [W208-02JAN].TOT8, [W208-02JAN].QTY9,
[W208-02JAN].DES9, [W208-02JAN].EA9, [W208-02JAN].TOT9,
[W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10,
[W208-02JAN].TOT10, [W208-02JAN].QTY11, [W208-02JAN].DES11,
[W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12,
[W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12,
[W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13,
[W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14,
[W208-02JAN].EA14, [W208-02JAN].TOT14, [W208-02JAN].QTY15,
[W208-02JAN].DES15, [W208-02JAN].EA15, [W208-02JAN].TOT6
FROM [W208-02JAN]
ORDER BY [W208-02JAN].CITY;

That is indeed the way to do it. Fields sort left to right. That has no
effect
on how you display the data on a Form or Report though.


Please use View... SQL to switch the view of the query into SQL view.
Copy and
paste the SQL text to a message here. Something is not coming across in
our
explanations and/or your description; the SQL *is* the real query (the
grid is
just a tool to make it easier to build the query).

John W. Vinson [MVP]

 

[jayC]

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.

Many thanks.

I didn t notice that sorting under 'acct no' was turned off. Here is the
SQL text with it turned on.

The data in datasheet view still is not sorted for 'acct no' within each
city.

SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME],
[W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION,
[W208-02JAN].[ACCT NO], [W208-02JAN].[JOB LOCATION], [W208-02JAN].PRICE,
[W208-02JAN].[CUST NO], [W208-02JAN].QTY1, [W208-02JAN].DES1,
[W208-02JAN].EA1, [W208-02JAN].TOT1, [W208-02JAN].QTY2, [W208-02JAN].DES2,
[W208-02JAN].EA2, [W208-02JAN].TOT2, [W208-02JAN].QTY3, [W208-02JAN].DES3,
[W208-02JAN].EA3, [W208-02JAN].TOT3, [W208-02JAN].QTY4, [W208-02JAN].DES4,
[W208-02JAN].EA4, [W208-02JAN].TOT4, [W208-02JAN].QTY5, [W208-02JAN].DES5,
[W208-02JAN].EA5, [W208-02JAN].TOT5, [W208-02JAN].QTY6, [W208-02JAN].DES6,
[W208-02JAN].TOT15, [W208-02JAN].EA6, [W208-02JAN].QTY7,
[W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].TOT7, [W208-02JAN].QTY8,
[W208-02JAN].DES8, [W208-02JAN].EA8, [W208-02JAN].TOT8, [W208-02JAN].QTY9,
[W208-02JAN].DES9, [W208-02JAN].EA9, [W208-02JAN].TOT9,
[W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10,
[W208-02JAN].TOT10, [W208-02JAN].QTY11, [W208-02JAN].DES11,
[W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12,
[W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12,
[W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13,
[W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14,
[W208-02JAN].EA14, [W208-02JAN].TOT14, [W208-02JAN].QTY15,
[W208-02JAN].DES15, [W208-02JAN].EA15, [W208-02JAN].TOT6
FROM [W208-02JAN]
ORDER BY [W208-02JAN].CITY, [W208-02JAN].[ACCT NO];

SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME],
[W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION,
[W208-02JAN].[ACCT NO], [W208-02JAN].[JOB LOCATION], [W208-02JAN].PRICE,
[W208-02JAN].[CUST NO], [W208-02JAN].QTY1, [W208-02JAN].DES1,
[W208-02JAN].EA1, [W208-02JAN].TOT1, [W208-02JAN].QTY2,
[W208-02JAN].DES2, [W208-02JAN].EA2, [W208-02JAN].TOT2,
[W208-02JAN].QTY3, [W208-02JAN].DES3, [W208-02JAN].EA3,
[W208-02JAN].TOT3, [W208-02JAN].QTY4, [W208-02JAN].DES4,
[W208-02JAN].EA4, [W208-02JAN].TOT4, [W208-02JAN].QTY5,
[W208-02JAN].DES5, [W208-02JAN].EA5, [W208-02JAN].TOT5,
[W208-02JAN].QTY6, [W208-02JAN].DES6, [W208-02JAN].TOT15,
[W208-02JAN].EA6, [W208-02JAN].QTY7, [W208-02JAN].DES7, [W208-02JAN].EA7,
[W208-02JAN].TOT7, [W208-02JAN].QTY8, [W208-02JAN].DES8,
[W208-02JAN].EA8, [W208-02JAN].TOT8, [W208-02JAN].QTY9,
[W208-02JAN].DES9, [W208-02JAN].EA9, [W208-02JAN].TOT9,
[W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10,
[W208-02JAN].TOT10, [W208-02JAN].QTY11, [W208-02JAN].DES11,
[W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12,
[W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12,
[W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13,
[W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14,
[W208-02JAN].EA14, [W208-02JAN].TOT14, [W208-02JAN].QTY15,
[W208-02JAN].DES15, [W208-02JAN].EA15, [W208-02JAN].TOT6
FROM [W208-02JAN]
ORDER BY [W208-02JAN].CITY;

I created a simple query, however the test sorts based on more than
field
that I'm creating are not flying.

In the lower pane of the design view of the query, "Customer Name" is a
column to the left of "City," so apparently I will have to rearrange the
fields in the table so that "Customer Name" appears to the right of
"City",
unless there is another way of doing it.

That is indeed the way to do it. Fields sort left to right. That has no
effect
on how you display the data on a Form or Report though.

However, when I attempted to sort it by "City", then "Account Number"
(after
changing the sort row under their respective columns to "ascending"),
the
data were still not appearing correctly in query datasheet view, even
though
it is a field that appears to the right of "City". The data sorted
correctly under "City" but was random under "Account Number."

Please use View... SQL to switch the view of the query into SQL view.
Copy and
paste the SQL text to a message here. Something is not coming across in
our
explanations and/or your description; the SQL *is* the real query (the
grid is
just a tool to make it easier to build the query).

John W. Vinson [MVP]

 

[jayC]

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.

Many Thanks to all.

 

[jayC]

I created a sample form off of a query that was created from the table and
entered data into the query form. Checking back at the original table I see
that the data had changed also, so that solves the problem. I was under the
mistaken impression the query would not handle changing the data.

Many thanks