Cartesian join in Access

[rmccafferty]

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

 

[SimonB1978]

Hi,

A cartesian JOIN can be done with this syntax:

FROM Qry_Sub2_TotalCommPdByCustID, Qry_Sub2_TotalInvByCustID

Remember that this will give you EVERY possible combination of query_1 and query_2, so num_row_qry_1 X num_row_qry_2. Is this really what you want?

Simon B.

 

[Atomic Shrimp]

If there is a possibility that a customer's records may be missing from one table/query or the other, then surely there is the possiblity that the customer's record might be missing from both - in which case no kind of join between the two recordsets is going to show anything for that customer.

I think what's needed here is to start with the customers table and left join out to each of the other recordsets, then find unmatched in one OR unmatched in the other OR one <> the other.