Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    37

    Unanswered: two queries into one report

    Combining two queries (for report)
    I have this query which give me the total number of cases for each worker:

    SELECT COUNT([A].[CASE_ID]) AS [OPEN CASES], [A].[IV_D_DO_CODE], [A].[WORKER_ID]
    FROM A
    GROUP BY [A].[IV_D_DO_CODE], [A].[WORKER_ID]
    ORDER BY [A].[IV_D_DO_CODE], [A].[WORKER_ID];

    I also have this query which gives me the total collections for a selected month and year for each worker. (not every worker with cases has any collections during this time period)

    SELECT [COLLECTIONS -- AT CASE LEVEL--PART ONE].[IV_D_DO_CODE], [COLLECTIONS -- AT CASE LEVEL--PART ONE].[WORKER_ID], [COLLECTIONS -- AT CASE LEVEL--PART ONE].[ID_CASE], SUM([COLLECTIONS -- AT CASE LEVEL--PART ONE].[NAA]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[PAA]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[TAA]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[CAA]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[UPA]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[UDA]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[IVEF]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[MEDI]+[COLLECTIONS -- AT CASE LEVEL--PART ONE].[FUTURE]) AS [TOTAL COLLECTED]
    FROM [COLLECTIONS -- AT CASE LEVEL--PART ONE]
    GROUP BY [COLLECTIONS -- AT CASE LEVEL--PART ONE].[IV_D_DO_CODE], [COLLECTIONS -- AT CASE LEVEL--PART ONE].[WORKER_ID], [COLLECTIONS -- AT CASE LEVEL--PART ONE].[ID_CASE];


    The problem is, everything that I try to combine these two queries doesn't give me the results that I want. I want to see all the workers listed in the report even if they don't have collections for that month, it that case the collections field would be empty but the case count would be there.

    I got to the point where I ended up with several lines which were only showing case counts, but the worker id field was empty. Further down in the report I could see the rest of the workers along with their collections and case count.... what am I doing wrong ?

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You could use an outer join kind of like this:

    select Query1.[Open Cases], Query1.IV_D_DO_CODE, Query1.WORKER_ID, Query2.[TOTAL COLLECTED]
    from Query1 left join Query2 on Query1.Worker_ID = Query2.Worker_ID

Posting Permissions

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