DJ Dates

Access 2007 Database Tutorial Series 5: Importing Many to Many Data and Deleting Duplicate Data

In my Access class, I take the databases created by each of the students for the first assignment and combine these databases into one large database that they use for subsequent assignments. I do this by creating a single Excel worksheet that contains the contents of all of the student databases, and then import that worksheet into Access. The process is a little labor intensive, but it also makes each class unique because the data is always unique. Plus, I'm a big movie buff, so I like to keep an eye out for movies that people think are good. The first two videos of the series show this process. The third video shows how you might delete duplicate records, if you run into such a situation.

In this video, Access 2007 Tutorial 5.1. Many to Many Import:

• Importing Excel Data
• Skipping Fields
• Table Analyzer Wizard
• Creating an Association Table
• Correcting Information

In this video, Access 2007 Tutorial 5.2. Make Table and Append:

• Importing Data
• Unique Values/Distinct
• SQL View
• Make Table Query
• Append Query
• Joins

In this video, Access 2007 Tutorial 5.3. Deleting Duplicate Records:

• Unique Values
• Append Query
• Count and First
• SQL View
• In Criteria
• Delete Query

Access 2007 Database Tutorial 4.1. Reports

In this video, Access 2007 Tutorial 4.1. Reports:

• Report Wizard
• Design View
• Adding Totals
• Grouping Levels
• Reports based on Queries

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