Filtering a Field

[Zunn]

Example

At the moment I have the fields Class, Pupil and Hair Colour in the table format

Pupil Class Hair Colour
W 3a Yellow
X 3a Red
Y 3b Yellow
Z 3b Blue
.. .. ..

I would like my report to do this

Report showing Pupil's hair colour

Class Red Blue Yellow
3a 5 4 2
3b 6 3 5

At the moment I can do this by
creating a 3a query and a 3b query manually
then using DCount ("[Hair Colour]", "[" & Class & " query]", "[Hair Colour] = 'Red'&quot where Class is a bound text box linked to a query that lists all the different classes

The problem with doing this is that I have to manually create a query for every Class. Not a very good idea as I have to keep a constant track of what Classes are newly formed and create new queries.

Ideally I would like to modify the DCount line above to produce the report without me having to create the individual queries beforehand.

Any ideas?

 

[JoeMiller]

You need a crosstab query. Go to the query tab, click new select the crosstab query wizard and go through it. It's very simple to understand and does exactly what you are trying to do.

HTH Joe Miller
joe.miller@flotech.net