P: n/a

hi
i have a database with two tables
Table 1 contains the following fields
area , date, breakdowns, cost
the date field is formatted as short date
the date is assigned when the record is entered by the user
table two has the following fields
area, monthly cost, month, year
the cost is input manually monthly as is the month and year.
now i join the tables together on the area field
i need to total my cost for a 12 month period by month and year based on
a combobox selection ie. 2004
giving
jan feb mar apr etc...
home 20 60 90 10
can anyone please tell how to do this
thanks in advance
kevin  
Share this Question
P: n/a

Air coded:
SELECT T2.Area,
SUM(IIf(T2.Month = 1, T1.Cost, 0) AS Jan,
SUM(IIf(T2.Month = 2, T1.Cost, 0) AS Feb,
SUM(IIf(T2.Month = 3, T1.Cost, 0) AS Mar,
SUM(IIf(T2.Month = 4, T1.Cost, 0) AS Apr,
SUM(IIf(T2.Month = 5, T1.Cost, 0) AS May,
SUM(IIf(T2.Month = 6, T1.Cost, 0) AS Jun,
SUM(IIf(T2.Month = 7, T1.Cost, 0) AS Jul,
SUM(IIf(T2.Month = 8, T1.Cost, 0) AS Aug,
SUM(IIf(T2.Month = 9, T1.Cost, 0) AS Sep,
SUM(IIf(T2.Month = 10, T1.Cost, 0) AS Oct,
SUM(IIf(T2.Month = 11, T1.Cost, 0) AS Nov,
SUM(IIf(T2.Month = 12, T1.Cost, 0) AS Dec
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON (T2.Area = T1.Area AND T2.Month =
MONTH(T1.Date) AND T2.Year = Year(T1.Date))
WHERE T2.Year = YourComboboxValue
GROUP BY T2.Area
ORDER BY T2.Area
Yannick
"kevin carter" <ke***@carter5711.freeserve.co.uk> wrote in message
news:2f**************************@posting.google.c om... hi i have a database with two tables Table 1 contains the following fields
area , date, breakdowns, cost
the date field is formatted as short date the date is assigned when the record is entered by the user
table two has the following fields area, monthly cost, month, year
the cost is input manually monthly as is the month and year.
now i join the tables together on the area field
i need to total my cost for a 12 month period by month and year based on a combobox selection ie. 2004 giving
jan feb mar apr etc... home 20 60 90 10
can anyone please tell how to do this
thanks in advance
kevin  
P: n/a

Correction (forgot a parenthesis)... still air coded!
SELECT T2.Area,
SUM(IIf(T2.Month = 1, T1.Cost, 0)) AS Jan,
SUM(IIf(T2.Month = 2, T1.Cost, 0)) AS Feb,
SUM(IIf(T2.Month = 3, T1.Cost, 0)) AS Mar,
SUM(IIf(T2.Month = 4, T1.Cost, 0)) AS Apr,
SUM(IIf(T2.Month = 5, T1.Cost, 0)) AS May,
SUM(IIf(T2.Month = 6, T1.Cost, 0)) AS Jun,
SUM(IIf(T2.Month = 7, T1.Cost, 0)) AS Jul,
SUM(IIf(T2.Month = 8, T1.Cost, 0)) AS Aug,
SUM(IIf(T2.Month = 9, T1.Cost, 0)) AS Sep,
SUM(IIf(T2.Month = 10, T1.Cost, 0)) AS Oct,
SUM(IIf(T2.Month = 11, T1.Cost, 0)) AS Nov,
SUM(IIf(T2.Month = 12, T1.Cost, 0)) AS Dec
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON (T2.Area = T1.Area AND T2.Month =
MONTH(T1.Date) AND T2.Year = Year(T1.Date))
WHERE T2.Year = YourComboboxValue
GROUP BY T2.Area
ORDER BY T2.Area
"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:s_********************@news20.bellglobal.com. .. Air coded:
SELECT T2.Area, SUM(IIf(T2.Month = 1, T1.Cost, 0) AS Jan, SUM(IIf(T2.Month = 2, T1.Cost, 0) AS Feb, SUM(IIf(T2.Month = 3, T1.Cost, 0) AS Mar, SUM(IIf(T2.Month = 4, T1.Cost, 0) AS Apr, SUM(IIf(T2.Month = 5, T1.Cost, 0) AS May, SUM(IIf(T2.Month = 6, T1.Cost, 0) AS Jun, SUM(IIf(T2.Month = 7, T1.Cost, 0) AS Jul, SUM(IIf(T2.Month = 8, T1.Cost, 0) AS Aug, SUM(IIf(T2.Month = 9, T1.Cost, 0) AS Sep, SUM(IIf(T2.Month = 10, T1.Cost, 0) AS Oct, SUM(IIf(T2.Month = 11, T1.Cost, 0) AS Nov, SUM(IIf(T2.Month = 12, T1.Cost, 0) AS Dec FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON (T2.Area = T1.Area AND T2.Month = MONTH(T1.Date) AND T2.Year = Year(T1.Date)) WHERE T2.Year = YourComboboxValue GROUP BY T2.Area ORDER BY T2.Area   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 995
 replies: 2
 date asked: Nov 13 '05
