DJ Dates

Access 2007 Database Tutorial 3.1. Forms and Subforms

In this video, trying some new techniques such as captions (YouTube), increasing audio speed and gradient wipes, in Premiere. I had reread the audio from a rewritten transcript, after finding that I said the words "going to" 90 times. Ouch.

There are two approaches to Microsoft Access. One may approach Access with a background and understanding of traditional relational databases and find themselves horrified by Access specific features like lookup fields that cause a break between the segregation of data and presentation. Or, one may approach Microsoft Access as singular product where it is treated as Excel on steroids. I am in the former school, so I was surprised when I looked up forms in the Access 2007 Missing Manual and found no mention of creating forms in the way I described in this video. The Missing Manual seems to take the latter approach to access, saying at one point that the use of association tables (junction tables, as they refer to them) is basically an old school holdover. Call me old fashioned.

In this video, Access 2007 Tutorial 3.1. Forms and Subforms:

• Form Wizard
• Form Navigation
• Creating a Form with Subform
• Adding a Row Source for Comboboxes
• Adding a Delete button
• Subform Properties

Access 2007 Tutorials Series 2: Importing and Querying Data

The Federal Elections Commission has an enormous amount of public data that we can download and manipulate in Access. I use this as an example in my classes because even though the data model is really simple (six data columns across two tables), there are many different ways to examine the data and at least one interesting little discovery in the data that is revealed at the end of the second video.

I think the availability of this data is a sticky issue. The purpose of campaign finance reform, as I understand it, is to add a layer of transparency to the funding of political campaigns. Obviously, money has a presence in the political process and that presence seems to outweigh the anonymous personal vote. Yet, often as not, it seems that the political candidates we are presented with are selected by the media and major party structures... structures that are hardly in any way transparent. I am personally part of an FEC dataset, just not this one, and I recall watching the television news on Super Tuesday and knowing how my candidate performed by looking at the percentages that weren't accounted for. Even though he placed third in one state and tied for second in another, the media refused to mention his name even while mentioning the names of candidates he displaced.

If you find this to be an interesting topic, be sure to watch the fantastic documentaries SPIN by Bryan Springer (as available on Google Video) and the documentary Ralph Nader: An Unreasonable Man.

In this video, Access 2007 Tutorial 2.1. Importing Data:

• Comma Delimited Files (CSV)
• Importing Data from Excel
• Import Spreadsheet Wizard
• Table Analyzer
• Lookup Fields versus Foreign Keys

In this video, Access 2007 Tutorial 2.2. Queries and Group By:

• Query Design
• Setting Query Criteria
• Using Grouping Functions:
  • Group By
  • Sum
  • Average
  • Count
• Hiding Criteria Fields
• SQL View

Access 2007 Database Tutorial 1.3. Many to Many Relationship

In this video:

• Limitations of One to Many Relationships
• Association Tables
• Deleting Relationships
• Deleting Fields
• Creating an Association Table
• Creating a Query
• Cartesian Products

Access 2007 Database Tutorial 1.2 – Tables and Relationships

This video shows the creation of a very simple Access database with a one to many relationship between authors and books.  The next video will show how this database falls short when you have multiple authors for the same book and how a many to many relationship can be simulated to solve this shortcoming.

The previous video in this series, not  yet created, will discuss why anyone would want to use Access over a program like Excel and talk about why you would use a  database, particularly a relational database.

Created using CamStudio 2.5b1, SoundBooth CS4, Premiere CS4, and After Effects CS4. Recorded at 1024x768, scaled to 640x480. Playing a little bit more with After Effects, using keyframes and easing to zoom in on the action.

In this video:

1. Creating a Database
2. Creating a Table
3. Primary Keys
4. Defining Fields
5. Entering Data
6. Foreign Keys
7. Creating a One to Many Relationship
8. Referential Integrity