Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Note that there is no WHERE or HAVING.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •