Reporting grouping sorting question...

[r-gordon-7]

I'm asking this question because I'm finally forced to convert to MS Access
from an old DOS flat file program. I've converted an existing approximate
14,000 record database which I've easily and successfully maintained for
years - and to which I add new records & change existing records
frequently. (I collect music - LP records & CDs - and this is the database
I use to keep track of my collection.) Until recently, I've been able to
maintain the database in a wonderful & extremely simple yet flexible old DOS
flat file database program, but that DOS program won't run on my new
computer running XP, so I've imported the database into Access. But the old
DOS database program allowed me to do something very simply that is proving
a nightmare to try to do in Access.



I have an existing report (now in Access) which allows me to sort, group and
print out the entire database, something which I need to do periodically.
The report sorts everything in the database alphabetically on the ARTIST
field as the "first sorting level". Within each group of records with a
common ARTIST field entry (for example, "BEATLES", "MOZART", "VARIOUS ROCK",
or "VARIOUS CLASSICAL"), what I need to do next (as the "second sorting
level") is to sort that group numerically on one field (YEAR) for all groups
in which the ARTIST entry does NOT include the word "VARIOUS" (i.e.
"BEATLES" or "MOZART") and alphabetically on a different field (TITLE) for
all groups in which the ARTIST entry contains the word "VARIOUS" (i.e.
"VARIOUS ROCK" or "VARIOUS CLASSICAL"). That way, the report will print out
showing all the records for each grouping of individual artists
chronologically by artist, and all the records for each "various artist"
(i.e. anthology) grouping alphabetically by title.



How do I do this in Access (without spending the rest of my life learning to
be an Access programmer)?



Thanks,

rgordon

 

[Douglas J. Steele]

Base your report on a query, not the table.

In the query, add a computed field that'll contain your sort criteria.
Something along the lines of:

SortTag: IIf(InStr([Artist], "Various") > 0, [Year], [Title])

 

[r-gordon-7]

Douglas,

Thank you!!! It worked perfectly (with only a slight bit of tweaking - I had
to reverse the position of TITLE and YEAR in the computed field entry you
created for it to sort the correct sets of groups by the correct fields -
but that was pretty much all I had to tweak...)! I never would have gotten
to this point without your help...

This is the very first moment since upgrading to Windows all those many
years ago that I can say I no longer need any DOS programs!

Thanks again,
rgordon

Base your report on a query, not the table.

In the query, add a computed field that'll contain your sort criteria.
Something along the lines of:

SortTag: IIf(InStr([Artist], "Various") > 0, [Year], [Title])



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm asking this question because I'm finally forced to convert to MS Access
from an old DOS flat file program. I've converted an existing approximate
14,000 record database which I've easily and successfully maintained for
years - and to which I add new records & change existing records
frequently. (I collect music - LP records & CDs - and this is the database
I use to keep track of my collection.) Until recently, I've been able to
maintain the database in a wonderful & extremely simple yet flexible old DOS
flat file database program, but that DOS program won't run on my new
computer running XP, so I've imported the database into Access. But the old
DOS database program allowed me to do something very simply that is proving
a nightmare to try to do in Access.



I have an existing report (now in Access) which allows me to sort, group and
print out the entire database, something which I need to do periodically.
The report sorts everything in the database alphabetically on the ARTIST
field as the "first sorting level". Within each group of records with a
common ARTIST field entry (for example, "BEATLES", "MOZART", "VARIOUS ROCK",
or "VARIOUS CLASSICAL"), what I need to do next (as the "second sorting
level") is to sort that group numerically on one field (YEAR) for all groups
in which the ARTIST entry does NOT include the word "VARIOUS" (i.e.
"BEATLES" or "MOZART") and alphabetically on a different field (TITLE) for
all groups in which the ARTIST entry contains the word "VARIOUS" (i.e.
"VARIOUS ROCK" or "VARIOUS CLASSICAL"). That way, the report will print out
showing all the records for each grouping of individual artists
chronologically by artist, and all the records for each "various artist"
(i.e. anthology) grouping alphabetically by title.



How do I do this in Access (without spending the rest of my life learning to
be an Access programmer)?



Thanks,

rgordon