Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Getting External data
I need to analyise some data from an Acces database. In the databse i have
a field 'Date Started'. What I need to do is to bring into Excel, a simple count of the number of records for each month e.g Jun 05. At present I am bringing into Excel all the records for that field, and then doing the count in Excel, but there are over 15,000 records, and I am sure that there must be a better way of doing it. Thanks Alex |
#2
|
|||
|
|||
The SQL code for the query would need to have this structu
SELECT FORMAT(Date_LU.RefDate,'YYYYMM') AS 'YrMth', COUNT(*) AS 'PeriodCount' FROM `C:\CoderreR\2005_db`.Date_LU Date_LU WHERE (FORMAT(Date_LU.RefDate,'YYYYMM') Between '200501' And '200505') GROUP BY FORMAT(Date_LU.RefDate,'YYYYMM') That returns the monthly count of records for the period Jan2005 thru Man2005: YrMth PeriodCount 200501 31 200502 28 200503 31 200504 30 200505 31 (In my example, the table only contained sequential dates. Hence, the query returned the number of days in each month) Does that help? -- Regards, Ron |
#3
|
|||
|
|||
As I keep forgetting, MS Query will also build the query for you if you:
Set it to return Grouped Values (ViewQuery PropertiesGroup Records). Add a column and set it to Count (RecordsAdd Column). The resulting SQL code will resemble this: SELECT FORMAT(Date_LU.RefDate,'YYYYMM') AS 'YrMth', Count(*) AS 'Periodcount' FROM `C:\CoderreR\Finances\Cash_Analysis_2005_db`.Date_ LU Date_LU GROUP BY FORMAT(Date_LU.RefDate,'YYYYMM') HAVING (FORMAT(Date_LU.RefDate,'YYYYMM') Between '200501' And '200505') -- Regards, Ron |
#4
|
|||
|
|||
Thanks Ron foryour help
Using your example i have: SELECT FORMAT(Date Started,`YYYYMM') AS 'YrMth', Count(*) AS 'PCount' FROM `C:\MIS\MISDATA`.tblStudents Group by Format(tblStudents.`Date Started`,'YYYYMM') Somethings not quite right - dont suppose you can see what it is can you? Thanks Alex "Ron Coderre" wrote in message ... As I keep forgetting, MS Query will also build the query for you if you: Set it to return Grouped Values (ViewQuery PropertiesGroup Records). Add a column and set it to Count (RecordsAdd Column). The resulting SQL code will resemble this: SELECT FORMAT(Date_LU.RefDate,'YYYYMM') AS 'YrMth', Count(*) AS 'Periodcount' FROM `C:\CoderreR\Finances\Cash_Analysis_2005_db`.Date_ LU Date_LU GROUP BY FORMAT(Date_LU.RefDate,'YYYYMM') HAVING (FORMAT(Date_LU.RefDate,'YYYYMM') Between '200501' And '200505') -- Regards, Ron |
#5
|
|||
|
|||
Regarding:
SELECT FORMAT(Date Started,`YYYYMM') AS 'YrMth', Count(*) AS 'PCount' FROM `C:\MIS\MISDATA`.tblStudents Group by Format(tblStudents.`Date Started`,'YYYYMM') I suspect the problem may be inconsistent use of accent grave marks (`) and apostrophes (') in the SQL code. NOTE: Apostrophes are ASCII code 039. The accent grave marks are ASCII code 096. You must use the accent grave marks around the file path, but I believe you use apostrophes everywhere else. So...I thnk your SQL code should be: SELECT FORMAT('Date Started','YYYYMM') AS 'YrMth', Count(*) AS 'PCount' FROM `C:\MIS\MISDATA`.tblStudents Group by Format('Date Started','YYYYMM') Does that help? -- Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data | Excel Discussion (Misc queries) | |||
import external data from changing file name | Excel Worksheet Functions | |||
Excel 97/2000 - Help with looking up external data. | Excel Discussion (Misc queries) | |||
How do I create a list in excel that contains external data? | Excel Discussion (Misc queries) | |||
parameters option on the external data toolbar is not available | Excel Discussion (Misc queries) |