# Thread: Multiple calculated fields in a query (2000 SP3)

1. ## Multiple calculated fields in a query (2000 SP3)

Using Access 2000sp3

I have a table of surbey question answers in table form and I'm trying to manipulate them to make it easier/possible/better for the users to do some analysis and make some graphs and tables and whatnot.

Of course, I'm stuck on the first operation (This is always the case with me and Access, I'm afraid :-( ).

Question 1, gender, is stored in field [Q1]. The values are "1" for male, "2" for female, and "something else" for "question not answered.

I want to find the total cound of male and femal records.

So I make a calculated field in my Query:

Field: Male:Q1
Total: Count
Criteria: [Q1]="1"

Is what worked for me. However, when I tried to add a second calculated field i.e:

Field: Femal:Q1
Total: Count
Criteria: [Q1]="2"

I get no result. When I look at the SQL statement:
HAVING (([q1]="1") AND ([q1]="2"));

Which is obviously not what I wanted. Is there a way to make multiple unrelated calculated fields in a query, or is it strictly "one question per query" ?

Confused and Stifiled in Pittsburgh
Jim

2. ## Re: Multiple calculated fields in a query (2000 SP3)

You can't do this with two different criteria, as you found. Instead, use this trick:

Male: Abs(Sum([q1]=1))

Female: Abs(Sum([q1]=2))

This uses the fact that [q1]=1 evaluates to True = -1 or False = 0. By summing these values and taking the absolute value to get rid of the minus, you get the count. The SQL is:

SELECT Abs(Sum([q1]=1)) As Male, Abs(Sum([q1]=2)) As Female

Note that there is no WHERE or HAVING.

3. ## Re: Multiple calculated fields in a query (2000 SP3)

Thanks Hans,

That puzzled me for a bit, but I managed to figure it out.

I could swear what I was trying to do with an SQL command. But, I'm not really game to try and fight Access if it doesn't want to do something.

Thanks
Jim

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•