Prev: Update - Microsoft Responds to the Evolution of Community
Next: crosstab parameterized query
From: MrRJ on 10 May 2010 13:47
Hello,

I am a very basic user of MS Access. I love the functionality of it and
would love to learn more. Here is my situation.

I have started up a query that includes 3 tables. One main database(Table
A) and the other two are conversion tables(Tables B & C). The query works
with no issues. Now, I would like to add another major database(Table D). I
believe that I have linked up the joins correctly. However, I noticed that
it is only doing a match between Table A and Table D.

I would like to include ALL records from both Tables A and D. How do I do
that? I appreciate any feedback.

Have a nice day!
From: MrRJ on 10 May 2010 13:57
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;

Here is the SQL version...does this help?

"MrRJ" wrote:

> Hello,
>
> I am a very basic user of MS Access. I love the functionality of it and
> would love to learn more. Here is my situation.
>
> I have started up a query that includes 3 tables. One main database(Table
> A) and the other two are conversion tables(Tables B & C). The query works
> with no issues. Now, I would like to add another major database(Table D). I
> believe that I have linked up the joins correctly. However, I noticed that
> it is only doing a match between Table A and Table D.
>
> I would like to include ALL records from both Tables A and D. How do I do
> that? I appreciate any feedback.
>
> Have a nice day!
 | 
Pages: 1
Prev: Update - Microsoft Responds to the Evolution of Community
Next: crosstab parameterized query