Access « DJ Dates

26Jul/113

Access 2007 Tutorial 5.4. Inserting into multiple fields using a Combo box and NotInList Event

You can work along with this file:
database-with-forms.accdb

Code can be found at the end of the post.

In this video:

On Not In List event
Variables & Constants
msgbox()
if-else statement
Arrays
Split()
DoCMD.RunSQL
UBound()
Remaining Issues

When I teach my Access course, I often get students who feel that entering information into the database should be easier than it is, even with forms. Well, we can make it easier, but it usually requires some programming to do some work on behalf of the user. The more work we do, the less work the user has to do.

Filed under: Access Continue reading

8Feb/103

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

Filed under: Access 3 Comments

10Jan/100

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

Filed under: Access No Comments

26Nov/092

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

Filed under: Access 2 Comments

22Nov/090

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: