Database How

Database How – To

1. Start | Programs | MS Access
2. Choose Blank Database | OK
3. Name your new database Student Contacts (do not accept the default) | Click Create
4. You are now positioned at the Database Window, but alas, no table, Click New
5. Choose Design View from the New Database dialog, Click OK
6. Type in the field names (that you will make up) for your new database… Choose carefully
      Jedi.
7. Choose a data type for each field by Clicking in the Data Type field which reveals your options
8. You may enter default values and rules for validation or completion in the Field Properties
      dialog at the bottom of the screen. Think carefully about the fields that you need in your
      database, what kinds of questions (or queries) you will be asking of the database, the type of
      information that they need to contain, and the need for validation or default values.
9. You will create six fields with the type specified in parentheses, do not type the parentheses
      and their contents: First Name (text), Last Name (text), Home Phone (text), Zip Code (text),
      Cooperative (Yes/No), and Gift (Lookup Wizard)
10. The Lookup Wizard process will create a set of choices that you will provide in what is called a
       list box. The list box lets you choose from a menu of options. You are currently about the
       business of creating those options. The lookup wizard will wasn’t to know if you want to find
       the information for it’s choices that will be presented to the user, or if you would like to type
       them in. You will choose type them in. One column of choices will suffice, Click in the text
       box to enter your first choice then hit "Tab" on the keyboard to move to the next field. You
       will input the following list: Music, Art, Math, Science, Literature, Social. When you finish
       Click Finish.
11.   Note that none of the field types are "number", this is because we will not be performing
       calculations on them.
12. Close the table by clicking the X in the top right corner of the active window (the table). You
       will be prompted to save the table before you leave. Name the table Student Contacts.
13. You will also be prompted to allow Access to define a Primary Key for you since you have not
       set one as of yet. Answer Yes, which will cause Access to create a primary key field called
       Autonumber which will assign a new number to each new record that you type in.
14. Now Double Click the Student Contacts table in the database window and you will be
       presented with the table in datasheet view. You may choose to move back to design view if
       you wish by clicking on the blue triangle and pencil icon in the top left of the screen. Close the
       table again by clicking the X in the top right corner of the table’s window.
15. Click the Forms Tab in the Database Window | Click New | Choose Form Wizard from the list
       | Click on the Drop Down List Box and select Student Contacts as the table where the form’s
       data comes from. Click OK | Move all fields into the form by Clicking the >> button | Click
       Next | Accept the default Columnar | Click Next | Pick a Purty style | Click Next | Click
       Finish… You have now created a form for data entry and access.
16. You are now presented with you complete form. Enter the needed information hitting "Tab" to
       get from field to field. Use locally valid phone numbers and zip codes. In all of the data you
       enter include a degree of similarity and difference. This is necessary to make querying the
       database a reasonable task with a relatively small number of records.
17. Tab at the end of the form will take you to the next record. Do this until you have at least ten
       records.
18. Close the form when you finish. Click on the Tables Tab | Double Click the Student Contacts
       table. You will see that the form has updated the table to include all of the data that you just
       entered. You didn’t think that I would have had you do that for nothing did you? Close the
       table as usual please.
19. Click on the Queries Tab in the Database Window | Click New | Choose Simple Query Wizard
       | Accept the default value which will be Table: Student Contacts | Move all fields into the
       Query by Clicking the >> button | Click Next | Accept the default value which is detail | Click
       Next | Click Finish… You have now created a Select Query so that you might be able to scan
       the ten thousand records that you just keyed in for useful information…BUT FIRST!!!!… You
       must decide what information you desire…Sire.
20. You will be thrown into Datasheet view in the query…take a second to cheat and remember
       what the information that you typed into the fields was…what were the "range" of zip codes,
       Phone numbers, Names, Gifts, and Cooperation. Looking at the data directly for a second will
       help define a query that will not come up empty handed. OK no more looking!!! You’ve
       stared long enough!!! Click on the blue triangle and pencil at the upper left to change to Design
       View.
21. Let’s start with something simple, Click in the Last Name field under the Criteria Row and
       type in a letter that you know more than one of the last names starts with… I chose S* the *
       lets the rest of the characters in the name "not matter". Hit "Enter" on the keyboard | Look for
       the maroon ! at the top and center of the window…this will run the query and return my
       results in a select query in datasheet view. How many did you get? If None Then Goto Try
       Again, because there was nothing that matched your query. Click on Design View and clear
       the old criteria, typing in new criteria. I got three results returned, I was one of them (my own
       record)…we were all cooperative, but that is not why we were picked, we just happened to
       match the criteria!
22. You may try this again and again, later on including multiple criteria, which will of course
       return fewer results. Be careful not to exclude everybody unless that is what you were
       checking for! I changed my query to *S* which returned all the previous results plus four
       more. This time anyone with an S anywhere in their last name was returned. I then saw that
       three of the records were NOT Cooperative students :-< so I sought to exclude them by adding
       the term Yes to the Criteria under Cooperative. It worked! Now there are only five records
       returned…all Cooperative students. Remember, your mileage will vary because SO WILL
       YOUR DATA. Save a query or two when they work well to help you remember how you did
       them. Do so by Clicking the X on the active Queries window, it asks if you would like to save,
       answer Yes.
23. Let’s generate a report so that we can print out and show the world that we can build an
       Access Database. Close your query if it is still open. Click the Reports Tab in the Database
       Window. Click New | Choose Report Wizard | Choose the Student Contacts Query for Where
       the Objects Data Comes From. | Click Next | Move all fields into the Query by Clicking the >>
       button | Click Next | Click Last Name in the box at the left then hit > on the keyboard to make
       that the primary grouping | Click Next Twice to accept the default values | Choose a Style that
       is sooo pleasin’ | Click Next | Click Finish… Cool isn’t it?
24. Click the X at the top right in the active report window to close your report.
25. Click File | Exit to allow Access to close and save your data for you. Whew! You guys (used
       generically) are troopers!