Query to 'Group By' and 'Count'

[Ambre]

Hi,

I have a table of data, Enrolments, that includes the columns, [DeptNo], [CourseNo], [LearnerNo] and [Gender] with a load of other data. The same [LearnerNo] can have rows relating to one or more [CourseNo]'s that might be in one or more [Dept]'s.

I need a query that will return by department {Group by [Dept]} and gender {group by [Gender]} the number of enrolments in the department {count of [LearnerNo]} and also the head-count by gender.

It's this last bit that I am stuck on. What I need is the number of (Males) already returned by {group by [Gender]} grouped by [LearnerNo] and counted ** I think **.

Sample data:
[Dept] [CourseNo] [LearnoNo] [Gender]
X AA R M
X AA S F
X AA T M
X AB R M
X AC R M
X AC S F
X AC T M
X AD R M

would return for department X
6 male enrolments by 2 Learners and
2 female enrolments by 1 Learner

Is there a way to do this?

Thanks in advance,
Ambre

 

[vbaInet]

You will find what you want in these links:

http://www.functionx.com/access/report/grouping.htm
http://office.microsoft.com/en-us/access/HA011377631033.aspx