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
November 20th, 2010 - 16:24
Hum I will try this again..
Professor ..
Where do you teach.. my assumption is that you are a guest lecturer (does that mean part time) professor at Corning Community College? I am also a resident New Yorker (Endicott) now retired and am trying to follow your videos on handling many to many relationship. Even when working in systems years ago, I always had problems understanding this topic. Sometimes I generated reports via SAS, not knowing if I truly had captured the correct content. In any event, would it be possible to create a link on your website to the movie database mentioned in the videos. That way I could download this data and try to duplicate what you are doing, hoping that some of it would ‘stick’. Basically I am just trying to keep my mind a bit ‘engaged’ as I near my late sixties. Thanks in advance..
November 20th, 2010 - 17:31
I think every college has their own vocabulary for job titles. At CCC, Visiting means non-tenure track, Lecturer means part-time, and Professor is a higher rank, Visiting or not. For the moment, I am teaching part time while finishing my graduate thesis, which is actually related to a lot of the content on this blog.
An understanding of SQL is really helpful, if you want to get the most out of databases. Query By Example in Microsoft Access is a poor substitute. If you would like to learn more SQL, I would recommend http://www.sqlcourse.com.
Here is the data for the current running class, same structure as what is in the video but different content.
http://djdates.com/Source_Data_for_MN_data_import.fall2010.xlsx
Hope this helps.
November 22nd, 2010 - 13:36
Yes it does..
Again I would like to thank you for sharing your knowledge online with novices such as me. You have opened my eyes, in many ways, to the power of this simple (or so I thought) progrm called access.
In the future, I may not be able to perform ‘your magic”, but at the very least I will know the fault of the anaysis will not reside in the data I desire to investigae, but the me. Been only ‘playing’ with access for a couple of weeks, and I can see that I have much to learn. Thanks again…