Hello again

And no I'm not dumb, I'm just having one of those days. :)

This time I have an even bigger problem that I've been battling wit
for a while. The only way to really explain it is to upload an exce
file which I have done - virus free I promise.

This is the code I'm working with

Code
-------------------

cn.Open cString
For m = 1 To curMonth
ServBattbyDealer = "select a.sp_name as branch, a.mat_grp_desc as material_group, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat) as value_excl_vat " & _
" from services_by_dealer a left outer join cb b on a.sp_name = b.sp_name and a.mat_grp_desc = b.mat_grp_desc" & _
" and month(b.inv_date) = " & m & " and year(b.inv_date) = 2004 group by a.sp_name,a.mat_grp_desc order by a.sp_name, a.mat_grp_desc"

If rs.State = -1 Then rs.Close

rs.Open ServBattbyDealer, cn, adOpenKeyset, adLockOptimistic, adCmdText

'dump data into excel
Call PopulatePage2(m)

rs.Close ' Close the recordset for next loop

Next m


-------------------



Code
-------------------

Public Function PopulatePage2(Optional y As Integer = 1)
Dim RowCount, HeaderRow, curColumn, m

HeaderRow = 2
RowCount = HeaderRow
While rs.EOF = False
RowCount = RowCount + 1

For m = 0 To rs.Fields.Count - 1
curColumn = IIf(m = 0, 1, ((y - 1) * (rs.Fields.Count - 1)) + m + 1)

If m = 1 Then msSheet.Cells(HeaderRow - 1, curColumn + 1).Value = MonthName(y) _

msSheet.Cells(HeaderRow - 1, curColumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow - 1, curColumn).Font.Size = 12
msSheet.Cells(HeaderRow - 1, curColumn).Font.Bold = True
msSheet.Cells(HeaderRow - 1, curColumn).Interior.Color = vbBlue

msSheet.Cells(HeaderRow, curColumn).Value = ProperCase(Replace(rs.Fields(m).Name, "_", " "))
msSheet.Cells(HeaderRow, curColumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow, curColumn).Font.Size = 12
msSheet.Cells(HeaderRow, curColumn).Font.Bold = True
msSheet.Cells(HeaderRow, curColumn).Interior.Color = vbBlue
msSheet.Cells(RowCount, curColumn).Value = rs.Fields(m).Value
Next m
rs.MoveNext
Wend
End Function

-------------------

The problem as you can see from my file is that some columns are bein
repeated which I dont want.

If you dont want to look at the file this is my problem in it'
simplest form.

Data is being extracted from a sql database for each month using th
above query, the data is then populated into an excel file. Each mont
has Qty and Price

This is the output I'm getting:

Product Group Desc Qty Price Product Group Desc Qty Price Produc
Group Desc Qty Price Product Group Desc Qty Price

This is what I'm actually looking for:

Product Group Desc Qty Price Qty Price Qty Price Qty Price

I hope I have not confused you, because I'm begining to confus
myself.

I would really appreciate some help.

Kind Regard

--
Message posted from http://www.ExcelForum.com