Query Join - Outer Join?

Hello,

I need your assistance as I am a beginner with MS Access 2007. Please
review the SQL and hope it makes sense. I am trying to capture all records
from 2010 AOP even if there is none from SAP GL side. It seems to work with
SAP GL, but not all of the AOP side. What am I missing? Not sure how to
fix.

SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS
SumOfP4, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits] INNER JOIN (([SAP P4 GL] INNER JOIN [DC
Conversion] ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion] ON [SAP P4 GL].[COST ELEMENT] = [Category
conversion].[SAP GL]) ON ([2010 AOPII with Benefits].[SAP GL] = [Category
conversion].[SAP GL]) AND ([2010 AOPII with Benefits].Category = [Category
conversion].Category) AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], [2010 AOPII with
Benefits].P4;

You need to change from an INNER JOIN (only matching records on both sides) to
a LEFT JOIN or RIGHT JOIN (all records on the indicated side and any matching
records on the other side.

SELECT [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, Sum([SAP P4 GL].P4) AS SumOfP4
, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits]
LEFT JOIN (([SAP P4 GL]
LEFT JOIN [DC Conversion]
ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion]
ON [SAP P4 GL].[COST ELEMENT] = [Category conversion].[SAP GL])
ON ([2010 AOPII with Benefits].[SAP GL] = [Category conversion].[SAP GL])
AND ([2010 AOPII with Benefits].Category = [Category conversion].Category)
AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, [2010 AOPII with Benefits].P4;

You may get a message about breaking the query into two or more queries
because of incompatible joins.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

MrRJ wrote:
Hello,

I need your assistance as I am a beginner with MS Access 2007. Please
review the SQL and hope it makes sense. I am trying to capture all records
from 2010 AOP even if there is none from SAP GL side. It seems to work with
SAP GL, but not all of the AOP side. What am I missing? Not sure how to
fix.

SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS
SumOfP4, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits] INNER JOIN (([SAP P4 GL] INNER JOIN [DC
Conversion] ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion] ON [SAP P4 GL].[COST ELEMENT] = [Category
conversion].[SAP GL]) ON ([2010 AOPII with Benefits].[SAP GL] = [Category
conversion].[SAP GL]) AND ([2010 AOPII with Benefits].Category = [Category
conversion].Category) AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], [2010 AOPII with
Benefits].P4;

John,
Thank you for the quick reply. What I did what copied your SQL below and
override my existing SQL and I received an error.

Join Expression not supported

Not sure what that means?

MrRJ


"John Spencer" wrote:

You need to change from an INNER JOIN (only matching records on both sides) to
a LEFT JOIN or RIGHT JOIN (all records on the indicated side and any matching
records on the other side.

SELECT [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, Sum([SAP P4 GL].P4) AS SumOfP4
, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits]
LEFT JOIN (([SAP P4 GL]
LEFT JOIN [DC Conversion]
ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion]
ON [SAP P4 GL].[COST ELEMENT] = [Category conversion].[SAP GL])
ON ([2010 AOPII with Benefits].[SAP GL] = [Category conversion].[SAP GL])
AND ([2010 AOPII with Benefits].Category = [Category conversion].Category)
AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, [2010 AOPII with Benefits].P4;

You may get a message about breaking the query into two or more queries
because of incompatible joins.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

MrRJ wrote:
Hello,

I need your assistance as I am a beginner with MS Access 2007. Please
review the SQL and hope it makes sense. I am trying to capture all records
from 2010 AOP even if there is none from SAP GL side. It seems to work with
SAP GL, but not all of the AOP side. What am I missing? Not sure how to
fix.

SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS
SumOfP4, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits] INNER JOIN (([SAP P4 GL] INNER JOIN [DC
Conversion] ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion] ON [SAP P4 GL].[COST ELEMENT] = [Category
conversion].[SAP GL]) ON ([2010 AOPII with Benefits].[SAP GL] = [Category
conversion].[SAP GL]) AND ([2010 AOPII with Benefits].Category = [Category
conversion].Category) AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], [2010 AOPII with
Benefits].P4;

