How to categorised data from recordset?

I need to retrieve data from a table called Bank_Account into Excel file. However, I want to categorised it in Excel file. For example, I have 3 RoyalBank and 2 ING data(s) in my Bank_Account table. When it appears in Excel file, I want to have the 3 RoyalBank from row 1 to row 3. Then from row 5 to row 6 is ING. Meaning there will have 1 row is blank between last row of RoyalBank and first row of ING. How do I do that?

here is the code I have, it display all data in the Excel file, but NOT separate the RoyalBank data(s) and ING data(s) into 2 group.

==========================

....

getReport = "SELECT * FROM REPORT ORDER BY BANK_NAME"
Set rs = cn.Execute(getReport)

Set xlsApp = New Excel.Application

If xlsApp Is Nothing Then
MsgBox "Cannot Open Excel Application"
Exit Sub
End If

excelFileName = App.Path & "\Report_Summary_ByBank.xls"

'Delete summary report for updated summary report

If Dir(excelFileName) <> "" Then
Kill (excelFileName)
End If

Set newWorkSheet = xlsApp.Workbooks.Add.Worksheets.Add


With newWorkSheet

.Range(.Cells(1, 1), .Cells(3, 8)).Select
xlsApp.Selection.Columns.AutoFit

.Range(.Cells(1, 1), .Cells(3, 8)).RowHeight = 20
.Rows(1).Font.Bold = True
.Rows(2).Font.Bold = True
.Rows(3).Font.Bold = True
.Cells(1, 4).Value = "Andy Tey"
.Cells(2, 4).Value = "Financial Report Summary"
.Cells(3, 4).Value = "****************************************************************"
.Rows(1).HorizontalAlignment = xlHAlignCenter
.Rows(2).HorizontalAlignment = xlHAlignCenter
.Rows(3).HorizontalAlignment = xlHAlignCenter

.Cells(4, 1).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
.Cells(4, 2).Value = "Bank Name"
.Cells(4, 3).Value = "Bank Type"
.Cells(4, 4).Value = "Amount $"
.Cells(4, 5).Value = "Deposit / Withdrawal"
.Cells(4, 6).Value = "Category"
.Cells(4, 7).Value = "Comments"

'Header settings

.Rows(4).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(4).Font.Bold = True ' set first row - header as font bold
.Rows(4).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(4, 1), .Cells(4, 1)).RowHeight = 30

.Cells(6, 1).CopyFromRecordset rs 'Copy recordset to Excel file


' Make every columns autofit to the data(s)
.Range(.Cells(4, 1), .Cells(20, 8)).Select
xlsApp.Selection.Columns.AutoFit

End With

newWorkSheet.SaveAs App.Path & "\Report_Summary_ByBank.xls"

MsgBox " File has been saved to '" & excelFileName & "' "

xlsApp.Visible = True 'Show the Excel file - Report_Summary.xls

'after the excel closed, close the excel application

If xlsApp.Visible = False Then
xlsApp.Quit
End If

Set newWorkSheet = Nothing
Set xlsApp = Nothing

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

I may be wrong in this, but as I understand it.. when you use the copyfromrecordset method to automate the conversion, then there is no way to directly specify breaks. You might try filtering for certain values before you copy the recordset...ie rsbank.filter "BankName=ING", and then reset the filter after you copy... although that might not work. Otherwise, just declare a counter variable and use it to loop through your recordset and assign particular Fields to their corresponding cells.

Okay ... now I am using two queries to get the data I want. I first I select the bank name with no repeatation --> "SELECT DISTINCT(BANK_NAME) FROM REPORT" ... then I use second queries to get all the data with the specified bank name(s) --> "SELECT * FROM REPORT WHERE BANK_NAME='"&getTT&"' ORDER BY BANK_NAME" ... when the Excel file open, it actually only display the last bank name(s) ... eg. If I have 2 ING and 3 RoyalBank, it should display the 2 ING first, then 3 RoyalBank. But, it just display 3 RoyalBank. I am not sure where did I do wrong. Please help. Code here:-

=============================

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim getReport As String

Dim excelFileName As String
Dim xlsApp As Excel.Application
Dim newWorkBook As Excel.Workbook
Dim newWorkSheet As Excel.Worksheet

Set cn = New ADODB.Connection

Set xlsApp = CreateObject("Excel.application")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & App.Path & "\account_db.mdb"

getReport = "SELECT DISTINCT(BANK_NAME) FROM REPORT"
Set rs = cn.Execute(getReport)

Set xlsApp = New Excel.Application

If xlsApp Is Nothing Then
MsgBox "Cannot Open Excel Application"
Exit Sub
End If

excelFileName = App.Path & "\Report_Summary_ByCategory.xls"

'Delete summary report for updated summary report

If Dir(excelFileName) <> "" Then
Kill (excelFileName)
End If

Set newWorkSheet = xlsApp.Workbooks.Add.Worksheets.Add

With newWorkSheet

.Range(.Cells(1, 1), .Cells(3, 8)).Select
xlsApp.Selection.Columns.AutoFit

