240 likes | 410 Views
Intro to Access 2007. Lindsey Brewer CSSCR September 18, 2009. 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. When should I use Access vs. Excel?.
E N D
Intro to Access 2007 Lindsey Brewer CSSCR September 18, 2009
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
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.
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.
Open Access 2007 • Click START • Click ALL PROGRAMS • Click MICROSOFT OFFICE • Click MICROSOFT OFFICE ACCESS 2007
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.
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
Tabs of Access 2007 • Home • Create • External Data • Database Tools • Datasheet
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.
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
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
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
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.
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
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
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!
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!
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!
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.
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
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.
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.
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.
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.