Crosstab Query?

B

bassplayer

Hi All
I have a db which records referrals sent into the office. Dat
entered onto the db includes the usual name of student, school nam
etc, and also includes 8 checkboxes for services requested (Guidanc
Officer, Speech Pathologist, Behaviour Management Councellor etc).
have managed to create an unbound text box to count the number o
services requested eg the unbound text box shows a 1 when onl
guidance officer is ticked or 2 if both guidance officer and speec
pathologist are ticked or 3 if guidance, speech and behaviour i
ticked etc. How can i create a query that i can use in a report t
show me under each school how many times 1 service has bee
requested, how many times 2 services have been requested, how man
times three services have been requested and so on


thanks for your hel
 
M

MGFoster

bassplayer said:
Hi All,
I have a db which records referrals sent into the office. Data
entered onto the db includes the usual name of student, school name
etc, and also includes 8 checkboxes for services requested (Guidance
Officer, Speech Pathologist, Behaviour Management Councellor etc). I
have managed to create an unbound text box to count the number of
services requested eg the unbound text box shows a 1 when only
guidance officer is ticked or 2 if both guidance officer and speech
pathologist are ticked or 3 if guidance, speech and behaviour is
ticked etc. How can i create a query that i can use in a report to
show me under each school how many times 1 service has been
requested, how many times 2 services have been requested, how many
times three services have been requested and so on.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It is better to put the info that is in the check boxes into a separate
table. E.g.:

Students (the original table)
StudentID
FirstName
LastName
SchoolName
etc.

Then have a table of Requested services:

StudentServiceRequests
StudentID - links as Foreign Key to Students.StudentID
ServiceDate
ServiceTypeCode - links as Foreign Key to ServiceTypes (below)

Then a list of Service Types:

ServiceTypes
ServiceTypeCode
ServiceTypeDefinition

Example of ServiceTypes records:

ServiceTypeCode ServiceTypeDefinition
--------------- ---------------------
1 Guidance Officer
2 Speech Pathologist
3 Behaviour Management Councellor

The StudentServiceRequests records would look like this:

StudentID ServiceDate ServiceTypeCode
--------- ----------- ---------------
1 1/1/2005 1
1 3/1/2005 2
3 1/15/2005 3
4 2/22/2005 1
... etc. ...

Then to get a count of the service types requested in a certain period:

SELECT ST.ServiceTypeDefinition, Count(SSR.ServiceTypeCode)
FROM StudentServiceRequests As SSR INNER JOIN ServiceTypes As ST
ON SSR.ServiceTypeCode = ST.ServiceTypeCode
WHERE SSR.ServiceDate BETWEEN #1/1/2005# And #6/30/2005#
GROUP BY ST.ServiceTypeDefinition

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyiiWoechKqOuFEgEQL/iwCgyTJBQjaRCAcUSayiuasZeOFN6m4AoNHz
wH5wDjGz8wX9C0dFlUV0L4VJ
=Qopa
-----END PGP SIGNATURE-----
 
Top