Need something like COUNT IF 1

[MagiMike]

I have two tables (made it a little simpler than the actual problem):

Student_T
Name Age
Mike 34
Steve 29

Test_T
Student Type
Mike A
Mike B
Mike B
Steve C

Student is linked to Student_T.Name
Type can only be A, B or C

The resulting report should look like:
Name A-test B-test C-test
Mike 1 2 0
Steve 0 0 1

I'm thinking of something like
SELECT Name, COUNT Type IF Type=A, COUNT Type IF Type=B...
FROM Student_T, Test_T
WHERE Name=Student
GROUP BY Name

Unfortunately COUNT IF doesn't exist in Access. Is there a similar command or is there a better way of doing it?

 

[bpurser]

Check out the Crosstab query wizard. In the Queries tab in the db window, click New, click on Crosstab. Use Student as the row heading, TestType as column heading, count as the function.

You said you simplified things, so I'm assuming that A) you're not basing the relationship on the first name of your students, but rather an ID field....and B) that you don't actually have fields named Type and Name, which are reserved words in access, but just in case, I mention these things...

HTH
Ben

 

[MagiMike]

Yes, I made up the problem to make it a simple as possible. Thanks for the design tips though, didn't think about them (not that experienced with Access...).

Seems like Crosstab was exactly what I looked for or rather the commands TRANSFORM and PIVOT.

Thanks for the help

Mike