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

    OutputTo Query help (xp)

    Hans helped me a few weeks ago with a way to pull together the responses to 11 questions and count the number of responses by groups. Now, I would like to take it a step farther and export the numbers to an Excel template that I have formulas in. I have never used the OutputTo function and am having quite a bit of trouble figuring it out. The query that I want to export is:
    SELECT q_nf_phone_courtesy.phone_courtesy, q_nf_phone_courtesy.Countofphone_courtesy, q_nf_refill_notice.Countofrefill_notice, q_nf_delivery_time.Countofdelivery_time, q_nf_correct_drug.Countofdrug_correct, q_nf_drug_condition.Countofdrug_condition, q_nf_supplies.Countofsupplies, q_nf_literature.Countofliterature, q_nf_RPh.Countofpharmacists, q_nf_nurses.Countofnurses, q_nf_customer_service.Countofcustomer_service, q_nf_overall_satisfaction.Countofoverall_satisfact ion, q_nf_total_surveys.SumOfCountofpostmark_date
    FROM q_nf_total_surveys, (((((((((q_nf_phone_courtesy INNER JOIN q_nf_refill_notice ON q_nf_phone_courtesy.phone_courtesy = q_nf_refill_notice.refill_notice) INNER JOIN q_nf_delivery_time ON q_nf_refill_notice.refill_notice = q_nf_delivery_time.delivery_time) INNER JOIN q_nf_correct_drug ON q_nf_delivery_time.delivery_time = q_nf_correct_drug.drug_correct) INNER JOIN q_nf_drug_condition ON q_nf_correct_drug.drug_correct = q_nf_drug_condition.drug_condition) INNER JOIN q_nf_supplies ON q_nf_drug_condition.drug_condition = q_nf_supplies.supplies) INNER JOIN q_nf_literature ON q_nf_supplies.supplies = q_nf_literature.literature) INNER JOIN q_nf_RPh ON q_nf_literature.literature = q_nf_RPh.pharmacists) INNER JOIN q_nf_nurses ON q_nf_RPh.pharmacists = q_nf_nurses.nurses) INNER JOIN q_nf_customer_service ON q_nf_nurses.nurses = q_nf_customer_service.customer_service) INNER JOIN q_nf_overall_satisfaction ON q_nf_customer_service.customer_service = q_nf_overall_satisfaction.overall_satisfaction;

    I tried and tried to figure out the OutputTo action but am failing miserably. Can someone explain to me if I have my arguments correct and just where does it go? I

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

    Re: OutputTo Query help (xp)

    If you want to do this in a macro, do this as follows:
    - Delete the OpenQuery action. You don't need it.
    - Specify the following arguments for the OutputTo action:
    Object Type: Query
    Object Name: q_nf_ratings_all
    Output Format: Microsoft Excel 97-2002
    Output File: leave empty to be prompted, fill in a complete path and file name otherwise
    Auto Start: Yes (to open the Excel workbook automatically) or No
    Template: leave empty; as is shown clearly in the macro window, this is only for HTML files.

    If you want to do this in code, it should be in the On Click event procedure for a command button on a form:

    Private Sub cmdExport_Click()
    DoCmd.OutputTo acOutputQuery, "q_nf_ratings_all", acFormatXLS, "C:WINDOWSDesktopcustomer_survey_template.xls"
    End Sub

    Since you haven't told us what q_nf_phone_courtesy_plancode is, I haven't the slightest idea about the second question. I don't see any parameter.

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

    Re: OutputTo Query help (xp)

    Hans, thanks for coming to my rescue yet again. And thanks for your patience. I had posted a few weeks ago about my parameter problems before adding the plan code. Your answer from post 380571:
    "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.

    Regards,
    Hans"
    So then when I decided to add the plan code to the query I thought I could just add it to the form parameters. The plan code is a 3 digit id number that identifies each patient's insurance code. So my form had txtStartDate, txtEndDate, PlanCode? but it only counts the number of Very Satisfied and Somewhat Satisfied answers.

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

    Re: OutputTo Query help (xp)

    I don't know why you get prompted for plancode three times; check carefully for typing errors.

    The reason that you only get counts for Very Satisfied and Somewhat Satisified must be that there are some categories that don'have any answer N/A or Dissatisfied. Instead of joining all queries on the first field, try the following:
    - If you don't have one yet, create a table with a field containing the response values (Very Satisifed etc.)
    - Create a query, add this table plus the q_nf_phone_courtesy_plancode etc. queries. Join all queries to the table, and make the joins into outer joins so that all records from the table are returned. Don't put joins between the queries, only between the table and each of the queries.

Posting Permissions

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