problem with dates

[kevin carter]

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

[Yannick Turgeon]

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

[Yannick Turgeon]

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