Multiple calculated fields in a query (2000 SP3)

Topic

New Reply

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
Table:Answers0
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
Table:Answers0
Total: Count
Criteria: [Q1]=”2″

I get no result. When I look at the SQL statement:
SELECT Count(Answers0.Q1) AS Male, Count(Answers0.Q1) AS Female
FROM Answers0
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

Reply | Quote

Replies

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
FROM Answers0

Note that there is no WHERE or HAVING.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
FROM Answers0

Note that there is no WHERE or HAVING.

Reply | Quote