If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Query Join - Outer Join?
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 |
Thread Tools | |
Display Modes | |
|
|