Join Three tables with Sum of column in access query

Question

I have Three tables as shown below.. I need output as shown in output table for this i need to join three tables and order output in month order

tbl_MonthList

MonthID   MonthList
1         January
2         February
3         March
4         April
5         May
6         June
7         July
8         August
9         September
10        October
11        November
12        December

tbl_Amount:

Month_id     Amount_Received     Customer_id
3             500                 aaa
3            1000                 bbb
4             700                 jjj
5             300                 aaa
5             400                 jjj
5             500                 ppp
7            1000                 aaa
10           1500                 bbb
12            700                 jjj

tbl_Month_Target

MonthID     MonthF_L
1             10000
2            150000
3              1000
4             50000
5              5000
6              3000
7             20000
8             12000
9             34000
10            85000
11            34000
12            45000

I need output as shown below

Month     Total_amount     MonthF_L
January           0          10000
February          0         150000
March          2000           1000
April           700          50000
May            1200           5000
June              0           3000
July           1000          20000
August            0          12000
September         0          34000
October        1500          85000
November          0          34000
December        700          45000

GIVE ME TEH CODEZ! I don't want to work! – Johnny Bones Aug 29 '13 at 15:30 — Johnny Bones, Aug 29 '13 at 15:30

score 3 · Answer 1 · edited May 23 '17 at 12:29

SELECT ML.MonthList AS Month, 
       Sum(A.Amount_Received) AS Total_amount, 
       First(MT.MonthF_L) AS MonthF_L
FROM (tbl_MonthList AS ML 
      INNER JOIN tbl_Month_Target AS MT ON ML.MonthID = MT.MonthID) 
      LEFT JOIN tbl_Amount AS A ON ML.MonthID = A.Month_id
GROUP BY ML.MonthList, ML.MonthID
ORDER BY ML.MonthID

Note: In MS Access, multiple joins must be explicitly nested within parentheses

score 0 · Accepted Answer · answered Aug 29 '13 at 08:03

0

Try this:

select ml.MonthList, sum(a.Amount_Received), mt.MonthF_L from tbl_MonthList ml
left join tbl_Month_Target mt on mt.MonthID = ml.MonthID
left join tbl_Amount a on ml.Month_id = ml.MonthID 
group by ml.MonthList, mt.MonthF_L

answered Aug 29 '13 at 08:03

ksalk

503
2
12

Join Three tables with Sum of column in access query

tbl_MonthList

tbl_Amount:

tbl_Month_Target

I need output as shown below

2 Answers2