Count Query using a switch function

[BettyWho]

Hi

I'm having a small issue with the below code and I have noidea what I am doing wrong. It keeps telling me I have a syntax error.

Can anyone shed some light on what I am doing wrong.:banghead:

I am trying to count the different genders over a selected period of time and I want to change anything from F to Female and count all those with f or Female as one group and likewise for M to Male and all Male as one group

Thanks in advance.

select count(ClientID) as ClientCount, GenderGroup
from
(
select distinct
ClientID,
switch
(
Gender" = F, "Female",
Gender = M, "Male"
) as GenderGroup
from
(
select tclientinfo.ClientID, tclientinfo.Gender, tclientservices.[Service Date]
from TClientServices , TClientInfo
where tclientservices.ClientID = tclientinfo.ClientID
and (StartDate <= [Service Date] and [Service Date] <= EndDate)
) C
)
group by GenderGroup

 

[sneuberg]

The Switch function doesn't look right to me. It has at least one excess double quote, but I think the expression need to have quotes around the value in them. I'd try.

Switch
(
[Gender] = 'F', "Female",
[Gender] = 'M', "Male"
) as GenderGroup

You probably don't need brackets around Gender but I put them in to make it clear that it's a field.

 

[sneuberg]

It would be better if you could avoid these subqueries. They can be really slow. If you could upload your database so that we could see the structure maybe we could give you some alternatives to this query.

 

[BettyWho]

Hi Steve

The suggestion you gave unfortunately did not work it still tells me I have a syntax error on the from clause. I am unable to upload the database due to the sensitive nature of the contents. But any assist that can be offered is much appreciated.

Thanks again

 

[sneuberg]

I've attached a database which shows a different approach. The main query qryClientGenders in this replaces the inner most subquery in your approach. The SQL for this is:

SELECT tclientinfo.ClientID, tclientinfo.Gender, Switch([Gender]='F',"Female",[Gender]='M',"Male") AS GenderGroup
FROM tclientinfo INNER JOIN tclientservices ON tclientinfo.ClientID = tclientservices.ClientID
WHERE (((tclientservices.[Service Date]) Between [forms]![frmSelectDates]![StartDate] And [forms]![frmSelectDates]![EndDate]));

The differences are:

1. The table are joined rather then being matched in the WHERE clause
2. I added a form to provide the start and end dates to make it easier to test hence the form references
3. I moved the corrected Switch function inside this query.
4. A Between clause is used for the date selection
   

This query is used in the aggregate query qryCountGender which provides the counts. The SQL for this is:

SELECT qryClientGenders.GenderGroup, Count(qryClientGenders.ClientID) AS CountOfClientID
FROM qryClientGenders
GROUP BY qryClientGenders.GenderGroup;

This makes it simplier and no subqueries are required.