Combine data from 3 different tables

[udigold1]

Hi, I have 3 tables (Table1 to Table3 in the following example), and I want to combine that with a sql statement so it would give a result shown in "output" (to be shown on datagrid, for example)

Table1_Customer
C_ID C_Name
1    James
2    Mike
3    John

Table2_DataType
D_ID D_Name
1     Deposit
2     Withdraw
3     Yield

Table3_Numbers
O_ID C_ID N_value
1      1     10000
1      1     1500 
2      1     450
3      1     1.5
3      2     2.2
3      3     0.4
1      2     10000
1      2     2500 
2      2     450
1      3     11000

Output:
C_Name Deposit Withdraw Yield
James   11500   450     1.5
Mike    12500   450     2.2
John    11000   0       0.4

I need the final output to show just one unique line for each name, and not repeating lines, and also to sum data.

Thanks,

Udi

 

[jasmith4]

TRANSFORM Sum(Table3_Numbers.[N_value]) AS SumOfN_value
SELECT Table1_Customer.C_Name
FROM Table2_DataType
INNER JOIN
(   Table1_Customer
    INNER JOIN Table3_Numbers
    ON Table1_Customer.C_ID = Table3_Numbers.C_ID
) ON Table2_DataType.D_ID = Table3_Numbers.D_ID
GROUP BY Table3_Numbers.C_ID, Table1_Customer.C_Name
ORDER BY Table3_Numbers.C_ID
PIVOT Table2_DataType.D_Name;

Paste that into a new query's SQL view, then go to Design view to see how you could have done it with the Query-by-Example interface.

 

[udigold1]

Hi,

Thanks for the answer!
If I may ask another two related questions:
1. What happens if there's a fourth table, like in the following example?
2. Is there a way to add another column to the output that show Net deposits (deposits - withdraw)?


Thanks again!

Table1_Customer
C_ID C_Name
1    James
2    Mike
3    John

Table2_DataType
D_ID D_Name
1     Deposit
2     Withdraw
3     Yield


Table21_DataBankType
DB_ID DB_Name               D_ID 
41    Deposit from Bank A   1 
42    Deposit from Bank B   1
43    Withdraw from Bank A  2
44    Withdraw from Bank B  2
45    Yield                 3


Table3_Numbers
DB_ID C_ID N_value
41      1     10000
42      1     1500 
44      1     450
45      1     1.5
45      2     2.2
45      3     0.4
42      2     10000
41      2     2500 
43      2     450
42      3     11000

Output:
C_Name Deposit Withdraw Yield
James   11500   450     1.5
Mike    12500   450     2.2
John    11000   0       0.4

Thanks

 

[jasmith4]

Don't do it that way. Your fourth table should be just banks: bank A and bank B. Then your Numbers table would have a new bank-ID column, so join that with your new banks table in Design mode.