Can't Join two Access 97 queries to include all the records from both

[gosling]

This is the query in Access 97 that is not returning
all the records required.

How can I include a record even if it is not in the
Left Query but is in the Right Query. Please note that I
need everything in the Left Query.


SELECT CStr(Qry_Total_Actual_AY.Job_Number) AS Job_Number,
Qry_Total_Actual_AY.Financial_Year AS Financial_Year,
Job_Number & "YR" & Financial_Year AS JobYear,
Qry_Total_Actual_AY.Total,
Qry_Total_Commit_AY.Commit_For_Period,
Qry_Total_Actual_AY.Total+IIf(IsNull(Qry_Total_Commit_AY.Commit_For_Period),0,Qry_Total_Commit_AY.Commit_For_Period) AS Total_AC,
Qry_Total_Actual_AY.ActualPeriod1 AS ActualPeriod1,
Qry_Total_Actual_AY.ActualPeriod2 AS ActualPeriod2,
Qry_Total_Actual_AY.ActualPeriod3 AS ActualPeriod3,
Qry_Total_Actual_AY.ActualPeriod4 AS ActualPeriod4,
Qry_Total_Actual_AY.ActualPeriod5 AS ActualPeriod5,
Qry_Total_Actual_AY.ActualPeriod6 AS ActualPeriod6,
Qry_Total_Actual_AY.ActualPeriod7 AS ActualPeriod7,
Qry_Total_Actual_AY.ActualPeriod8 AS ActualPeriod8,
Qry_Total_Actual_AY.ActualPeriod9 AS ActualPeriod9,
Qry_Total_Actual_AY.ActualPeriod10 AS ActualPeriod10,
Qry_Total_Actual_AY.ActualPeriod11 AS ActualPeriod11,
Qry_Total_Actual_AY.ActualPeriod12 AS ActualPeriod12
FROM Qry_Total_Actual_AY LEFT JOIN Qry_Total_Commit_AY ON Qry_Total_Actual_AY.JobYear = Qry_Total_Commit_AY.JobYear;

A Sample of the Output Resultset Follows:

Query Name Job_Number JobYear
Qry_Total_Commit_AY 9052571100 2003
9052571300 2003

Query Name Job_Number JobYear
Qry_Total_Actual_AY 9052571100 2003
9052571400 2003

Query Name Job_Number JobYear
Qry_Total_Actual_Commit_AY 9052571100 2003
9052571400 2003
This last record should be here 9052571300 2003
However it is excluded because
of the LEFT JOIN.

 

[scriverb]

I think a UNION query using the DISTINCT operator should give you what you want. Here is an example that you can expand on.

SELECT DISTINCT CStr(A.Job_Number) AS Job_Number,
A.Financial_Year AS Financial_Year,
Job_Number & "YR" & Financial_Year AS JobYear,
A.Total,
B.Commit_For_Period,
A.Total+IIf(IsNull(B.Commit_For_Period),0,B.Commit_For_Period) AS Total_AC,
A.ActualPeriod1 AS ActualPeriod1,
A.ActualPeriod2 AS ActualPeriod2,
A.ActualPeriod3 AS ActualPeriod3,
A.ActualPeriod4 AS ActualPeriod4,
A.ActualPeriod5 AS ActualPeriod5,
A.ActualPeriod6 AS ActualPeriod6,
A.ActualPeriod7 AS ActualPeriod7,
A.ActualPeriod8 AS ActualPeriod8,
A.ActualPeriod9 AS ActualPeriod9,
A.ActualPeriod10 AS ActualPeriod10,
A.ActualPeriod11 AS ActualPeriod11,
A.ActualPeriod12 AS ActualPeriod12
FROM Qry_Total_Actual_AY as A LEFT JOIN Qry_Total_Commit_AY as B ON A.JobYear = B.JobYear;
UNION
SELECT CStr(B.Job_Number) AS Job_Number,
B.Financial_Year AS Financial_Year,
Job_Number & "YR" & Financial_Year AS JobYear,
B.Total,
A.Commit_For_Period,
B.Total+IIf(IsNull(A.Commit_For_Period),0,A.Commit_For_Period) AS Total_AC,
B.ActualPeriod1 AS ActualPeriod1,
B.ActualPeriod2 AS ActualPeriod2,
B.ActualPeriod3 AS ActualPeriod3,
B.ActualPeriod4 AS ActualPeriod4,
B.ActualPeriod5 AS ActualPeriod5,
B.ActualPeriod6 AS ActualPeriod6,
B.ActualPeriod7 AS ActualPeriod7,
B.ActualPeriod8 AS ActualPeriod8,
B.ActualPeriod9 AS ActualPeriod9,
B.ActualPeriod10 AS ActualPeriod10,
B.ActualPeriod11 AS ActualPeriod11,
B.ActualPeriod12 AS ActualPeriod12
FROM Qry_Total_Commit_AY as A LEFT JOIN Qry_Total_Actual_AY as B ON A.JobYear = B.JobYear;

I used the A and B alias to make it easier to keep the queries straight. This union query will provide records from both queries that are unique as well as those that match. The matches will only show up as one record due to the DISTINCT predicate.

Let me know how this works as I was not able to test the code.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gosling]

This suggestion produced an Error Message "Invalid Use Of Null"

 

[scriverb]

Run the two selects seperate to see which one is causing the problem. I suspect the second one. If it is then the following statement with the CStr function is referencing a null value on the B table side of the join. I am assuming that Job_Number is also on the A side so you can use that reference:
SELECT CStr(A.Job_Number) AS Job_Number,. .

The above is the Select statement just after the UNION statement.

You have to make sure that the Job_Number is a valid field in the query Qry_Total_Commit_AY


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???