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

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

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

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

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