Getting External data

A

Alex H

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
 
R

Ron Coderre

The SQL code for the query would need to have this structure:

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?
 
R

Ron Coderre

As I keep forgetting, MS Query will also build the query for you if you:
Set it to return Grouped Values (View>Query Properties>Group Records).
Add a column and set it to Count (Records>Add 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')
 
A

Alex H

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
 
R

Ron Coderre

Regarding:
SELECT
FORMAT(Date Started,`YYYYMM') AS 'YrMth', Count(*) AS 'PCount'
FROM `C:\MIS\MISDATA`.tblStudents
Group by Format(tblStudents.`Date Started`,'YYYYMM')
Click to expand...

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?
 
Top