Multicolumns from Access
 

hi Nigel,
I added a response to your other one. here it is again:
strSQL = "SELECT datefield, count(*) FROM table GROUP BY datefield " & _
"ORDER BY datefield"

Chris

"Nigel RS" wrote:

Hi All
Chris Wilkinson kindly provided me the following code that extracts from an
Access Database with around 200,000 records a summary of the number of rows
by Date.
This displays the count for each date, however I am unable to figue out how
to show the date as well.

Currently I get in one column:
2,345
4,567
589

I would like to get in two columns....
23/3/2007 2,345
24/3/2007 4,567
25/3/2007 589

Sub extractData()
Dim strSQL As String
Dim app As DAO.DBEngine
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set app = New DAO.DBEngine
Set db = app.OpenDatabase("C:\db1.mdb") ' enter your own info here

' get count of records by date in date order - enter own info below
strSQL = "SELECT COUNT(*) from table GROUP BY datefield ORDER BY
datefield"
Set rs = db.OpenRecordset(strSQL)
Range("A1").CopyFromRecordset rs

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing
Set app = nothing

End Sub

Multicolumns from Access
 

Many thanks Chris,

I tried various approaches and stumbled on the solution

You have confrimed it.

Cheers


"Chris Wilkinson" wrote:

hi Nigel,
I added a response to your other one. here it is again:
strSQL = "SELECT datefield, count(*) FROM table GROUP BY datefield " & _
"ORDER BY datefield"

Chris

"Nigel RS" wrote:

Hi All
Chris Wilkinson kindly provided me the following code that extracts from an
Access Database with around 200,000 records a summary of the number of rows
by Date.
This displays the count for each date, however I am unable to figue out how
to show the date as well.

Currently I get in one column:
2,345
4,567
589

I would like to get in two columns....
23/3/2007 2,345
24/3/2007 4,567
25/3/2007 589

Sub extractData()
Dim strSQL As String
Dim app As DAO.DBEngine
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set app = New DAO.DBEngine
Set db = app.OpenDatabase("C:\db1.mdb") ' enter your own info here

' get count of records by date in date order - enter own info below
strSQL = "SELECT COUNT(*) from table GROUP BY datefield ORDER BY
datefield"
Set rs = db.OpenRecordset(strSQL)
Range("A1").CopyFromRecordset rs

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing
Set app = nothing

End Sub