Separate Sort Order within same record

[Roddy]

I'm using Access 2002 and made up a report of my CD collection. I have
records with fields such as: STYLE, INSTRUMENT, ARTIST, ALBUM TITLE, YEAR,
etc.. I have all my records sorted by style, instrument, artists, album,
year, etc.

Is it possible to separately sort a specific range of records (say, all my
CDs under the style: "Soundtracks" without affecting the sort order of the
rest of the report?

 

[Ken Snell \(MVP\)]

You'd need to use a subreport for that particular "range" (and of course
exclude that range from your main report's RecordSource query) and then you
can set up that subreport's sorting/grouping differently from the rest of
the report.

 

[KARL DEWEY]

Try this calculated sort field ---
Sort_Field: IIF([style]= "Soundtracks", " " &[INSTRUMENT]& [ARTIST]&
[ALBUM]& [TITLE]& [YEAR], [Style]& [INSTRUMENT]& [ARTIST]& [ALBUM]& [TITLE]&
[YEAR])

The above will sort soundtracks at the top. If you use "ZZZZZ" in stead of
space then soundtracks will sort to the bottom.

 

[Roddy]

Ken & Karl,

I appreciate your quick responses, but unfortunately I don't know how to go
about doing either of your solutions. If it's not too much to ask, could you
give me step-by-step instructions, including what menu to go to, etc. to do
this?
--
In God''s Harmony

Try this calculated sort field ---
Sort_Field: IIF([style]= "Soundtracks", " " &[INSTRUMENT]& [ARTIST]&
[ALBUM]& [TITLE]& [YEAR], [Style]& [INSTRUMENT]& [ARTIST]& [ALBUM]& [TITLE]&
[YEAR])

The above will sort soundtracks at the top. If you use "ZZZZZ" in stead of
space then soundtracks will sort to the bottom.

--
KARL DEWEY
Build a little - Test a little

I'm using Access 2002 and made up a report of my CD collection. I have
records with fields such as: STYLE, INSTRUMENT, ARTIST, ALBUM TITLE, YEAR,
etc.. I have all my records sorted by style, instrument, artists, album,
year, etc.

Is it possible to separately sort a specific range of records (say, all my
CDs under the style: "Soundtracks" without affecting the sort order of the
rest of the report?

 

[KARL DEWEY]

If your report is not based upon a query then create one. Then open the
report in design view and click on menu VIEW - Properties. Change the Record
Source property to the name of the query. Save and close.

Modify the query by opening in design view. If any of the fields have
Ascending or Descending in the Sort row remove the sort. Scroll the grid to
the right so that a blank column is in view. Copy and paste my post in the
Field row of the blank column. Edit it to have field names to be sorted on
in left to right order of high to low sort order (enclosed in brackets and
connected by ampersand.). Save and close.

Open the report in design view and click on menu VIEW - Sorting and
Grouping. In the new window select the Sort_Field. Save. Run.

--
KARL DEWEY
Build a little - Test a little

Ken & Karl,

I appreciate your quick responses, but unfortunately I don't know how to go
about doing either of your solutions. If it's not too much to ask, could you
give me step-by-step instructions, including what menu to go to, etc. to do
this?
--
In God''s Harmony

Try this calculated sort field ---
Sort_Field: IIF([style]= "Soundtracks", " " &[INSTRUMENT]& [ARTIST]&
[ALBUM]& [TITLE]& [YEAR], [Style]& [INSTRUMENT]& [ARTIST]& [ALBUM]& [TITLE]&
[YEAR])

The above will sort soundtracks at the top. If you use "ZZZZZ" in stead of
space then soundtracks will sort to the bottom.

--
KARL DEWEY
Build a little - Test a little

I'm using Access 2002 and made up a report of my CD collection. I have
records with fields such as: STYLE, INSTRUMENT, ARTIST, ALBUM TITLE, YEAR,
etc.. I have all my records sorted by style, instrument, artists, album,
year, etc.

Is it possible to separately sort a specific range of records (say, all my
CDs under the style: "Soundtracks" without affecting the sort order of the
rest of the report?

 

[Roddy]

I tried to follow your instructions, unfortunately, I'm not too familiar with
queries and such. I followed through, but after pasting and saving and
re-opening, it still doesn't work. Is there no way to just hilight the
records you want to sort separately, rather than having to go into details
andand have to come up with more commands? It seems that this should have
been a simple task feature without having to go through all this.--
In God''s Harmony

If your report is not based upon a query then create one. Then open the
report in design view and click on menu VIEW - Properties. Change the Record
Source property to the name of the query. Save and close.

Modify the query by opening in design view. If any of the fields have
Ascending or Descending in the Sort row remove the sort. Scroll the grid to
the right so that a blank column is in view. Copy and paste my post in the
Field row of the blank column. Edit it to have field names to be sorted on
in left to right order of high to low sort order (enclosed in brackets and
connected by ampersand.). Save and close.

Open the report in design view and click on menu VIEW - Sorting and
Grouping. In the new window select the Sort_Field. Save. Run.

--
KARL DEWEY
Build a little - Test a little

Ken & Karl,

I appreciate your quick responses, but unfortunately I don't know how to go
about doing either of your solutions. If it's not too much to ask, could you
give me step-by-step instructions, including what menu to go to, etc. to do
this?
--
In God''s Harmony

Try this calculated sort field ---
Sort_Field: IIF([style]= "Soundtracks", " " &[INSTRUMENT]& [ARTIST]&
[ALBUM]& [TITLE]& [YEAR], [Style]& [INSTRUMENT]& [ARTIST]& [ALBUM]& [TITLE]&
[YEAR])

The above will sort soundtracks at the top. If you use "ZZZZZ" in stead of
space then soundtracks will sort to the bottom.

--
KARL DEWEY
Build a little - Test a little


:

I'm using Access 2002 and made up a report of my CD collection. I have
records with fields such as: STYLE, INSTRUMENT, ARTIST, ALBUM TITLE, YEAR,
etc.. I have all my records sorted by style, instrument, artists, album,
year, etc.

Is it possible to separately sort a specific range of records (say, all my
CDs under the style: "Soundtracks" without affecting the sort order of the
rest of the report?

 

[KARL DEWEY]

You could do as Ken Snell suggested.
Or you can post your query SQL, tell me the sort order for all but the
soundtracks, and the sort order for the soundtracks. I or someone else will
edit your query SQL to do that sorting.

You will need to paste it in your query (remember that the post add a hard
return at the end of each displayed line so you must edit them out of the
SQL.). You will then need to open the report in design view and click on
menu VIEW - Sorting and Grouping. In the new window select the Sort_Field.
Save. Run.

--
KARL DEWEY
Build a little - Test a little

I tried to follow your instructions, unfortunately, I'm not too familiar with
queries and such. I followed through, but after pasting and saving and
re-opening, it still doesn't work. Is there no way to just hilight the
records you want to sort separately, rather than having to go into details
andand have to come up with more commands? It seems that this should have
been a simple task feature without having to go through all this.--
In God''s Harmony

If your report is not based upon a query then create one. Then open the
report in design view and click on menu VIEW - Properties. Change the Record
Source property to the name of the query. Save and close.

Modify the query by opening in design view. If any of the fields have
Ascending or Descending in the Sort row remove the sort. Scroll the grid to
the right so that a blank column is in view. Copy and paste my post in the
Field row of the blank column. Edit it to have field names to be sorted on
in left to right order of high to low sort order (enclosed in brackets and
connected by ampersand.). Save and close.

Open the report in design view and click on menu VIEW - Sorting and
Grouping. In the new window select the Sort_Field. Save. Run.

--
KARL DEWEY
Build a little - Test a little

Ken & Karl,

I appreciate your quick responses, but unfortunately I don't know how to go
about doing either of your solutions. If it's not too much to ask, could you
give me step-by-step instructions, including what menu to go to, etc. to do
this?
--
In God''s Harmony


:

Try this calculated sort field ---
Sort_Field: IIF([style]= "Soundtracks", " " &[INSTRUMENT]& [ARTIST]&
[ALBUM]& [TITLE]& [YEAR], [Style]& [INSTRUMENT]& [ARTIST]& [ALBUM]& [TITLE]&
[YEAR])

The above will sort soundtracks at the top. If you use "ZZZZZ" in stead of
space then soundtracks will sort to the bottom.

--
KARL DEWEY
Build a little - Test a little


:

I'm using Access 2002 and made up a report of my CD collection. I have
records with fields such as: STYLE, INSTRUMENT, ARTIST, ALBUM TITLE, YEAR,
etc.. I have all my records sorted by style, instrument, artists, album,
year, etc.

Is it possible to separately sort a specific range of records (say, all my
CDs under the style: "Soundtracks" without affecting the sort order of the
rest of the report?

 

[Roddy]

Karl,
Once again I thank you for your help. I know this is going to sound like a
lazy response, but this is just a simple database of my music collection. Had
this been for something much more intense, I’d go through the whole procedure
of posting the info and letting you fix it for me. I really don’t want to
take up your time with this when someone else could be using your services. I
just wanted to have an organized database of my CDs and have them sorted,
with the soundtrack albums being the only ones I wanted sorted by album title
only (the rest of my collection is sorted by: Style, Instrument, Artist Last
Name, Artist First Name, Year of Album, and finally the actual Album Title).

I tried doing it in Excel, but Excel only allows sorting by 3 columns. Too
bad, if it had the ability to sort through all columns like in Access, I
could just highlight the individual records and sort them separate in just a
few clicks. In any event, thanks again for your help.
--
In God''s Harmony

You could do as Ken Snell suggested.
Or you can post your query SQL, tell me the sort order for all but the
soundtracks, and the sort order for the soundtracks. I or someone else will
edit your query SQL to do that sorting.

You will need to paste it in your query (remember that the post add a hard
return at the end of each displayed line so you must edit them out of the
SQL.). You will then need to open the report in design view and click on
menu VIEW - Sorting and Grouping. In the new window select the Sort_Field.
Save. Run.

--
KARL DEWEY
Build a little - Test a little

I tried to follow your instructions, unfortunately, I'm not too familiar with
queries and such. I followed through, but after pasting and saving and
re-opening, it still doesn't work. Is there no way to just hilight the
records you want to sort separately, rather than having to go into details
andand have to come up with more commands? It seems that this should have
been a simple task feature without having to go through all this.--
In God''s Harmony

If your report is not based upon a query then create one. Then open the
report in design view and click on menu VIEW - Properties. Change the Record
Source property to the name of the query. Save and close.

Modify the query by opening in design view. If any of the fields have
Ascending or Descending in the Sort row remove the sort. Scroll the grid to
the right so that a blank column is in view. Copy and paste my post in the
Field row of the blank column. Edit it to have field names to be sorted on
in left to right order of high to low sort order (enclosed in brackets and
connected by ampersand.). Save and close.

Open the report in design view and click on menu VIEW - Sorting and
Grouping. In the new window select the Sort_Field. Save. Run.

--
KARL DEWEY
Build a little - Test a little


:

Ken & Karl,

I appreciate your quick responses, but unfortunately I don't know how to go
about doing either of your solutions. If it's not too much to ask, could you
give me step-by-step instructions, including what menu to go to, etc. to do
this?
--
In God''s Harmony


:

Try this calculated sort field ---
Sort_Field: IIF([style]= "Soundtracks", " " &[INSTRUMENT]& [ARTIST]&
[ALBUM]& [TITLE]& [YEAR], [Style]& [INSTRUMENT]& [ARTIST]& [ALBUM]& [TITLE]&
[YEAR])

The above will sort soundtracks at the top. If you use "ZZZZZ" in stead of
space then soundtracks will sort to the bottom.

--
KARL DEWEY
Build a little - Test a little


:

I'm using Access 2002 and made up a report of my CD collection. I have
records with fields such as: STYLE, INSTRUMENT, ARTIST, ALBUM TITLE, YEAR,
etc.. I have all my records sorted by style, instrument, artists, album,
year, etc.

Is it possible to separately sort a specific range of records (say, all my
CDs under the style: "Soundtracks" without affecting the sort order of the
rest of the report?

 

[Chuck]

I tried doing it in Excel, but Excel only allows sorting by 3 columns. Too
bad, if it had the ability to sort through all columns like in Access, I
could just highlight the individual records and sort them separate in just a
few clicks. In any event, thanks again for your help.

It's possible to sort by more than 3 columns in Excel, but its awkward.
Sort columns a,b,c,d,e,f,g ascending.

First sort e,f,g ascending.
Second sort b,c,d ascending.
Third sort a ascending.

If this is a one time deal, it's not too bad.
If it is every time you enter data, Move your data to Access.

Chuck
--

 

[Chuck]

Karl,
Once again I thank you for your help. I know this is going to sound like a
lazy response, but this is just a simple database of my music collection. Had
this been for something much more intense, I’d go through the whole procedure
of posting the info and letting you fix it for me. I really don’t want to
take up your time with this when someone else could be using your services. I
just wanted to have an organized database of my CDs and have them sorted,
with the soundtrack albums being the only ones I wanted sorted by album title
only (the rest of my collection is sorted by: Style, Instrument, Artist Last
Name, Artist First Name, Year of Album, and finally the actual Album Title).

I tried doing it in Excel, but Excel only allows sorting by 3 columns. Too
bad, if it had the ability to sort through all columns like in Access, I
could just highlight the individual records and sort them separate in just a
few clicks. In any event, thanks again for your help.

If you put all your data in an Access table, it is not necessary to sort the
records in the table. The sorting is done in the individual reports you make
(one for the sound tracks, one for everything else) . It is possible to have
it all in one report with the sound tracks first followed by all the rest.

That said, all your data in one table is not the best way to make a database.
Check out database normalization. It will make your database more compact,
easier to work with, and less prone to input errors.

Chuck
--

 

[Roddy]

Chuck,
Thank you also for your input. I was able to transfer to Excel, and do
several passes to sort with no problem. But now I'm curious about what you
said of database normalization, what is that? I'm not familiar with that
term. My database in Access is in table format, with all my styles sorted. It
was just that I wanted certain styles within the same table to be sorted
fifferently without affecting the sort order of the rest of the records
within the same table.

 

[Chuck]

Chuck,
Thank you also for your input. I was able to transfer to Excel, and do
several passes to sort with no problem. But now I'm curious about what you
said of database normalization, what is that? I'm not familiar with that
term. My database in Access is in table format, with all my styles sorted. It
was just that I wanted certain styles within the same table to be sorted
fifferently without affecting the sort order of the rest of the records
within the same table.

The data in an Access table does not have to be 'sorted' in any particular way.
Therefore, it doesn't have to be 'resorted' every time new data is entered.
The sorting is done when by a report or a form when it is opened to display one
or more records.

Normalization basically means that you don't enter the same data over and over
again in a table. It is nor essential, but it is good practice.

Example: In a table with people and address you don't want to enter the same
city and state for a hundred different people. You would set up a table with
fields for zip code, city, and state. You would make the zip code a Primary
Key in that table. Then in the 'main table' you would enter only the zip code,
which is called a Foreign Key. The two tables are "related" Primary Key to
Foreign Key. When you make a report, Access uses the Foreign Key in the 'main
table' to 'look up' the related city and state. Since there is only one entry
for each city and state, it can not be misspelled in the data entry process.

Zip codes are not the best example because one city can have more than on zip
code, but it is easy to visualize. Actually a zip code only denotes the Post
Office for the area where the individuals' goes through. It is possible for
several small towns to have a zip code with the same first five numbers, but
when that happens, the last four numbers in the 5+4 zip code nomenclature will
be different. This situation can also happen within one city if it is very
densely populated. The last four numbers denoting an area that the post office
services.

If you see the same data in several fields for many records, there is a good
chance that it should be pulled out and placed in a related table. Again, not
necessary, only good practice.

Chuck
--

 

[Roddy]

Thanks for the lesson Chuck, very informative, and well written.