Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I access the access data via Excel 2002 with auto update ? | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
Access.Application.Quit - opens Access again then won't close - He | Excel Programming | |||
Access Form In An Access Report (SubForm) Question | Links and Linking in Excel | |||
Getting Access Error Messages when running Access through Excel | Excel Programming |