Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Count Query (2000)

    I have a query with four fields that get their values from an option frame. The fields are called Ques1, Ques2, Ques5 & Ques6. Ques1 & Quest 2 has 5 options with values assigned from 1
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Query (2000)

    Now you're discovering why we don't recommend the structure you used, with all the questions in a flat record. I can't really think of an effective way to do what you're asking with a single query. However, you could create a temporary table in the recommended structure of one record per question/response and append the records from your base table. Each record would have a field for question (ques1, etc.), any identifier that indicates which response/client/whatever, this question/answer belongs to, and a field for the value. Make the combination of question field and identifier(s) the primary key. Then you can use a crosstab query with column heads for each question and rows to return the count by value.
    Charlotte

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

    Re: Count Query (2000)

    Start by creating four separate queries to return the frequency of replies for each question. Say your table is named tblQues. The query for Ques1 would have SQL:

    SELECT Ques1, Count(*) AS CountOfQues1
    FROM tblQues
    GROUP BY Ques1;

    Let's say you save this as qryQues1. Similarly, create qryQues2, qryQues5 and qryQues6.
    Next, create a table with a number field Opt, and enter 6 records, with values 1, 2, ..., 6 in the Opt field. This table, named tblOpt, represents all possible options.
    Finally, create a query based on tblOpt and qryQues1, ..., qryQues6. Create a left join between tblOpt and each of the queries on Opt vs Ques1 etc. We want the query to display ALL records from tblOpt and only related records from the queries. Add the Opt field from the table, and the CountOf... field from each of the queries to the grid. The SQL is

    SELECT tblOpt.Opt, qryQues1.CountOfQues1, qryQues2.CountOfQues2, qryQues5.CountOfQues5, qryQues6.CountOfQues6
    FROM (((tblOpt LEFT JOIN qryQues1 ON tblOpt.Opt = qryQues1.Ques1) LEFT JOIN qryQues2 ON tblOpt.Opt = qryQues2.Ques2) LEFT JOIN qryQues5 ON tblOpt.Opt = qryQues5.Ques5) LEFT JOIN qryQues6 ON tblOpt.Opt = qryQues6.Ques6;

    See attached example (Acc 97 zipped to keep size down)
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count Query (2000)

    Thank you Charlotte for your sage recommendation and admonishment. I realized that once I started having problems, but I was too far down the road to turn back. In the future you can bet I won't do that again.
    Easy Access

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count Query (2000)

    Hans,
    I bow to your superior knowledge. <img src=/S/bow.gif border=0 alt=bow width=15 height=15> Once again you have saved me from dispair and frustration. Your suggestion provided a quick fix and got me out of a jam. Thank you for the quick response.
    Easy Access

Posting Permissions

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