Data Storage & Retrieval: Access instead of Excel

1. Data Storage & Retrieval:Access instead of Excel Melissa Pico Summer 08

2. Two Peas in a… Microsoft package • Both Access and Excel… • run queries to sort and filter data • Run calculations • Generate reports • Use forms to navigate your data • Have similar aesthetic components

3. Access vs. Excel • In Access you can create multiple relational tables, whereas, in Excel, you can only have non-relational or flat worksheets-- they don’t interact with one another • In Access you can store more data than in Excel (many thousands of entries, versus 15,000 rows) • Access is better for long text “string type” data, whereas Excel is better when working mainly with numeric data and running statistical comparisons with that data

4. Relational Databases Access allows the user to create relationships between similar fields across different tables or queries Ex: If your study is collecting demographic info, and pre and post test data for the same subjects, creating a relationship between the subject and their responses, enable the demographic data to be linked to your subject’s responses without having to enter the data multiple times

5. Relationships: Integrity is the key • Access databases are all designed to enforce the concept of referential integrity, which ensures that relationships between tables remain consistent. • TableA & TableB  • We may not add a record to the TableA unless it points to a valid record in TableB. • If the primary key for a record in the TableB table changes, all corresponding records in the TableA must be modified using a cascading update. • If a record in the TableB is deleted, all corresponding records in the TableA must be deleted using a cascading delete.

6. Monogamy isn’t for everybody One-to-one relationships : 1 record in TableA corresponds to 1 record in TableB. One-to-many relationships : each record in TableA may have many linked records in TableB, but each record in TableB may have only 1 corresponding record in TableA. Many-to-many relationships : each record in TableA mayhave many linked records in TableB and vice-versa.

7. Storing Data A unique attribute of Access is that it stores your data (in the form of tables) inside Access itself. This allows the user to interact with a more friendly layout called “form view” without losing the datasheets (tables) in the background. In form view, the user can perform all the same tasks as in the datasheet view (change, delete, or add to a record), but it is presented in a more aesthetically pleasing way

8. Retrieving Data Unlike Excel, with a relational database you can also use a query to search multiple tables at once! Or, you can use a query to merge the data of two tables to create a new table Note: you can define the specific “criteria” you are looking for in a query! For criteria examples, search “criteria” in the help menu, located in the upper right hand corner, identified by a question mark 

9. Retrieving Data You can also retrieve data using a form with parameters. To do this, first create a query, and in the criteria field, enter the text you want the dialog pop-up box to say, with brackets at the each end. For example: [what cartoon?] Then, create a form (using form design), and define the “record source” as the query you created, the dialog box will pop-up when you open the form

10. Starting your Access database • A Bottom-up process • Think about what you will need you database to do with the data you have • will you need to be able to sort by date? query persons by age? enter many records for one person? or one record per person? • these questions are essential to define prior to starting in access! • Develop a list of questions you will want your database to be able to answer.

11. Access 2007 Resources • http://office.microsoft.com help and how-to  Access 2007 help • UW library online (search through the uw catalog): • Access 2007 for starters: the missing manual • Access 2007: the missing manual