Data storage retrieval access instead of excel
Download
1 / 11

Data Storage & Retrieval: Access instead of Excel - PowerPoint PPT Presentation


Data Storage & Retrieval: Access instead of Excel. Melissa Pico Summer 08. 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. Access vs. Excel.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha

Download Presentation

Data Storage & Retrieval: Access instead of Excel

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Data storage retrieval access instead of excel l.jpg

Data Storage & Retrieval:Access instead of Excel

Melissa Pico

Summer 08


Two peas in a microsoft package l.jpg

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


Access vs excel l.jpg

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


Relational databases l.jpg

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


Relationships integrity is the key l.jpg

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.


Monogamy isn t for everybody l.jpg

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.


Storing data l.jpg

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


Retrieving data l.jpg

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 


Retrieving data9 l.jpg

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


Starting your access database l.jpg

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.


Access 2007 resources l.jpg

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


ad
  • Login