Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    report & query help (XP)

    I have a table that contains 10 different categories that can have the answers Very Satisfied, Somewhat Satisfied, Dissatisfied or N/A. I want to put the total number of each response per category into a report but I can't figure out how to get them there. I was thinking one query per category but then I don't know how to get them to the report. I also need to put a total for each category.
    I came up with this query just for one category but you have to enter the date range 4 times. Can anyone give me a clue what I'm doing wrong?

    SELECT Q_nf_telephone_courtesy.phone_courtesy, Count(Q_nf_telephone_courtesy.phone_courtesy) AS CountOfphone_courtesy
    FROM Q_nf_telephone_courtesy
    GROUP BY Q_nf_telephone_courtesy.phone_courtesy;

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

    Re: report & query help (XP)

    Since you give us no clue where the date range comes into this, how should we know?

    I get the impression that your table contains 10 different fields, each of which can contain Very Satisfied etc. If that is correct, it is unfortunate. This design makes tallying results difficult. It would be better to use one category field (that can have 10 values) and one answer field. You could then create a crosstab query to tally category vs answer, and base a report on that query.

  3. #3
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report & query help (XP)

    Came up with a better query while I was thinking, see if it makes more sense:
    SELECT nf_customer.phone_courtesy, Count(nf_customer.phone_courtesy) AS CountOfphone_courtesy
    FROM nf_customer
    WHERE (((nf_customer.postmark_date) Between [START DATE] And [END DATE]))
    GROUP BY nf_customer.phone_courtesy;

    I have fields for phone courtesy, refill notice, delivery time, etc.

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

    Re: report & query help (XP)

    If you create 10 such queries, one for each category, you'd have to enter the date parameters 10 times. To make it more user-friendly, create a form frmParameters, with text boxes txtStartDate and txtEndDate. Set their Format property to a date format. Refer to the text boxes in the query:

    SELECT phone_courtesy, Count(*) AS CountOfphone_courtesy
    FROM nf_customer
    WHERE postmark_date Between [Forms]![frmParameters]![txtStartDate] And [Forms]![frmParameters]![txtEndDate]
    GROUP BY phone_courtesy;

    Once you have 10 such queries, create one query to rule them all and in the darkness ... oops, wrong forum. One query to combine them all - join them on the first field.

Posting Permissions

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