Please Help! Simple Query Problem

[Snoopy]

Hello! I am trying to execute a query in Access but keep getting the following error message :

"Data Type Mismatch in Criteria Expression"

I have double-checked that the expressions are typed correctly and am certain that they are, here is the query SQL:

SELECT [About You].Residence, Count([About You].Gender) AS CountOfGender, Count([About You].Age) AS CountOfAge
FROM (([About You] INNER JOIN Arrival ON [About You].[Student ID] = Arrival.[Student ID]) INNER JOIN [Experience in residence] ON (Arrival.[Student ID] = [Experience in residence].[Student ID]) AND ([About You].[Student ID] = [Experience in residence].[Student ID])) INNER JOIN [Satisfaction of facilities and services] ON ([Experience in residence].[Student ID] = [Satisfaction of facilities and services].[Student ID]) AND ([About You].[Student ID] = [Satisfaction of facilities and services].[Student ID])
GROUP BY [About You].Residence
HAVING (((Count([About You].Gender))="Male") AND ((Count([About You].Age))="Under  20 years of age"));

Please help if you can

 

[FoFa]

In your having clause, you are specifing COUNT but supplying a string. Count is numeric, I suspect you really don't want COUNT wrapped around those checks

 

[Snoopy]

Thank you for your advice! Do you have any recommendations on how I may get around this? I would be extremely grateful!

 

[KeithG]

Take the Count out of the having clause.

HAVING (([About You].Gender)="Male") AND ([About You].Age)="Under 20 years of age");

 

[Snoopy]

Thank you, I just tried that but got this error message:

"You tried to execute a query that does not include the specified expression '[About You].Gender="Male" And [About You].Age="Under 20 years of age" as part of an aggregate function."

I presume its kicking up a fuss because I've included count at the start, but I need count to give the total of records for people of a male gender and people aged under 20 years old. Yikes! Any ideas?

 

[FoFa]

You have to include all columns that are not agregate values in your GROUP BY clause, which is basically what that error is saying.

 

[Snoopy]

Re

Thank you, how would I do that?

 

[FoFa]

Sorry, HAVING deals with agregate values, WHERE does not. Your criteria needs to be in a WHRE clause instead of a HAVING clause. Change the HAVING to WHERE and move it before the GROUP BY

 

[Snoopy]

Re

Thankyou, I've tried that and changed the SQL, so that it now reads:

SELECT [About You].Residence, Count([About You].Gender) AS CountOfGender, Count([About You].Age) AS CountOfAge
FROM (([About You] INNER JOIN Arrival ON [About You].[Student ID] = Arrival.[Student ID]) INNER JOIN [Experience in residence] ON (Arrival.[Student ID] = [Experience in residence].[Student ID]) AND ([About You].[Student ID] = [Experience in residence].[Student ID])) INNER JOIN [Satisfaction of facilities and services] ON ([Experience in residence].[Student ID] = [Satisfaction of facilities and services].[Student ID]) AND ([About You].[Student ID] = [Satisfaction of facilities and services].[Student ID])
WHERE (((Count([About You].Gender))="Male"))
GROUP BY [About You].Residence;

Unfortunately I now get the message:

"Cannot have aggregate function in WHERE clause (Count([About You].Gender)="Male"). "

Is there another way I can do this that anyone can think of?

 

[FoFa]

Change it from (((Count([About You].Gender))="Male")) to ((([About You].Gender))="Male")