Dave Mason's TTI Web Site
SchoolMAX and MS Access
Thursday 8:30 - 10:30
SchoolMAX (the new student information system for the district) is a
comprehensive database of everything you could want to know about our students.
Learn how to get at the information to create mailing labels and more.
To get access the the SchoolMAX database through Microsoft Access, you must be on one of the office (in the schools) computers or a computer at the District Office that has been set up for this connection.
You will find an Access File on your computer at school or the District Office (possibly on your desktop, possibly in your data directory). Here at the college, I have copied some of the information from the actual file to a partial database. Look at drive K: in the "Mason" folder for the SchoolMAX.mdb file. Open that file, and you will see the following list of tables:
The "Data Dictionary" file (from the "Manuals" pdf files) shows which tables contain which pieces of information:
Open the CE010 table to see what fields are in it -- Family information and addresses.. Now take a look at CE020 -- Student information. To make mailing labels, we need to get information from both of those tables. To do that, we need a query.
In Access, click the "Query" object button and double-click on "Name & Address Query" to see its results.
If we only want to see the students who were born in lake tahoe, we'll modify the query. Click the "design" button (the triangle and pencil) in the upper left corner of your screen:
Select the "Brth_city" field and in the criteria box, type ":Tahoe" (without the quotes) and click the Exclamation Mark on the toolbar to run the query.
Queries gather information from the tables that are used to create them. Take the Access class here at the college to learn more about making queries (CPS153). Or have your tech support person create a query for you. If your query is not set up completely, you might need to add a "District Number" criterion to your query (7054) so that you don't get information from every student in the state.
Using Information from Queries
Once you have your information, you need to use it somehow. One way is to create form letters or mailing labels.
Because the database is large and off over the rainbow across the Internet, it is probably quicker to take the information and store it on your own computer for use in the form letters or labels.
To do that, we'll copy the information from the query and put in into Excel -- this also allows us to sort and further select the information easily.
In your query, to select all of the information, either press [ctrl]+[a], or in the Edit menu, "select all," or click the blank button where the row and column headers meet at the left side of the screen.
Copy the information ([ctrl]+[c], edit menu, or ).
Then (or first) start Excel (a spreadsheet).
Paste the information into Excel (it will look like the query output).
You can delete the columns that you won't use by clicking the column header letter, right-clicking the column, and selecting "delete" from the shortcut menu.
You can sort your information by clicking in the column you want to sort by and clicking the "A Z" (sort) button.
Always a good idea to save your file before going too far.
Start Microsoft Word.
Tools menu, "Mail Merge." In the Options, you can choose which labels you are printing to.
Create the main document (the Word document). Use labels.
Open the data source (the Excel file)
Merge the data (always go to a new document first, so that you can see it (and format it, if necessary). Then you can print the document.
Same process for form letters. You type the letter and insert the fields you need where you need them.