.

Try building the query a step at a time. You may need to use RIGHT JOINs and
not LEFT JOINs and you may need to drop the join between [2010 AOPII with
Benefits] and [DC Conversion]

SELECT [D].DC, [A.DC, [A].[SAP GL], [C].Category, [S].[COST ELEMENT],
[C].[Cost Element Name], Sum([S].P4) AS SumOfP4, [A].P4

FROM [2010 AOPII with Benefits] AS A
INNER JOIN (([SAP P4 GL] AS S
RIGHT JOIN [DC Conversion] AS D
ON S.[COST CENTER] = D.[SAP Cost Center])
RIGHT JOIN [Category conversion] AS C
ON S.[COST ELEMENT] = [C].[SAP GL])
ON A.[SAP GL] = [C].[SAP GL]
AND A.Category = [C].Category
AND A.DC = D.DC


GROUP BY [D].DC, [A.DC, [A].[SAP GL], [C].Category, [S].[COST ELEMENT],
[C].[Cost Element Name], [A].P4


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

MrRJ wrote:
John,
Thank you for the quick reply. What I did what copied your SQL below and
override my existing SQL and I received an error.

Join Expression not supported

Not sure what that means?

MrRJ


"John Spencer" wrote:

You need to change from an INNER JOIN (only matching records on both sides) to
a LEFT JOIN or RIGHT JOIN (all records on the indicated side and any matching
records on the other side.

SELECT [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, Sum([SAP P4 GL].P4) AS SumOfP4
, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits]
LEFT JOIN (([SAP P4 GL]
LEFT JOIN [DC Conversion]
ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion]
ON [SAP P4 GL].[COST ELEMENT] = [Category conversion].[SAP GL])
ON ([2010 AOPII with Benefits].[SAP GL] = [Category conversion].[SAP GL])
AND ([2010 AOPII with Benefits].Category = [Category conversion].Category)
AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, [2010 AOPII with Benefits].P4;

You may get a message about breaking the query into two or more queries
because of incompatible joins.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

MrRJ wrote:
Hello,

I need your assistance as I am a beginner with MS Access 2007. Please
review the SQL and hope it makes sense. I am trying to capture all records
from 2010 AOP even if there is none from SAP GL side. It seems to work with
SAP GL, but not all of the AOP side. What am I missing? Not sure how to
fix.

SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS
SumOfP4, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits] INNER JOIN (([SAP P4 GL] INNER JOIN [DC
Conversion] ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion] ON [SAP P4 GL].[COST ELEMENT] = [Category
conversion].[SAP GL]) ON ([2010 AOPII with Benefits].[SAP GL] = [Category
conversion].[SAP GL]) AND ([2010 AOPII with Benefits].Category = [Category
conversion].Category) AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], [2010 AOPII with
Benefits].P4;

.

John,
I hope you don't mind me asking this. When you say "building the query a
step at a time", what do you really mean? When I copied your SQL to mine, I
still have a problem. I very much would like to learn this and am a good
student. I know excel pretty well, but when it comes to access, not so good.

What approach should I take?
Leave the tables in the query and delete the joins. Then manually create a
join (right) as you stated below?

Just looking for some direction.

Your help is very much appreciated.

"John Spencer" wrote:

Try building the query a step at a time. You may need to use RIGHT JOINs and
not LEFT JOINs and you may need to drop the join between [2010 AOPII with
Benefits] and [DC Conversion]

SELECT [D].DC, [A.DC, [A].[SAP GL], [C].Category, [S].[COST ELEMENT],
[C].[Cost Element Name], Sum([S].P4) AS SumOfP4, [A].P4

FROM [2010 AOPII with Benefits] AS A
INNER JOIN (([SAP P4 GL] AS S
RIGHT JOIN [DC Conversion] AS D
ON S.[COST CENTER] = D.[SAP Cost Center])
RIGHT JOIN [Category conversion] AS C
ON S.[COST ELEMENT] = [C].[SAP GL])
ON A.[SAP GL] = [C].[SAP GL]
AND A.Category = [C].Category
AND A.DC = D.DC


GROUP BY [D].DC, [A.DC, [A].[SAP GL], [C].Category, [S].[COST ELEMENT],
[C].[Cost Element Name], [A].P4


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

MrRJ wrote:
John,
Thank you for the quick reply. What I did what copied your SQL below and
override my existing SQL and I received an error.

Join Expression not supported

Not sure what that means?

MrRJ


"John Spencer" wrote:

You need to change from an INNER JOIN (only matching records on both sides) to
a LEFT JOIN or RIGHT JOIN (all records on the indicated side and any matching
records on the other side.

SELECT [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, Sum([SAP P4 GL].P4) AS SumOfP4
, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits]
LEFT JOIN (([SAP P4 GL]
LEFT JOIN [DC Conversion]
ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion]
ON [SAP P4 GL].[COST ELEMENT] = [Category conversion].[SAP GL])
ON ([2010 AOPII with Benefits].[SAP GL] = [Category conversion].[SAP GL])
AND ([2010 AOPII with Benefits].Category = [Category conversion].Category)
AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC
, [2010 AOPII with Benefits].DC
, [2010 AOPII with Benefits].[SAP GL]
, [Category conversion].Category
, [SAP P4 GL].[COST ELEMENT]
, [Category conversion].[Cost Element Name]
, [2010 AOPII with Benefits].P4;

You may get a message about breaking the query into two or more queries
because of incompatible joins.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

MrRJ wrote:
Hello,

I need your assistance as I am a beginner with MS Access 2007. Please
review the SQL and hope it makes sense. I am trying to capture all records
from 2010 AOP even if there is none from SAP GL side. It seems to work with
SAP GL, but not all of the AOP side. What am I missing? Not sure how to
fix.

SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS
SumOfP4, [2010 AOPII with Benefits].P4
FROM [2010 AOPII with Benefits] INNER JOIN (([SAP P4 GL] INNER JOIN [DC
Conversion] ON [SAP P4 GL].[COST CENTER] = [DC Conversion].[SAP Cost Center])
INNER JOIN [Category conversion] ON [SAP P4 GL].[COST ELEMENT] = [Category
conversion].[SAP GL]) ON ([2010 AOPII with Benefits].[SAP GL] = [Category
conversion].[SAP GL]) AND ([2010 AOPII with Benefits].Category = [Category
conversion].Category) AND ([2010 AOPII with Benefits].DC = [DC Conversion].DC)
GROUP BY [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with
Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST
ELEMENT], [Category conversion].[Cost Element Name], [2010 AOPII with
Benefits].P4;

.

.

A possible solution can be like this:

Have a first query involving [2010 AOPII with Benefits] with the required
conversion tables (I assume they are the other tables except [SAP P4 GL] ).
Say it it saved under the name q1.

Have a second query involving [SAP P4 GL] with the required conversion
tables (ie, the tables you mentioned, except [2010 AOPII with benefits].
Make it q2.

Have a table with all the possible sap_gl values, or the following query

SELECT [SAP GL] AS sap_gl FROM [2010 AOPII with Benefits]
UNION
SELECT [SAP GL] FROM [SAP P$ GL]


make it q3.


Make a final query. Bring q3, q2 and q1. Join q3 and q2 trough the sap-gl
values, edit the join to keep all records from q3. Join q3 and q1 trough the
sap-gl values, edit the join again to keep all records from q3.

That should do it.


Note that the big difference with the query you pasted, is that each main
table has it OWN COPIES of the conversion table, while in the query you
pasted, they SHARE one single 'reference' to each conversion table, which
was creating some unwanted dependency, like John's pointed out.



We need to use multiple query, because Jet has no other syntax to indicate
what kind of join we want to be solved 'before' another. That is not a
problem if all the joins are inner join, but with outer joins involved, that
matters, some times, and we need nested query in these cases.



Vanderghast, Access MVP