Query Join - Outer Join?

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

Start with TWO tables and join them. See if the query runs and returns data.

If so, add another table and join it. etc.

There is no direct connection between
[2010 AOPII with Benefits] and [SAP P4 GL]


I did notice that you join both
[2010 AOPII with Benefits] and [SAP P4 GL] to [DC Conversion] although on
different fields. You might try adding the DC Conversion table to the query a
second time and setting up the link between [SAP P4 GL] and the second
instance instead of linking both tables to the same instance.

You other choice might be to build a query with
[SAP P4 GL] and [DC Conversion] and [Category conversion]

A second query with
[2010 AOPII with Benefits] and [Category conversion] and [DC Conversion]

And then Join those two queries together. You will need to be sure to include
the fields in the two above queries that you need for linking the two queries
together. Which seems to be fields in the joining tables Category Conversion

You can change the join types by double-clicking on the join line between
tables and selecting various options.

Hope this helps.

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

MrRJ wrote:
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