Report sorting & grouping 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

 

[Arvin Meyer]

How about spending a few hours learning how to write good queries? With
that, you'll be able to write lots of reports. In the meantime, this will
probably help:

To do what you want, you need to filter (query) to get the records which
have or don't have the word "various" in them. You can do that by creating a
new column:

Various: IIF(Left([FieldName], 7) = "Various"), [FieldName], "")

which will return the field name for every record you have that has the word
"Various" as the first word. Now you should be able to use thereport wizard
to build a report which will group on the "Various" column that you have
created.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access