Counting Problem...

[dynamite9585]

I need a way to get a count of records matching multiple criteria.

Table: TblCadets
Criteria:
[Rank] = U/O,
[Gender] = Female,
[Active] = yes

the table at the moment has 1 matching record. this my be the sticking point. the number then needs to be able to be exported into an existing excel workbook.

Thanks for you help

 

[John Big Booty]

Check out the DCount() function.

 

[dynamite9585]

had a look at that. have been trying to break it down and tackle it bit by bit.

Expr1: DCount("ID","TblCadets",[Gender]="Female")
is what i have so far, it's returning 3 entries under expr1 2 of them (the ones set to female) return a count of 3, the male in the list returns a 0

 

[John Big Booty]

You could also have a look at the options available to you in a Totals Query.

 

[dynamite9585]

Sorry for being a pain.
I have a working totals query.

SELECT Count(TblCadets.ID) AS CountOfID
FROM TblCadets
WHERE (((TblCadets.Rank)="U/O") AND ((TblCadets.Gender)="Female") AND ((TblCadets.Active)=Yes));

i also need to find other combinations, other ranks and genders ect.
is there some way i can modify this to put all the combinations in, or will i need a new query for each one? If i need a new qurey for each one, that will give me a total of 14 queries for the one task, that seems silly.

 

[John Big Booty]

Are you able to post a copy of your DB? '03 version for preference.

 

[dynamite9585]

won't let me save it as an older version sorry.
this is the 2010 verson

 

[John Big Booty]

Sorry '07 I can do but I don't have '10, so perhaps someone else will jump in and help