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

    Report from 3 tables (XP)

    I am trying to create a report to show all the errors within a month. I tried creating a query that includes all three tables but only the patients who have all three errors show up. I want them even if they have one. Billing and Dispensing Errors are stored in T_Patients. Out of Stock Drugs are stored in T_Out_of_Stock and Courier Errors are stored in T_Courier. Courier and out of stock errors can be more than one mistake in a shipment so I had to put subforms on my main forms to let them grow but I need a way to get them all together for reporting purposes. Will I have to run three different reports?

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

    Re: Report from 3 tables (XP)

    If some of the tables can contain more than one error record per patient, data from the other tables is going to be repeated in a query based on all three tables. I don't think that that is desirable (correct me if I'm wrong.)

    I would start by creating a query that returns all patients with at least one error record:
    - Add the three tables to the query window.
    - Join T_Patients to the other two on the patient ID field (assuming you have a unique ID.)
    - Make the joins into left joins (return ALL records from T_Patients). the joins will change from lines to arrows.
    - Add the Patient ID field, plus standard patient info from T_Patients to the query grid.
    - Add a field from each of the three tables that indicates whether there is an error. I am assuming that there is an error if the contents of these fields is non-empty.
    - Set the criteria for these three fields to Is Not Null, on a different line for each.
    - Test the query by switching to datasheet view; you should see all records for which at least one of the tables contains an error.
    - Switch back to design view.
    - Clear the Show check box for the three criteria fields.
    - Set the Unique Values property of the query to Yes.
    - Test the query again. It should now return each patient once for which an error exists.

    Use this query as record source for the report. Display the errors in subreports, linked to the main report by PatientID.

  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 from 3 tables (XP)

    Thanks, Hans. Worked like a charm.

Posting Permissions

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