I have very little formal training in data and statistics.  I am a lawyer, but I have always been frustrated that very few people were using the very important data set from the T3010 Registered Charity Information Return that is painstakingly compiled by CRA every year.  So I taught myself a little about the T3010 and analyzing it.  I would appreciate any feedback and your comments don’t have to start with “Mark did you not get only 57% on Grade 13 math”.  By the way, sometimes you are just trying to pass a course and 57 is a clearly a pass!   Needless to say, I would not suggest that anyone hire me as a math tutor above say Junior Kindergarten level.

 

CRA used to provide T3010 in Microsoft Access.  Then they moved to having both MS Access and CSV (Excel).  Then they got rid of MS Access.  It was all for a good reason – they did not want to provide data from a regulator in a proprietary format such as MS Access that you would need to purchase the software to use.   So now you can request from CRA the information on the CRA website but they will send it to you in CSV/Excel, not in MS Access.  In normal times you can obtain Charity data from CRA at Request publicly available data from the List of charities (Charities Listings)  Best (when not facing the COVID-19 shutdowns) is to contact CRA directly to obtain the latest T3010 data.

 

HOW TO CONVERT EXCEL T3010 DATA TO MS ACCESS

 

Take the CRA diskette and put it in your CD ROM drive.

[The data may be sent in CSV format and you may need to convert each file to MS EXCEL workbook if you are having problems with certain restrictions on file size]

Open MS Access

Create a blank desktop dataset

Go to External Data /New Data Source / From File / TEXT

Select source / BROWSE for file

(you will do this a number of times with different files)

Then do Import the source data into a new table in the current database

Use Delimited

Then it should say “comma” and leave then

***Tick off First Row Contains Field Names (so that they are removed) and then do next and next and finish for that file

Then do next again etc for each file (but not the first few files  that have the # in front of them)

Now you have in access all the Tables but they need to be linked for searches.

Go to Database Tools and select “Relationships”

Highlight all the files and do “Add”

Then I connect everything to the IDENT file BN#

Click on the Bn/Registration line on IDENT and link to the next file say Gifts and drop it on the BN/Registration link in Gifts.

Then it will have a box saying Edit Relationships and it will show BN/Registration in Ident Linked to BN Registration for Gift and you click ok.

Make sure that you save your  Access document.   Then you are ready to search.

 

 

Let us say we want to see the Canadian registered charities with the highest revenue. (Line 4700)

 

Go to for example CREATE /Query Wizard/ Simple Query Wizard.

I always start by selecting the Ident file and at a minimum adding Bn/Registration and either legal name or account name.  You add them by clicking them on the left and they appear on the right.  Then you select for example Financial Section D &Schedule 6 and in this case lolling for total revenue line 4700

 

Then you do next and call your query whatever you want and do finish.

 

Now you have Access showing you BN, Legal Name, Account Name and Line 4700 (Revenue).  You can sort by highlighting a column and then sort then column from largest to smallest.