Reports with counting - brick wall.....head

[diggadave]

Hello

I've tried and tried to get this to work, but to no avail.

I am working on a database for an information centre who offer information to young people, based around 10 headings.

What I have is a table, which contains the following field names:-

infoid (autonumber)
infodate (date/time)
infoheading (lookup another table)
16+ (yes/no)
sex (lookup (male/female))
intials (text)

I then have a query on this table, which is set to query it on month and year, dependant on the month and year selected on a form I have.

What I would then like to do, is have a report, which will display the number of times information has been requested.

For example, infoheading could be equal to 1 which in the table is displayed as education, from that I would like to be able to display the number of times educational information has been saught, and so on for the 9 remaining headings and a total for all information, without displaying any other data, and fitting the report on one page.

If anyone needs any more information, or would like to have the database to look at for yourselves, please email me at digga.dave@virgin.net

Thanks

Digga

 

[mpastore]

Dave,

We are inevitably back to SQL (assume your example table is called info_request and your decode table info_types):

select info_types.infotype
,count(*) as num_requests
from info_request,info_types
where info_types.infoheading=info_request.infoheading
and month(info_request.infodate)=[my_month_parm]
and year(info_request.infodate)=[my_year_parm]
group by info_types.infotype

The [parms] will probably reference back to combo box values in your calling form.



Mike Pastore

Hats off to (Roy) Harper