I have an application where I need to return all records from two tables (actually, predecessor queries).

My goal is to see the difference between two different calculations of what should have been the same data. But in some cases, the data was not calculated in one or the other of the two sources. So I need to see every record from every record in either predecessor query. A non entry is one of the data sets is just another error, just as if a wrong amount were entered.

I cannot figure out how to write this query. The SQL for my query if I use an inner join is:

SELECT DISTINCTROW Qry_Sub2_TotalInvByCustID.SumOfInvoiceCommisions, Qry_Sub2_TotalCommPdByCustID.SumOfCommPd, Qry_Sub2_TotalCommPdByCustID.[CUSTOMER ID], ([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd]-[Qry_Sub2_TotalInvByCustID].[SumOfInvoiceCommisions])/[Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd] AS CommVariance
FROM Qry_Sub2_TotalCommPdByCustID INNER JOIN Qry_Sub2_TotalInvByCustID ON Qry_Sub2_TotalCommPdByCustID.[CUSTOMER ID] = Qry_Sub2_TotalInvByCustID.CustID
ORDER BY ([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd]-[Qry_Sub2_TotalInvByCustID].[SumOfInvoiceCommisions])/[Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd] DESC;


Neither a right nor a left join solves the entire problem and I really don't want top have to fun an inner join, a left join and a right join and then combine all those responses. What I really need is a cartesian join, but don't have a clue how to write it for Access.

Yes, there are some lengthy phrases for computations involved, but they are quite simple calculation in concept. That should not have any impact on what I need. I just need to know how to write any query, no matter how simple, that includes all records from both tables (or predecessor queries).