Count records (in a query) based on certain criteria and display the results on forms

[Marlene23]

Good Day!

I am busy building a "Projects" database for our training department. This is my second database I have build and have come a far end to where I am now through reading forum posts and watching videos :-)

I am however stuck as to what I am trying to do and is hoping that someone will be able to assist or guide me in the right direction. I have played around with the DCOUNT function as well as the grouping and sum functions within queries but still do not get the result I am looking for.

I built the below query getting the data from the different tables:

Project	Name	Gender	Race	Disabled	Status
Project 1	Student 1	Female	African	Yes	Active
Project 1	Student 2	Female	Coloured	No	Terminated
Project 1	Student 3	Male	White	No	Active
Project 1	Student 4	Female	Indian	No	Terminated
Project 1	Student 5	Male	African	No	Completed
Project 2	Student 6	Male	Coloured	No	Active
Project 2	Student 7	Male	White	Yes	Active
Project 2	Student 8	Female	Indian	Yes	Terminated
Project 2	Student 9	Female	African	Yes	Active
Project 2	Student 10	Male	African	No	Terminated
Project 2	Student 11	Female	African	No	Completed
Project 2	Student 12	Male	Coloured	Yes	Active
Project 2	Student 13	Male	White	Yes	Terminated
Project 2	Student 14	Female	Indian	Yes	Completed
Project 2	Student 15	Female	White	Yes	Active
Project 2	Student 16	Female	African	Yes	Active
Project 2	Student 17	Female	Coloured	Yes	Terminated
Project 3	Student 18	Male	African	No	Completed
Project 3	Student 19	Male	Coloured	No	Active
Project 3	Student 20	Female	White	No	Terminated
Project 3	Student 21	Female	Indian	No	Completed

What I would like to do is do a summary per Project as below and display the results on my Project forms as well:

	Female	Male	African	Coloured	Indian	White	Disabled	Active	Terminated	Completed	Total Learners per Project
Project 1	3	2	2	1	1	1	1	2	2	1	5
Project 2	7	5	4	3	2	3	9	6	4	2	12
Project 3	2	2	1	1	1	1	0	1	1	2	4

The above counts the number of records for male, female etc. per project.



What will be the best approach to do this in a query and then to display the results in an unbound text box on the forms per project as well?

Any assistance will be greatly appreciated!

[June7]

Options:

Multiple CROSSTAB queries then join those queries

Another approach with CROSSTAB - review http://allenbrowne.com/ser-67.html#MultipleValues

or

Multiple IIf() expressions in an aggregate (GROUP BY) query.

SELECT Project, Count(IIf([Gender]="Female",1,Null)) AS Female, Count(IIf([Gender]="Male",1,Null)) AS Male FROM query GROUP BY Project;

[CJ_London]

another way would be to create a union query to place each column value in one column e.g.

SELECT Project, Gender
FROM myTable
UNION ALL SELECT Project, Race
FROM myTable
etc

then use a crosstab query on the union query

[June7]

Oh, yes, but probably want to use alias field name in first Select. And maybe a third field although 1 field could serve double duty for a header and count.

SELECT Project, Gender AS Data, "Gender" AS Category FROM myTable
UNION ALL SELECT Project, Race, "Race" FROM myTable
UNION ALL SELECT Project, Disabled, "Disabled" FROM myTable
UNION ALL SELECT Project, Status, "Status" FROM myTable;

Then CROSSTAB

TRANSFORM Count(Category) AS CountOfCategory
SELECT Project
FROM Query
GROUP BY Project
PIVOT Data;

[Marlene23]

Thank you so much! I am playing around with the crosstab queries and so far it is working well.

How would I be able to display the values calculated in the crosstab query to display on the form for that specific project?

[June7]

Could try a subform and set Master/Child Links.

[Marlene23]

Thank you for the assistance and guidance. The crosstab queries already assisted a great deal in what I was trying to achieve :-)