Creating a report with calculated fields

[HopyMSU]

Hi all,

I am a teacher and I'm trying to get Access to do something that I
found very easy in FileMaker. I know it can be done with Access, just
not exactly sure how. Basically, I have a table with student names
and an instrument that they play. I want to generate a report that
will list the students' names in alphabetical order underneath a
heading of the instrument that they play AND display a count next to
the header.

It all needs to be "dynamic" ... for example, the report will have to
grow/shrink for each section, but also each instrumental section needs
to be pre-sorted so it is in a non-alphabetical order down the page.

It would look like this:

Flute (3)
<<FNAME>> <<LNAME>>
<<FNAME>> <<LNAME>>
<<FNAME>> <<LNAME>>

Clarinet (1)
<<FNAME>> <<LNAME>>

Alto Saxophone (2)
<<FNAME>> <<LNAME>>
<<FNAME>> <<LNAME>>

So... here are the specific questions:
1. How can I sort the instruments so that they are in "score
order" (Flute - clarinet - saxophone - trumpet - trombone ... etc.)?

2. How do I do a count of the number of kids in each section

3. How can I set up a report (or a query for that matter) that lists
the name of the instrument and displays student names below it?

Hope you all can help - thanks!
Andy

 

[HopyMSU]

I should mention... I'm using Access 2007.

A

 

[KARL DEWEY]

Add a field named Sort, using numerials to designate which is first, second,
third, etc.
In report design view click on Group and Sort, select the Sort field and
then add names.
In the Sort header put the instrument field. Add text box and for source
use =Count[LNAME])

 

[HopyMSU]

Karl,

Thanks. I've got it now so it counts the total; but it only displays
one instrument and name... now how do I get it to show ALL names AND
each instrument (with names)? Does it have something to do with
grouping?
Karl,

Thanks. I've got it now so it counts the total; but it only displays
one instrument and name... now how do I get it to show ALL names AND
each instrument (with names)? Does it have something to do with
grouping?

A