stats page

[john.kingscott]

I have set up avery simple database (instrumental pupil list - includes
what instrument they play , when they started etc..) Can I create a
page that will return general stats? like how many kids in the database
/ how many at each school.
A query returning say, each instrument is easy - but just the numbers
seems harder!

John
UK

 

[Trey Davis]

YOU'RE AN IDIOT AND YOU DON'T MAKE ANY SENSE AT ALL

 

[Al Campagna]

John,
You'll find it easier to create a report to do this.
Create a query behind the report that returns all your data
School Intrument Student StartDate

On your report, use the Sorting and Grouping to Group by those fields, and create a
Header and Footer for each. In each footer, you can Count the appropriate value...

Your report should come out like this...
Davis School (School Header)
Clarinet (Instrument Header)
Bob Davis 1/1/06 (Name Detail)
Mary Jones 2/1/05 (Name Detail)
Clarinet Count = 2 (Instrument Footer)

Trumpet (Instrument Header)
Ed Brown 1/8/06 (Name Detail)
Trumpet Count =1 (Instrument Footer)
Davis School Count = 3 (School Footer)

Elm School (School Header)
Clarinet (Instrument Header)
Jane Smith 1/5/06 (Name Detail)
etc etc.....

 

[London Chap]

thanks!

The report is fine but I'm trying to avoid listing the names, something
like:
Davis school
number of pupils learning : 20
number of trumpets: 12
number of clarinets: 8
that kind of thing.

 

[Al Campagna]

London Chap,
Well, it would seem that if you had 20 instruments agaianst a school, that would imply
that 20 students are "learning"...
So, if that's logically true... then you would just need a School Group and an
Instrument group, with NO details, and just totals in the appropriate footer.
Dibley School School Header
Trumpets 10 InstrFooter
Clarinets 5 Instr Footer
Dibley Total 15 School Footer

Darrowby School
etc....

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[London Chap]

yes! its just how do i do that! is it like a formula in excel? its the
actual doing it that the problem!

 

[Al Campagna]

London Chap,
Sorry if I misunderstood...
You could do a Totals query behind the report. (In query design View/Totals)
SchoolName InstrumentName InstrumentName
GroupBy GroupBy Count

Will yield...

Dibley School (School Header)
Clarinet 8 (Detail)
Trumpet 7 (Detail)
-----------------------------------------------
Dibley Totals 15 (School Footer)**

Darrowby
Harp 2
Drums 4
---------------------------------------------
Darrowby Totals 6 (School Footer)

All Schools Total 21 (Report Footer)


** In the School Footer place an unbound text control with a ControlSource of...
= [SchoolName] & "Totals " & Count(Instruments)
In the Report Footer use this
= "All Schools Total " & Count(Instruments)

Count(Instruments) in the School footer yields the count for the School...
In the Report Footer it yields the Count overall.