Solved Union query to combine outer join with a WHERE criteria?

[CupKat]

Hello Access World Forum!

I have two queries I’m trying to combine into one, but I’m having some trouble.

q1 summarises days worked per contractor per billing month as per the information provided by the client.

q2 summarises days worked per contractor per billing month as per the information provided by the contractor.

q3 is meant to compare days worked per month between q1 and q2. Where there is information for the given month in both queries, it works fine. Where records exist for a contractor in q1 but without any matching records in q2, it also appears to work fine and they’re still included in the query but with a null value in the q2 fields. What it fails to do is where there are multiple records for contractor1 in q1, but for instance only one matching record in q2. In this case it appears to only return the single matching record, but not the rest of the records in q1 that do not have any data for that month in q2, if this makes sense.

Even if I got this working as I was hoping, I suppose the best case scenario would still only be getting all the records from q1 including those with no matching q2 records, but not the other way around. I’m wondering if I should be using a union query, but I’m not entirely sure how to do this.

(I used the query design function to make the query)


SELECT [q1].Full_Name, [q1].MyMonth,

[q2].MyMonth, [q1].SumOfDays_Worked,

[q2].SumOfDays_Worked, (([q1].[SumOfDays_Worked])-Nz(([q2].[SumOfDays_Worked]),0)) AS DaysWorkedDiff

FROM q1 LEFT JOIN q2 ON [q1].Full_Name = [q2].Full_Name

GROUP BY [q1].Full_Name, [q1].MyMonth, [q2].MyMonth, [q1].SumOfDays_Worked, [q2].SumOfDays_Worked, (([q1].[SumOfDays_Worked])-Nz(([q2].[SumOfDays_Worked]),0))

HAVING ((([q2].MyMonth)=[q1].[MyMonth] Or ([q2].MyMonth) Is Null));

Any advice would be much appreciated x

 

[plog]

Oftentimes a UNION query is a hack around a poor table set up or lack of querying skills. Not 100% certain, but I am seeing red flags in your post.

To really help you I need to see what you are starting with and what you hope to end up with. So please provide me 2 sets of data:

A. Starting data from your tables. Include table and field names and enough data to cover al cases.

B. Expected results of A. Show me what data you expect to end up with when you feed in the data from A.

Again, 2 sets of data (not words, data) to demonstrate what you have and where you hope to end up based on what you have.

 

[ebs17]

You need an OUTER JOIN related to a full amount.
The full amount is generated from the table of clients and a monthly table (auxiliary table over a sufficient period of time).

Principle:

SELECT
   C.Full_Name,
   M.MyMonth
FROM
   tblClients AS C,
   tblMonth AS M

SELECT
   [FieldList]
FROM
   (Query1 AS q0
      LEFT JOIN q1
      ON q0.Full_Name = q1.Full_Name
         AND
      q0.MyMonth = q1.MyMonth
   )
   LEFT JOIN q2
   ON q0.Full_Name = q2.Full_Name
      AND
   q0.MyMonth = q2.MyMonth