Using Microsoft Excel as a Database
|
Introduction
|
Ever wonder how to use the advanced features of the software tools that come loaded on your classroom computer? These classes cover some of the advanced features and functions of your software tools. Experience the creativity of teaching with technology by learning about enticing projects and products that can improve your planning and productivity and as well as engage your students in rigorous thinking.
|
|
Overview
|
Teachers are increasingly asking for database software to manage classroom libraries, student data, or organize school events. Microsoft Excel is a very manageable tool for creating and using databases. Participants in this workshop will learn the basic features of Excel that allow you to use spreadsheets as a database. Participants will review authentic uses of databases in the classroom and will create a database file that they can continue to use during the school year. Teachers will also receive example databases, including a school science fair template which manages students, volunteers, and certificate creation for each student. Participants must be already familiar with using MS Excel as this is not a basic spreadsheet class.
|
What is a database? (Library catalogs, note cards, a collection of data that we wish to view in different ways.
Learn the Spreadsheet/Database Basics (PC)
Work with a database: Baseball! Follow instructions in this file to organize, maintain, and manage data.
zzExcelDB1.xls
Creating a New Database
Start creating a student database. Collectively generate field names and types.
computer number, student name, book numbers, parent name(s), parent email(s), DOB, grade, how getting home, extracurricular (sports), phone#, serial nujmbers, student id, allergies, special needs, ...
zzDummyData.xls (Dummy data if needed)
Advanced Spreadsheet Ideas
For practice with the If/Then statements in a spreadsheet: zzif_then.xls
Pleace an If statement in your student database.
=IF(E19=1, "Space Lab", IF( E19=2, "Cooking", IF(E19=3, "Drafting", IF(E19=4, "Auto Mechanics", "Error" ))))
Replace 'E19' with the cell your code is in. Replace words inside quotes with your new choices.
Nest If Statement Example: zzNestedIf.xls
Merging the Data Into Other Documents
Using Excel to collect and organize data is great. Using that data in other forms and in other documents is even better. Microsloth Word can use Excel worksheets as data sources for creating labels, forms, certificates, etc.
The Science Fair is a great basis for really getting a lot out of a database and word processor. (As this workshop is being held in a PC lab, Mac users will find these files useful since the screenshots are Mac-based.) Download the following files and place them into a new folder on your computer.
PC: Data Files: zzxlstudentdata.xls zzxlvolunteer.xls
PC: Templates: 
zzxlthanks.doc zzxlSciFrRubric_simple.doc zzxlFirst_place_cert.doc
zzxlparent_letter.doc zzxlscp_recog_certif.doc
Open studentdata.xls file. This is the data file. Add a record if needed, but make sure your data is in a list using Data:Filter:Auto Filter .
Open parent_letter.doc . Find merge fields that are already embedded into the document. View the merged field code and actual contents of merged fields using the Mail Merge Toolbar.
(Merge toolbar will appear when merge tools are activated. Opening these files will activate the merge toolbar.)
Open the different documents and alter them in some way. You should have to locate the data file, which should be in the same folder if you downloaded all into the same folder. Practice Mail Merging by 'printing' to a new file instead of the printer.
Creating Your Own Classroom Database
Either create a new spreadsheet or open a previously created spreadsheet of student data. You need to create the column headers and at least one row of data. Save the spreadsheet file. A workable idea for the classroom would be to have each student use the Form tool and complete their student record for you by the end of the week. You wouldn't have to type each one and they can start learning how a database can work.
Open a new Word document. Let's create an teacher letter that will go home at the end of the first week. You can reflect on what the class generally covered, but you can also touch base with the parents to make sure the contact information you have is correct. Save the document.
Insert the merge field by using the Tools:Mail Merge menu. Create a Form Letter for Step 1. Find your student data file for Step 2. The merge tool bar will now show on your Word document's menu bar.
For clarification: Microsloth's Online Instructions
Using the Database Merge to Print Labels
Printing to Avery Labels is just as easy. Instead of selecting a form letter in Step 1, select Mailing Labels. These take some time to get setup the right way, so print to blank paper sheets first to test their alignment.
July 24, 2008 - Fulmore MS, Trainer: Howard J Martin
Roster: William Bergner, Vonda Bloomquist, Betty Butler, B Cruz, Norma Hawes, Holly Kofod, Carlos Lopez, Vivian Majeed, Manny Miramontes, Cheryl Verse, Erika Pesek, Wanda Wilson
Comments (0)
You don't have permission to comment on this page.