Intro to Access 2007

1. Intro to Access 2007 Lindsey Brewer CSSCR September 18, 2009

2. What we will be learning today • Access vs. Excel • Starting with Access 2007 • Create a database • Tables • Forms • Relationships between data • Queries • Reports • Where to find more information

3. When should I use Access vs. Excel? • Excel is designed for flat data structures • analysis of data is the primary goal • Excel is designed to store numbers rather than text • Access is designed for relational data structures • relational data divides your data into different pieces through different tables • using separate tables can help make data easier to manage • storage of data is the primary goal • Access can hold much more data than Excel and is better at storing text • Access is also a better choice when there are multiple users working on one data file since Access only locks a record when a user is changing it.

4. Access 2007 and older versions • The file extension for Access 2003 is .mdb, and Access 2007 is .accdb. • To go between the different versions of Access, you can save and upgrade files into different formats with the “Save As” button.

5. Open Access 2007 • Click START • Click ALL PROGRAMS • Click MICROSOFT OFFICE • Click MICROSOFT OFFICE ACCESS 2007

6. The getting started page • This page has categorized templates that you can choose from. • If you are creating a new template, you can select a blank database option and give it a new name in the far right pane. • You can also download an existing database.

7. Create a database • Click on BLANK DATABASE at the top of the getting started page • Give the file the name SURVEY on the right • Click CREATE

8. Tabs of Access 2007 • Home • Create • External Data • Database Tools • Datasheet

9. Tables – option 1 • Tables are places to store your raw data in a spread-sheet like format • There are two different ways to make tables. • First, you can select a table template which is a table already set up for you by Access. • Click on CREATE. • In the TABLES group, click TABLE TEMPLATE. • This can be handy if you want to create a table for the areas that Access has already established.

10. Creating a table – option 2 • Click on the HOME tab • You can create a more customized table by selecting DESIGN VIEW in the upper left hand corner. • Give the Table the name CONTACTS

11. Adding field names to a table • The ID field name is already filled in when you create a table • Enter • LAST NAME • FIRST NAME • EMAIL • PHONE • NOTES • MOST RECENT CALL • INTERVIEWER ID • Change the data type of for MOST RECENT CALL to DATE/TIME

12. Table creation continued • Click VIEW in the upper left hand corner and switch to DATASHEET VIEW • Click YES when you are prompted to save your table • Another way to save a table is to right click on the table’s tab • Now we can enter data into our new table • After entering information, right click on the contacts tab and click SAVE

13. Forms • The form gives you a easy to use interface for data entry and editing. Forms aren’t necessary, but they can make data easier to manage. • Click the CREATE tab • Click FORM in the FORMS group • When you are in LAYOUT VIEW, you can easily delete parts of a form by right clicking and selecting delete. This will not delete the field name from your table. • Right click and select SAVE.

14. Creating a new entry using forms • To enter data into your table, you can use forms. • Switch the form into FORM VIEW • Click NEW (BLANK) RECORD at the bottom of the form box • Enter your new data in the form • Save your form, and hit the REFRESH button to see your data appear in your CONTACTS table

15. Creating split forms • Split forms allow you to view the datasheet and the form at the same time • Click on the CREATE tab • In the FORMS group, click SPLIT FORMS

16. Queries • Queries enable you to specify the information that you want to display. • It is important to note that the query data is live. That means that if you change the data in the query results, it will be changed in the entire database. • Deleting data in a query deletes the data everywhere!

17. Run a query • Click on the CREATE tab • Click QUERY DESIGN • Select the table that we want to query. Select CONTACTS. • In CONTACTS, double-click the fields that you want to see in the results of the query. • Add fields LAST NAME and NOTES and MOST RECENT CALL • In the RESULTS section, click RUN!

18. Putting parameters on a query • Click on the CREATE tab • Click on QUERY DESIGN • Add table CONTACTS • Add LAST NAME, NOTES, and MOST RECENT CALL • In the CRITERIA row, enter the criteria that you would like to put on the query. • Enter “Between Date() And Date()-6” under MOST RECENT CALL • This will find all data for calls that have happened in the past week • To find the syntax for other criteria, type in EXAMPLES OF QUERY CRITERIA into the help menu. • Click RUN!

19. Create a report – option 1 • Reports are ways of presenting your data. • Select the table or query that you would like to be the basis of your report. In this case, CONTACTS. • Click the CREATE tab. • In the REPORTS group, click REPORT. •  Access will create a report using the data from CONTACTS.

20. Create a report – option 2 • Use the REPORT WIZARD to create a report • Click the CREATE tab • In the REPORTS group, click REPORT WIZARD • Select the options that you would like for your report

21. Editing your report • View your report in LAYOUT VIEW • Use the commands in the FORMAT, ARRANGE, OR PAGE SETUP tabs. •  View your report in DESIGN VIEW • Change the titles and other design features.

22. Relationships • Relationships bring tables together so you can gather the information that you need. • Relationships are made by including one field from one table as a field in another table. • Primary key – the field which is included in another table to create a relationship. Each table has a primary key. • Foreign key – the name of the new field once the primary key is shared with another table.

23. Three types of relationships • One-to-one: A single record in one table is related to a single record in another table, and vice versa. • One-to-many: A single record in a table is related to many records in another table. These are the most common type of relationship in databases. • Many-to-many: Several records in one table are related to several records in another table.

24. More information • To learn more about Access 2007, you can visit Microsoft’s website http://office.microsoft.com/en-us/access/default.aspx. They have tutorials, demos, and webcasts available. • CSCCR has a handout about Access 2000 and relational databases on its website.