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

    query on multiple tables (XP)

    I have a query that I built to pull information for a report. I have four tables (T_Courier, T_Billing, T_Disp, and T_OutofStock). I built a query for each table that lists each error. I have inserted the query for one of them below:

    SELECT Count(T_Courier.RNA_ID) AS CountOfRNA_ID, T_Courier.ErrorByCourier, T_Courier.Courier, T_Patients.ErrorNotificationDate
    FROM T_Patients RIGHT JOIN T_Courier ON T_Patients.RNA_ID = T_Courier.RNA_ID
    GROUP BY T_Courier.ErrorByCourier, T_Courier.Courier, T_Patients.ErrorNotificationDate
    HAVING (((T_Patients.ErrorNotificationDate) Between [startdate] And [enddate]));


    Then I had a query for each of these that added the sum of the errors from the query above:

    SELECT Sum(Q_CourierErrorCount.CountOfRNA_ID) AS SumOfCountOfRNA_ID
    FROM Q_CourierErrorCount;

    I then put each of these queries together to try to pull all the sums into one query:

    SELECT [Q_BillingErrorSum].[SumOfCountOfErrorNotificationDate], [Q_CourierErrorSum].[SumOfCountOfRNA_ID], [Q_DispErrorSum].[SumOfCountOfRNA_ID], [Q_ErrorOutofStockSum].[SumOfCountOfRNA_ID]
    FROM Q_BillingErrorSum, Q_CourierErrorSum, Q_DispErrorSum, Q_ErrorOutofStockSum;


    On the query, the numbers come out fine. But when I used this query to build a report, the DispError and CourierError flipflop sometimes. It's not every time that this happens. Am I going to need to do a separate report for each type of error?

    Laura

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

    Re: query on multiple tables (XP)

    Do you mean that two non-empty values change places? That seems hard to believe.

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

    Re: query on multiple tables (XP)

    Problem solved! One of the employees had simply made a copy and didn't relink the tables on the database. It is split and shared so she was working off the version where I hadn't corrected the query. If only they would listen.

Posting Permissions

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