.Range(.Cells(1, 1), .Cells(3, 8)).RowHeight = 20
.Rows(1).Font.Bold = True
.Rows(2).Font.Bold = True
.Rows(3).Font.Bold = True
.Cells(1, 4).Value = "Andy Tey"
.Cells(2, 4).Value = "Financial Report Summary"
.Cells(3, 4).Value = "****************************************************************"
.Rows(1).HorizontalAlignment = xlHAlignCenter
.Rows(2).HorizontalAlignment = xlHAlignCenter
.Rows(3).HorizontalAlignment = xlHAlignCenter

.Cells(4, 1).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
.Cells(4, 2).Value = "Bank Name"
.Cells(4, 3).Value = "Bank Type"
.Cells(4, 4).Value = "Amount $"
.Cells(4, 5).Value = "Deposit / Withdrawal"
.Cells(4, 6).Value = "Category"
.Cells(4, 7).Value = "Comments"

'Header settings

.Rows(4).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(4).Font.Bold = True ' set first row - header as font bold
.Rows(4).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(4, 1), .Cells(4, 1)).RowHeight = 30


Dim p As Integer
Dim getTT As String

Do While Not rs.EOF

For p = 0 To rs.EOF

getTT = rs.Fields(p)

Dim rs2 As New ADODB.Recordset
Dim getReport2 As String

getReport2 = "SELECT * FROM REPORT WHERE BANK_NAME='" & getTT & "' ORDER BY BANK_NAME"
Set rs2 = cn.Execute(getReport2)

Dim k As Integer

For k = 0 To rs2.EOF
.Cells(i + 6, 1).CopyFromRecordset rs2 'Copy recordset to Excel file
Next

' Make every columns autofit to the data(s)
.Range(.Cells(4, 1), .Cells(50, 8)).Select
xlsApp.Selection.Columns.AutoFit

Next
' Call insert4rows

rs.MoveNext
Loop

End With
newWorkSheet.SaveAs App.Path & "\Report_Summary_ByCategory.xls"

MsgBox " File has been saved to '" & excelFileName & "' "

xlsApp.Visible = True 'Show the Excel file - Report_Summary.xls

'after the excel closed, close the excel application

If xlsApp.Visible = False Then
xlsApp.Quit
End If

Set newWorkSheet = Nothing
Set xlsApp = Nothing
rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

End Sub

ok.. question... are you always going to be dealing with the same two banks? I don't like automating things if I don't have to, and so I would probably do something along these lines....

Private Sub DiscoverDistinct()
dim i as integer
dim rsrecordset as adodb.recordset
dim cnconnection as adodb.connection
dim OldBank as string, NewBank as string
dim strAll as string
dim BankNames() as string
set cnconnection = new adodb.connection
cnconnection.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\account_db.mdb"
cnconnection.open
strAll = "Select * from Report"
set rsrecordset = new adodb.recordset
rsrecordset.open strall, cnconnection, adforwardonly, adlockpessimistic, adcmdtext
OldBank = trim(Ucase(rsrecordset("Bank_Name")))
NewBank=trim(Ucase(rsrecordset("Bank_Name")))
do until rsrecordset.eof=true
if oldbank <> newbank then
i = i + 1
redim preserve BankNames(i) as string
BankNames(i) = OldBank
oldbank = newbank
else
rsrecordset.movenext
newbank = trim(Ucase(rsrecordset("Bank_Name")))
end if
loop

for i = 0 to ubound(BankNames)
WriteExcel AddFilter(BankNames(i), rsRecordset)
RemoveFilter rsRecordset
next i

Public Function AddFilter(ByVal Name as string, ByRef rsRecordset as recordset) as recordset
addFilter = rsRecordset.filter "Bank_Name='" & Name &"'"
End Function

Public Function WriteExcel(ByRef rsRecordset as recordset)
'Do excel output here
end function

public function RemoveFilter (byref rsRecordset as recordset)
rsrecordset.filter = adfilternone
end function



Now.. I'm not guaranteeing that this will work ver batim... I am banging this out as I go... but it should be close to working.

Well, I think I will have 3 differences Bank name in my db. If next tiem I need to add more bank into db, I will just add it manually in the db. Do I answered your question?

kind of. I'm guessing that this is a contract project that you can't send me the code for, huh? I'm apparently not making myself clear, but may be able to if I could see more of what you were doing

No. It's not a contract project I am doing that for my brother. He needs a financial diary. So I am making the application for him. What code do you want? I thought I posted the code on my first post?

well.. I'll tell you what I did in a project I'm doing. I took the loop I posted earlier and built an array of items I wanted to search by. Then I built another loop to parse that array from inti = 0 to ubound(array) and built a where clause by concatenating a string together inside the loop. Then I appended that to my SQL string and executed it. I deal primarily with SQLServer, and found that Select Distinct in Access didn't behave the same way it did in SQLServer. In fact, it did the exact opposite of what you are getting. It gave me only the first record from the table. My SQL String basically looked like this after concatenation:

strSQL = "Select * from Dimwiddle where dim='" & array(0) & "' OR dim='" & array(1) &"'" and etc.....


One question though... do you really need those () around your distinctive field?

You are right. DISTINCT in Access only display 1 bank name. I took out the distinct and it display what i want in my excel file. Thank you very much

BTW, I am now working on the part of inserting 2 blank rows in excel file after every bank name. But it doesn't work, do you have any idea how? Eg. in excel file ...

ING
ING
blank row
blank row
RoyalBank
RoyalBank
RoyalBank
blank row
blank row