By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,045 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,045 IT Pros & Developers. It's quick & easy.

problem with dates

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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


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

Nov 13 '05 #2

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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.