SQL - Sum Values

[gatarossi]

Dear all,

In my access database I have two tables:

1) period - fields: year_month, year_quarter, year
2) expenses_control - fields: accounting_code, profit_center,
year_month and transaction_value.

I have done a relationship between the fields: period.year_month and
expenses_control.year_month

For example:

Table 1
year_month year_quarter year
200701 200701 2007
200702 200701 2007
200703 200701 2007
200704 200702 2007
..
..
..
Table 2
accounting_code profit_center year_month transaction_value
10 3
200701 10
10 3
200701 11
10 3
200701 12
..
..
..

Now I have already done the VBA code to bring the information to an
excel sheet:

A B C D E F
1
2
3 p_cent 3
4
5
6 goup code descr 200701 200702 200703
7 1 10 salary 10 11 12

Sub return_values_year_month()

Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
Dim xlsht As Excel.Worksheet
Dim lin As Long
Dim col As Long

Set xlsht = Sheets("expenses")

filenm = ThisWorkbook.Path & "\expenses.mdb"

col = ActiveCell.Column
lin = Application.Count(Worksheets("expenses").Range("B7:B65536"))
lin = lin + 7

contador = 7

Range(Cells(7, col), Cells(65536, col)).ClearContents

If Cells(7, 2).Value <> "" Then
If Cells(6, col).Value <> "" Then

Do Until contador = lin

sql = "SELECT transaction_value from expenses_control "
sql = sql & "Where profit_center= '" & Cells(3, 2) & "' "
sql = sql & "And year_month= '" & Cells(6, col) & "' "
sql = sql & "And accounting_code = '" & Cells(contador, 2)
& "' ;"

Call getCn(adoconn, adors, sql, filenm, "", "")

xlsht.Cells(contador, col).CopyFromRecordset adors

contador = contador + 1

Loop

adors.Close
adoconn.Close

Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing

End If
End If

End Sub

Now I don't know how to create a VBA code to bring the sum of the
values for year_quarter and year, and I want too change this code to
Sum the values in year_month because if I have a duplicate combination
in my table, I don't know what excel will do...

I will need to change of the layout of the table?

Sum by quarter
A B C D E F
1
2
3 p_cent 3
4
5
6 goup code descr 200701 200702 200703
7 1 10 salary 33 ... ...

Or Sum by year
A B C D E F
1
2
3 p_cent 3
4
5
6 goup code descr 2007 2008 2009
7 1 10 salary 33 ... ...

Thanks a lot!!!

André.