Cartesian join in Access

[rjmccafferty1]

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).

 

[pjm]

Don't think you want a "cartesian join"/"cartesian product" because this will join every row in each table to every row in each other table. It's more an "absence of join"! Perhaps you want a full outer join? Access doesn't support this directly, but you can get it with:

 A LEFT OUTER JOIN B ON ...
UNION
B LEFT OUTER JOIN A ON ...

HTH
pjm

 

[rjmccafferty1]

Yes, I think I want a full outer join. I have two tables and want to see every record in every table, in both cases regardless of whether there is a matching record in the other table.

So do I understand correctly that I will essentially take the entire SQL code I posted but with a LEFT OUTER JOIN rather than INNER JOIN, the repeat the exact same code with a RIGHT OUTER JOIN this time, and between the two sets have a line that reads UNION?

 

[pjm]

That's it - except my apologies: omit the word OUTER. Syntax is just LEFT JOIN, RIGHT JOIN

So:

select tableA.colA, tableB.colB from tableA left join tableB on tableA.ID = tableB.ID
union
select tableA.colA, tableB.colB from tableA right join tableB on tableA.ID = tableB.ID

HTH

pjm