I'm trying to count the number of times a value appears in a table, where it's ID matches another tables ID.

The following is the relevant information from each table:

tblInfo contains ClientID, Gender
tblDetail contains ClientID

I want to count the number of times that the gender "Male" occurs in tblInfo where tblDetail.ClientID = tblInfo.ClientID

I've tried

SELECT DCount("[Gender]","tblInfo","gender = 'male' ") AS M,
DCount("[Gender]","tblInfo","gender = 'female' ") AS F
FROM tblInfo, tblDetail
where tblInfo.ClientID = tblDetail.ClientID;

But this returns a count of ALL the records for 'Male' and 'Female' in tblInfo, instead of only those where ClientID matches.

Could someone tell me what I'm doing wrong?

[This message has been edited by woodrow (edited 03-26-2002).]

Use this instead:

SELECT Count(*)
FROM tblInfo, tblDetail
WHERE tblInfo.ClientID=tblDetail.ClientID
AND tblInfo.Gender = "M"
;

>But this returns a count of ALL the records for 'Male' and 'Female' in tblInfo, instead of only those where ClientID matches.<

Your query returns the counts you're looking for for each row which meets the criteria in your where clause.
If you put DISTINCT in your query, you also get the results you're looking for:

SELECT DISTINCT DCount("[Gender]","tblInfo","gender = 'male' ") AS M,
DCount("[Gender]","tblInfo","gender = 'female' ") AS F
FROM tblInfo, tblDetail
where tblInfo.ClientID = tblDetail.ClientID;

Suc6,

RV

Sorry, I left something out. Otherwise your suggestion would be correct.

I need to know the number of times that "Male" occurs AS WELL AS the number of times that "Female" occurs, where the ID numbers exist in both tables.

As I posted before:

If you put DISTINCT in your query, you also get the results you're looking for:

SELECT DISTINCT DCount("[Gender]","tblInfo","gender = 'male' ") AS M,
DCount("[Gender]","tblInfo","gender = 'female' ") AS F
FROM tblInfo, tblDetail
where tblInfo.ClientID = tblDetail.ClientID;

Suc6,

RV

SELECT tblInfo.Gender, Count(*) As GenderCount
FROM tblInfo Inner Join tblDetail
On tblInfo.ClientID = tblDetail.ClientID
Group By tblInfo.Gender;

Thanks for you help.

I tried your suggestion RV, but I couldn't get it to work (probably a typing error on my part).

I didn't get to Pat's code.

I don't know if it's the best way to do things... but I did get the answers I needed from:

SELECT DISTINCT Sum(IIf(gender="male",1,0)) AS Male, Sum(IIf(gender="female",1,0)) AS Female
FROM tblInfo, tblDetail
WHERE (((tblinfo.ClientID)=[tblDetail].[clientid]));

Thanks again.