ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 101: Beginner Database Concepts, Tables, Queries, Forms, Mailing Labels
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 
Courses - Microsoft Access 101
Description: Introduction to Microsoft Access
Running Time: 99 minutes
Pre-Requisites: Windows 101 recommended, Windows 102 or Windows 110 recommended, Excel 101 recommended, Word 101 helpful
Next Lesson: Access 102
Main Topics: Beginner Database Concepts, Tables, Queries, Forms, Mailing Labels
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 4/27/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 101
Introduction to Microsoft Access

Create a customer table, queries, customer form, mailing labels, and more. 99 Minutes.
 

AC101 Major Topics

  • Basic Database Concepts
  • Build a Customer Table
  • Create a Query to Limit Data
  • Design an Interactive Form
  • Generate Mailing Label Reports

If you want to learn how to build databases with Microsoft Access, you've come to the right place. Our instructor, Richard Rost, is one of the most knowledgeable Access developers you'll find. When he's not teaching Access, he's building databases for our clients worldwide.

Access is one of the most powerful tools available today for building databases of all sizes. It can be used for personal databases, running an entire small business, or even managing data on an enterprise level using a back-end such as SQL Server. Access can do it all.

We'll begin our course by covering all of the basic database terminology. We'll talk about the different components of a database: tables, queries, forms, reports, macros, modules, and so on. We'll spend some time teaching you how to plan your database on paper before starting to build it on the computer. This step is very important for proper database design.

 

We'll get started by building a database from scratch. We avoid using a lot of the wizards - we want to teach you how to build databases, not just have databases built for you with some wizard. We'll show you how to create your database file. We'll show you the different parts of Access and walk you through the interface briefly.

Our goal is to build a small business database to track customers, contacts, orders, products, and so on. Eventually this database will be a full-fledged contact manager. We'll add more to it in future Access courses. In today's class we will begin by creating the customer table. We'll talk about what tables are and how they work. We'll talk about the different fields you'd use in your customer table.

 

We'll talk about different data types (text, number, memo, etc.) We'll tell you why certain fields that are really numbers should be stored as text data types (like ZIP code). We'll learn about AutoNumbers and Primary Keys. We'll teach you how to save your tables with the right table name.

 

Next we'll add some sample data to our new customer table. We'll show you all of the right ways to enter data such as dates, notes, and so on.

 

When we're all done with tables we'll move on to building a customer Query. We'll show you how to construct Queries to limit your data based on certain criteria (such as, "show me a list of customers sorted by last name who are from New York.")

 

When we're done with Queries, we'll get into the fun stuff: building forms. Forms are a great way to build interactive windows-based databases that are easy for your end-users to work with.

 

We'll spend time teaching you how to create great-looking forms. We'll talk about labels, text boxes, check boxes, and so on. We'll show you how to format your forms, change the colors, resize objects, and so on.

 

Our goal is to teach you the skills you need to make professional-quality forms!

 

When we're done building our Customer form, we'll show you how to create mailing labels for your customers right in Access. Yes, for this we'll use the Label Wizard, but it's a good wizard (you don't want to lay labels out by hand).

 

We'll use the wizard to create easy-to-use mailing labels that are based right off our table.

 

Again, if you're really looking to learn Access, this is the perfect course to start with. We'll cover all of the fundamentals from concept to completion. We'll show you how to build tables, store your data, generate queries, construct forms, and make mailing labels. This course will teach anyone how to build a database - no prior knowledge of databases is assumed. Even if you have been using Access for a while, this is still a great course for learning the fundamentals - and how a database should be built, properly.

 

 

Access 101 Outline
 
1. Introduction
Welcome to Access 101
Important concepts

2. Terminology
What is a database
Database components (tables, queries, etc.)
Describe each component and it's function

3. Planning Your Database
Planning your database on paper
Plan for needed tables
Plan for needed fields in each table

4. Getting Started
Starting Access
Parts of the interface
Creating a blank new database

5. Customer Table, Part 1
Creating a customer table using design view
Creating the FirstName field

6. Customer Table, Part 2
Brief description of basic data types (text, memo, etc.)

7. Customer Table, Part 3
Adding additional fields to your table (phone, address. etc.)
Brief description of basic field sizes (long int, decimal)

8. Customer Table, Part 4
Creating a CustomerID Autonumber
Moving fields in order
Saving your table
Setting the primary key

9. Entering Data
Adding data to your table
Deleting records

10. Customer Query
Building a query in design view
Adding fields to your query
Sorting your query (LastName, FirstName)
Moving fields in your query
Using criteria (e.g. show all customers from NY)

11. Customer Form
Building a form using design view
Selecting a data source
Adding fields to your form using the field list
Lining up your controls
Switching to Form View and back to Design View
Using navigation buttons to navigate records
Moving controls with the Hand
Selecting a block of controls
Deleting controls
Trick to add all controls from the field list to the form
Saving your form

12. Form Formatting
Changing colors of your form and objects
Deleting a label, keeping the text box
Editing your label text
Resizing a text box
Moving a block of controls together
Moving a label independent of it's text box
Resizing your form
Object borders and special effects
Deleting a record from the form

13. Mailing Labels
Using the mailing label wizard
Desigining a prototype label
Report Print Preview
Zooming in and out
 
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 101

Richard on 1/1/2007:  Getting started with Microsoft Access? This course covers all of the basics. Create a customer table, queries, a customer form, and customer mailing labels, printing, more.
Richard Rost on 10/12/2007: Sorry folks, my email address 'richard@myolp.com' is no longer valid. Spammers destroyed that one. If you need to contact me, post a message here, or use the contact form on the web site: www.599cd.com/contact. If you do need to email me, make sure to include [599CD] in the subject line (yes, include the brackets).
John on 10/17/2007: I took Access 101 and I finally got it!

I want to make a database for a school of 190 students. I know I can do it after a few more lessons.

However, I need to know if it is possible to make Access 2003 forms larger and the font larger. I think the clerical people at the school will have trouble using the small print. The resolution on the computer I am using is 1024 x 768. Do I have to change the resolution, or is there a better solution?

Thanks

Richard Rost on 10/17/2007: John, all you have to do is increase the dimensions of your form by dragging the borders out. You can then resize all of the elements on the form (text boxes, labels, etc.) and change their font sizes. We cover a lot more with formatting forms over the next couple of classes.
Eric Valencia on 10/23/2007: Hey I advertise on Craigslist and want to catalog all my competition's ads in access (including the html of the ads). Is this the right program to do this in? Access?
Richard Rost on 10/23/2007: Sure, you can catalog anything in Access, it just depends on what format you're getting the data in. I'm assuming you're typing it in yourself and then just copy-n-pasting the HTML?
Virgil DISSMEYER on 11/19/2007: Richard some time ago you told me how to enlarge the small screen used by Amicron player. I don't like the small window and lost your instructions.
Richard Rost on 11/21/2007: Virgil, just click on the "EXTERNAL" checkbox inside the player and it will force the videos to open in Windows Media Player. I'm working on a solution to allow you to resize the videos directly inside the player.
Carol Case on 11/30/2007: Excellent class. I plan to order more. Will a future class tell me how to password protect a database? I would like to create a db and publish it on a website so users in many locations could access and edit the data. I would like to password protect it and give the pw to a select group of users. I would also need to be able to change the pw if a user left the group.
Naveed Ahmad on 11/30/2007: I really like the course and most likely i loved the way he teahes the lessons.I have a question , is there any way i can buy the whole access bunddle and it will save me money as i would like to go for it rether than buy it seperatly.
Thanks
Naveed

Richard Rost on 12/9/2007: I cover real basic database security (setting a single db password and creating an MDE file) in Access 307. I have not yet covered REAL database security because it changes in every version. I'll probably make a single class coming up soon that will cover security for Access 2002, 2003, and 2007 databases.

Now, as far as making an online database, I strongly recommend building it using Active Server Pages. I have a whole Web Database Seminar available on how this works.

Richard Rost on 12/9/2007: If you select multiple courses, our shopping cart will automatically gift you a quantity discount starting with 2 items. The more you buy, the higher your discount. This is in place of (and actually better than) the "Combo" packages we used to have. Just select all the courses you need, and the more you buy at once, the more you'll save.
 IC on 1/20/2008: Have been trying to understand ms Access but there s still lots of confusing areas.what do I do or do you have a simplified example for me to follow?
Thanx
isaac

 Kamuste on 1/23/2008: Hi sir,
Can i complete and master acess without taking visual basics and java courses

Richard Rost on 1/23/2008: Well, my Access 101 class is the perfect place to start. It's got step-by-step videos and a sample database for you to work with. You can't get any more basic than that. Of course, if you have specific Access questions, post them here and I'll do my best to help you.
Richard Rost on 1/23/2008: Yes, you can. Microsoft Access is a stand-alone program, and you don't NEED to know Visual Basic or Java (or any other programming language) to use it. In fact, after taking my Access 100-level series of courses (101-104) you'll be able to develop a perfect working database without ANY programming. Thru the 200-level series, I teach you more power-user techniques, but still you don't need programming. I'll begin touching on Visual Basic for Access in AC301, but again you don't NEED to go that far to have a great understanding of Access.
Fran Raymond on 2/9/2008: Hi Richard, you strongly recommended that we not use spaces in the FieldName. I have a table in which i have used all kinds of spaces in the FieldName. (I did this befoe i took your course! ) This table is related to a number of other tables in my database. My question is this: Is there a way for me to safely eliminate all the spaces in my Field Names and not screw up all my relationships?
 dreamer on 2/14/2008: thanks richard..i had learn basic microsoft access through this tutorial.
JAMES STANZIONE on 2/26/2008: can I use a date as the primary key?
Richard Rost on 2/28/2008: There's nothing in Access to stop you from using a date as a primary key. Personally, I wouldn't do it because it's a bit sloppy, but you can if you want. Just be careful because different computers might be set to different regional date formats. Take 4/5/08 for example. You might see this as April 5th. Someone in Europe would see May 4th. If I were to use a date as a key field, I would make a custom format like this: YYYYMMDDHHNNSS. That way my date is accurate down to the second, and this format is nearly universal. The only problem you have then is what if two records are added by users on a network at the exact same moment? Will Access handle it? I'd be curious to see (no, I'm not going to set it up and test it right now). You could also run into problems around Daylight Savings Time switchover... all of the sudden it's 1:00 AM again and you're running into tons of duplicate values! I personally prefer and recommend using AutoNumbers for primary keys. That's basically what they're in Access for.
Richard Rost on 2/28/2008: In Access XP and higher, Access should automatically keep your relationships OK as long as you are certain to rename the fields in both tables to the same thing. MAKE SURE you BACKUP your database before trying it though. If you have GLOBAL relationships, Access might not let you change the fields until you remove the relationships, however.
Richard Rost on 2/28/2008: You're welcome.
 Terry on 3/4/2008: Great class Richard, I'm sure I'll be visiting your site again. I've always had Access questions and other tutorial information was extremely long. I really appreciate your help. I believe you've made my Jobs a bit easier. I actually have the 2007 version so finding some of the keys/buttons is difficult do you plan to show in '07 version too. Have a great one.
 Gonas on 3/5/2008: This microsoft access you can do programs with it.
But if you want to make games what program should i use.If u know tell me something.

Richard Rost on 3/13/2008: Terry, I'll be working on Access 2007 upgrades very soon. First will be a seminar for people transitioning from Access 2000/2002/XP/2003 to 2007. The learning curve is HUGE even for me - everything's in a different place. Next will be a new series of beginner tutorials for NEW Access 2007 students.
Richard Rost on 3/13/2008: If you mean games involving GRAPHICS and things that move around on the screen, I would recommend learning Visual Basic first. See www.599cd.com/VB for a beginner VB course. Most professional games are developed in the C/C++ language. I used to teach C years ago, but I haven't had any demand for it lately.
 Rajneesh on 3/30/2008: It is recommeded in some other tutorials (e.g. Allen Browne) that the data should always be added through forms and never directly into tables.
Is it just one of their styles or something else?

Richard Rost on 4/1/2008: I agree with that - mostly. The point is that you don't want your END USERS having direct access to your tables. For one thing, aside from real simple table-level data validation, you really have no control over what they do to your table. Unless you set up user-level security you can't, for example, prevent them from deleting bunches of records. So I would mostly agree that you should have your users always access data through FORMS.
 Derrick on 4/19/2008: I have designed a database for tracking marine personnel and generating specific forms, etc. I need some info on actually synchronizing a database on the ship with a master database ashore. Replication seems to be the way to go, but I need some better info than I can find in the manuals.
 paul casaclang on 4/21/2008: this is really GREAT stuff sir...thanks so much to people like you!!!
Richard Rost on 4/21/2008: A REPLICA is probably what you need in this situation. For everyone else, a REPLICA is when you make a copy of your Access database that you can take on the road with you, or send to a different location that doesn't have Internet access. Any changes made to the Replica can then be merged back into the main database when you are able to bring them together (copy on a CD, for example). If you're out on a ship and you need access to the database, you can use a Replica, and then when you're back to shore, synchronize the two together. Unfortunately, this is not an easy process, and I don't have a tutorial available for it YET. I will be covering it eventually, but in my mountains of Access tutorials, I don't have anything for Replication as of right now. If this is urgent for you (and if anyone out there reading this wants to learn how to do this too), let me know and perhaps I can throw together a quick mini-lesson in how to do it. I've used Replicas before. I made a database that was designed for sales agents "in the field" to carry on their laptops. They would enter sales and contact info, and then at the end of the week when they went back to the office, merge them all together. The problem is that resolving conflicts is NOT always easy. If you change JOE SMITH and I change JOE SMITH and then we try to merge them, someone's data goes bye-bye.
Richard Rost on 4/21/2008: Paul, thank you very much for your compliments.
Richard on 4/24/2008: No, you cannot change the BACKGROUND color of a checkbox. You can only change its border color, style, and "special effect." We'll talk about those more in future lessons.
1littlechickie on 5/9/2008: Regarding Access 101 lesson 9 time index 6:50...
I have set up my customers in CustomersT with the customer number being the key and auto number. First record comes out number one, but when I put in the second record it comes up 5! I don't know what to do. Thanks, 1LC

Randy Sims on 5/20/2008: Deleting cut records? My table and forms have different "ID's",AutoNumber. I think it started when I cut a few records from the table.
Richard Rost on 5/24/2008: 1LC, this is a common question I get all the time with regards to AutoNumbers. You put one record in and it comes up "1" and then some time later you add another one and it's "5". Why is this?

Well, somewhere along the line you inserted 2, 3, and 4, and then perhaps DELETED them. Access will NOT reassign them again. They're gone. That's why the next record is 5. You see, once an AutoNumber is used, it can NEVER* be reused. This is to maintain integrity in your database. Imagine if you added 5 customers, and each of them have orders in your system. Customer 2 dies so you delete his record (which you shouldn't do, but let's say you did). Now, you add another customer and he gets assigned ID 2. Well, he now just inherited all of the orders for the old dead customer because you forgot to delete all of those too - which, again, you shouldn't do... but this is just an example of WHY AutoNumbers aren't reused.

The good news is that it shouldn't matter. You see, the AutoNumber ID is not for you. It's for THE DATABASE to keep track of unique records. You'll use it later when you get into relating two tables together in my Access 201 class. If you want your OWN number to assign to customers for vanity purposes, then set up your own CustomerNumber system. Don't rely on the ID field. It's being assigned by Access, you just have to deal with the number you're given.

* Now, I should mention for the tech-geeks that read this, that YES you can make Access reassign some of the AutoNumbers with a compact and repair. This is way beyond my Access 101 students, but if let's say you have customers 1 through 5 in the database, and then you delete 3 thru 5 and you want to get 3,4, and 5 back as IDs. If you compact and repair your database, the next ID assigned should be 3. Again, you shouldn't worry too much about what IDs Access is assigning. They're not for YOU. :)

Richard Rost on 5/24/2008: Randy, if your form is BOUND to a table, then it would be impossible to have different IDs than the table has... unless I'm not understanding your question properly. Could you please elaborate?
ted hayes on 5/28/2008: Access 97 used to let me package a db essentially as a separate program. I believe it was an .mde extension. User did not have to have a copy of Access.
I got away from programming for a few years, and now we have .accdb's. Is there a way to package a current version db as a "stand alone" program?

Willis on 5/29/2008: I am trying to get two dates to calculate. I watched your video on date and time. That showed more about time. I am trying to use the current date function to calculate a date of birth 28 May 08 - 24 Jul 66 which at this time should be 41 years old. I hope that my question make sense.
 Wiley Redding on 5/29/2008: Would like to incorporate a self-generating confirmation number macro. How do I build such a macro.
shykh Ahmed on 6/7/2008: is this the same as Access 2007"
Richard Rost on 6/13/2008: Ted, an MDE is not a standalone program, but rather an Encrypted ("E") version of your Microsoft Database ("MD"). You can however download a tool from Microsoft's web site called the "Microsoft Access 2007 Developer Extensions" which will let you take your AC2007 database and distribute it. This tool is free, unlike previous versions. Here's the link.
Richard Rost on 6/13/2008: Willis, the easiest way to calculate someone's AGE is to simply subtract the two dates (Now-DOB). Now, simply divide that result by 365 and you get a "close enough" age in years. If you need anything more accurate than that, it involves some programming (which I can cover in a future lesson if anyone REALLY needs to see it).
Richard Rost on 6/13/2008: Wiley, you could use an AUTONUMBER field and just set it to RANDOM instead of INCREMENT. That's the easiest way to generate a random number without bounds. Of course, then you can't use a standard AutoNumber for your ID field. Anything more is going to require some programming and the use of the Random function. Ask me about it when you get to the 300-level classes. :)
Richard Rost on 6/13/2008: Shykh, Access 2007 has changed a lot since 2003. The database itself works just about the same, but the interface has undergone a huge facelift. I will be covering the differences in an upcoming lesson.
Kelly Little on 6/26/2008: Why should I not put spaces when entering phone numbers? The problem with this is, when I pull the database into excel, then there are no dashes or spaces and makes the phone # difficult to read. Thanks!
Richard Rost on 7/1/2008: Kelly, I recommend NOT including dashes or spaces for various reasons (some of which I go over in the video) but mostly because it keeps your format STANDARD. You don't have some with dashs, some without, some with dots, etc. If you only allow DIGITS to be entered, it keeps the data homogenous. Now, if you're having problems READING the phone number on a form or report, remember you can always use a FORMAT to display the number, like ###-###-####.
 Tracey on 7/3/2008: If I've updated my customer list since the time the Mailing Labels report was created, do I need to update this report in some way, or does Access do this automatically?
Richard Rost on 7/22/2008: Tracey, if you've added any fields, you will need to also add them to your mailing label report. If you've just CHANGED the name of any of your fields, Access MIGHT have picked up the change for you automatically, but it's best to check.
Sri Nair on 8/3/2008: Richard, that was GREAT.
 Jeanie on 8/20/2008: How do I bring in more than one table to link together.I understand the link part, but I can not bring in my second table.
Elia Gore on 8/31/2008: Job well done Richard.I have leaned a lot since I enrolled in your affordable well structured,and well deliverd computer courses.I am looking forward to my favourite Microsoft Publisher 2003 courses.God bless you
Elia Gore

Richard Rost on 9/8/2008: Jeanie, linking tables is a concept covered in Access 201. It's a bit too advanced for Access 101. You have to link tables in a QUERY. When you FIRST go to create your query, you can select multiple tables, or click on the Add Table button. See Access 201 for more help.
 raffai bardin on 9/9/2008: Dear Rost
How refresh my form after data entry?

Richard Rost on 9/10/2008: Raffai, when you move to a different record or close the form, your data is saved to the underlying table.

If you want to MANUALLY save the data (or refresh other information on the form, like calculations) then you can create a command button using the Command Button Wizard to do this.

The two commands you might want to use are under "Form Operations > Refresh Form Data" and "Record Operations > Save Data" depending on what you want to do.

I cover the Command Button Wizard in Access 102, Lesson 6: Forms.

 Matt B. on 10/8/2008: Great chapters so far, "evil wizards" hee hee. I want to learn how to let the user of a form choose a customer, and then have the other customer data fill in from the customers table.
Richard Rost on 10/15/2008: Matt, you can certainly do that, but it's WAY beyond Access 101. It involves some programming in VBA code. You need to use something called a DLOOKUP function and an AfterUpdate event, which I cover in much later classes. See this free tutorial for an idea.
Kelwyn on 11/6/2008: Thanks for a very educational session, Richard. I'm moving on immediately to Access 102. You've earned yourself a truly long-term customer.
Richard Rost on 11/7/2008: Kelwyn, thank you for the compliments.
Rich Castillo on 11/11/2008: I'm using Access 2007 on Microsoft Office 2008 (absolute beginner) and the ledger is completely different. I can follow part of your lesson plan, but it diverges enough to not follow through the entire lesson. What do you suggest?
Richard Rost on 11/14/2008: Rich, just hang in there. I'll have Access 2007 lessons out very soon.
 javed Farooq on 11/14/2008: Excelant Teaching...
 Ashley on 12/12/2008: Um...
Yeah, these tutorilas rock.
Thanks for the hard work.
(Well to us it was hard, for you probably not so much.)

 Lisa on 12/18/2008: Great teaching! I am learning very quickly!
 Analyst3 on 1/16/2009: Thank you sooooo much
You are a gifted teacher,will definitely purchase the rest

Waqas AHmed on 1/19/2009: great Job Richard
I have learnt a lot from your course.WIll definately be moving on from here to next level.
Keep it up.

Deborah Glenn on 1/26/2009: You are an excellent teacher! You make learning fun again.
Bijaya on 2/11/2009: Every time I try to add Command Button in the form using wizard it hanged at either clicking "Finish" button or "cancel" button.

Week before it was working fine. I am using Access 2007

 Lisa on 2/12/2009: These tutorials have made me very popular at work because now everyone comes to me for info. I might even get a promotion!
Richard Rost on 2/12/2009: Bijaya, this could be due to any number of things... I would first try reinstalling Office. Have you installed any other programs between the time that this worked and stopped working? Could be a conflict.
Richard Rost on 2/12/2009: Lisa, that's awesome. Make sure you name-drop 599CD.com around the office then! :)
 saba on 2/15/2009: I really enjoyed the lectures but actually i wanted to learn about the replationships between different tables.n u haven't told anythng about that in your lectures.
Sam King on 2/15/2009: Fantastic tutorial!
Wondering if there is a simple way to attach a time stamp to a form so that every time a change is made to a data entry the time and date of the change is noted and shown on the form. Thanks for the help!

Sam King

Richard Rost on 2/18/2009: Saba, relationships between tables in an "intermediate" topic that I start covering in Access 201.
Richard Rost on 2/18/2009: Sam, this is certainly possible with a little bit of programming. You'd need to learn how to create an "AfterUpdate Event" which is something I start covering in the 300-advanced series. Also see this free tips video: http://599cd.com/tips/access/afterupdate-event-vba
 Linda Stern on 2/19/2009: Thank you for the review. I have a new job and did not remember a number of things in access.
Mark Bennett on 3/8/2009: I am putting together a table to collect answers to questions. Some questions have text answers, others are scored according to the response (Yes=5, No=0, etc.). Should I set up 2 different types of questions categories or can these be in the same category?
Richard Rost on 3/9/2009: There's nothing wrong with having both types of answers available in the same table. You could have AnswerID, AnswerNumeric, AnswerText as three fields for the same record, and then store the appropriate response in the field it needs to go in. You also *could* store a numeric answer in a text field. It all depends on how you want to implement it.
 Nutty on 3/13/2009: Hello,sir. How do use clock times to display on the form when the user opens the form?

 Nutty on 3/13/2009: If you have tables, ex: customer table, and invoice table which contain the same field names that means you have to enter data again in the table, isn't there a way to get the other table updated if it is the same information without having to enter many times?
Richard Rost on 3/13/2009: You want to display the current time on the form? I'm assuming you want to just default a specific field to the current time? You can do this by setting the DefaultValue property of the box (or table field) to:

=Now()

This is covered in depth in Access 104.

Richard Rost on 3/13/2009: Nutty, yes, of course. This is the whole point of a relational database. I begin talking about this in Access 201.
 Jack Zentmeyer on 3/15/2009: I'm getting so much out of your tutorials! Can you tell me what course talks about linking/importing files into Access? Thank you!
Richard Rost on 3/15/2009: Jack, that all depends on what kind of files you're importing. I show you how to import data from plain text files, Excel sheets, and other Access databases in Access 308.
Paul Logue on 3/30/2009: Great presentation ...knew 98%..... still looking for info on using photos in Access.. Hopefully 102 will help ..
 Lynn Robbins on 6/21/2009: Any Plans To Offer training for current Microsoft Access
I need it

Richard Bogen on 6/21/2009: I've deleted some records, but the autonumber field doesn't change so that now there are gaps in the sequence. Is there anyway
to renumber the records?

Richard Bogen on 6/21/2009: I changed a field from integer to decimal. When I enter a number like 2.5 it still shows as 2.
Richard Rost on 6/25/2009: Yes, of course! In fact, I'm working on Access 2007 tutorials now.
Richard Rost on 6/25/2009: Richard, you can't reuse AutoNumbers. Those are numbers that Access uses for internal purposes. The only exception is that if you delete a bunch of numbers at the END of your table, you can COMPACT the database to get those back - but gaps inside other numbers, nope. Can't do it. See this post I made earlier for details.

If you ever need the ability to renumber your numbers, then you should use your OWN numbering field instead of an Autonumber. I have a free tutorial that explains how to do it.

Richard Rost on 6/25/2009: Richard, check the number of DECIMAL PLACES (field property) and make sure it's set to 1 or 2 (however many you need). If it's set to 0 it will not show any decimals. I've also noticed that in some versions of Access if you switch from Int to Decimal like that, the AUTO setting somehow gets messed up. Manually change it to 1 or 2 and you should be OK.
Brandon on 6/30/2009: How can you add information to the form from more than one table or query?
Richard Rost on 7/1/2009: Brandon, to have data from more than one table, this involves setting up relationships between the tables. I don't cover this until Access 201.
Richard Bogen on 7/6/2009: I have created a memo field, but when I enter text into it for a particular record the same text appears in every record.
Richard Rost on 7/7/2009: Richard, this can happen if your control (memo field) isn't BOUND to a field. Open its properties and make sure it has a CONTROL SOURCE. This is the field in your table that the control will save the data to. I cover this in more detail in upcoming lessons.
 Carolyn Quinn on 7/15/2009: Based on the information you have provided, i think i need to start my database all over. :(
Richard Rost on 7/16/2009: Carolyn, I've found that with a LOT of my clients, it's generally EASIER and QUICKER to rebuild the database from scratch than to try to fix a database that was poorly designed from the start. The more you learn, the more you study databases (Access in particular) the more times you're going to say, "darn, I wish I would have known this before I started building this database!" I would strongly suggest taking my 101 through 202 classes before STARTING a new database project, because those classes really lay down the fundamentals of HOW to build a database. AC201 is especially important because it teaches you about RELATIONSHIPS between tables.
Shane Tungate on 7/17/2009: Well i am hoping that this is going to work for me. I am hoping that these coarses will teach me to use access so i can develope a PLANT HIRE CONTROL database for work, as they will not purchase the required software. Ive developed a system i use in Excel but it is way to long winded so here goes. Wish me luck
Carolyn Quinn on 7/17/2009: Shykh - you will love Access 2007 once you understand the differences. It is very cool!
Carolyn Quinn on 7/17/2009: How do i enlarge the small screen when looking at the videos. I wear glasses man!!! Thanks
 Carolyn Quinn on 7/17/2009: Tables - When it comes to tables, i know i need to normalize the data. I realy have to know all about relationships. How many tables do i have to have or can the they go on for ever? So far i am looking at at least o9 tables in a database i must build - Region, division, class, DVP or DirectorVP, State, city, SAP or Cost Center Number, Acct Name, this could go on for every. I am going crazy because i am afraid of doing the relationships right.
Richard Rost on 7/21/2009: Shane, good luck. If you have any questions while you're working on your database, be sure to post them here.
Richard Rost on 7/21/2009: Carolyn, see this post on how to make the videos larger.
Richard Rost on 7/21/2009: Carolyn, there is no limit to the number of tables that I'm aware of. The maximum size of an Access database is 2 GB, but you can extend even that by linking to other database files. Yes, good databases will often have LOTS of tables. And remember, they don't ALL have to relate to each other.
Srinath Nandyal on 7/22/2009: I know I am late in offering my comment to "1littlechickie" question. Here it is. I found out that there is serious bug in Access. As soon as you tab from the FIRST autonumber, save the file and you will see it will maintain autonumber sequence properly. If you fail to save after the first tabbing and say you tab to 3 more adjacent fields, Access increments the autonumber for each tab. Hope this helps
 lucy on 7/25/2009: hi, which one of your lessons would I need to watch to see how to make a query/form where the user sets the parameters? I want the user to enter the surname and forename and the database to return the customer details. Thanks
Richard Rost on 7/27/2009: Lucy, I cover this in Access 102. It's called a PARAMETER QUERY.
 Charlene on 7/30/2009: Hi Richard, this was GREAT! I currently use a db which was designed by someone else, but I look forward to building my first to support other aspects of our business needs. I will visit here often!!!! Great job! From Victor, NY :-)
 Paul Bramer on 8/17/2009: Richard, you are a really good teacher! Thank you. PB
 Red on 9/27/2009: Hi Richard,
Can I get this 599 cd theater on my job/at work?? I work for a state agency and wanted to know if I could learn the lessons online at work?? thanks.

Richard Rost on 9/28/2009: You should be able to use the 599CD Theater at work. It works just like any normal web site, and you don't have to download or install anything.
Lynn Robbins on 9/30/2009: In Access how do I delete a Table I do not want??
Also for Acess 2007

Richard Rost on 9/30/2009: The easiest way is to just right-click on the table and select DELETE.
 Lynn Robbins on 9/30/2009: How do I delete a Data base??
Richard Rost on 10/1/2009: Lynn, a database is a file just like any other file on your hard drive. Just open up Windows Explorer or My Computer and look for the MDB file (or ACCDB file for Access 2007).
Scott Rusinko on 10/18/2009: CAN ONE TABLE COVER SAY INVENTORY OF MUSIC AND MOVIES
Richard Rost on 10/18/2009: Sure, you could use a generic "Media" type to store both. I don't see why not.
 Darlene on 1/8/2010: How do I lock data in the table?

Richard Rost on 1/15/2010: Darlene, this is too complicated for Access 101. I do talk more about database security and locking records in future classes. In fact, I have a whole seminar just on database security.
HARRY RUTHERFORD on 5/6/2010: if you use ZIP LIKE 24260-1150 CAN YOU SORT BY THE FIRST FIVE

Reply from Richard Rost:

Harry, yes you can do this, but it's beyond the scope of Access 101. You need to use a special function to read just the first five characters, called LEFT(). I cover this in Access 222.

Peter Ward on 7/20/2010: can I start the autonumber field from BP0001 and increment from there?

Reply from Richard Rost:

Nope. Your AutoNumber will start at 1 and Access will increment it for you automatically. You have NO control over it... and you shouldn't use it for anything. It's an internal number that Access uses for tracking relationships between tables (which we'll learn in Access 201). You can create your own custom counter fields, but that will require a little programming... and we cover that in the 300 series.

Deborrah Cisneros on 7/31/2010: How do you know when to create another table for the information vs. putting all the information in one table?

Reply from Richard Rost:

This is a very good question. I could easily spend an hour discussing this topic - called "normalization." USUALLY when you have information that's going to be DUPLICATED amongst multiple records, you want to spawn that off into it's own table. For example, if you're tracking orders and you have repeated customer information (name, address, etc.) on multiple orders you would USUALLY want to split that off into its own table (a customer table). There are exceptions (which I talk about in my classes). I go into a lot more detail on this topic once we get into multiple-table databases, starting with Access 201.

  on 9/30/2010: I saw your comments about TAB CONTROLS -- I, too, HATE them...
Fred on 10/3/2010: My database is about soccer players playing in 6 different league ( spain,england,france,italy,germany and USA)It's almost impossible to write each name of the players and their infos, so is there a possible way to make this easier?
 JJ on 11/6/2010: Can you use letters in "Auto Numbers" Like start a ID with "A" then "B" so on and so on? When you get to "Z" the next number will be 1???

Reply from Richard Rost:

Nope. AutoNumbers can ONLY be numbers, and they can ONLY be assigned by Access. You have no control over them. If you want to create your own CUSTOM numbering system, you can, but it will involve programming, as seen in this Tip.

 LFlakes on 12/5/2010: Can't view the video only hear sound.

Reply from Richard Rost:

Make sure you have the latest version of Windows Media Player. If you still have problems, try switching to the Flash player.

Essaid on 12/17/2010: Hi,
I´m wondering what kind of courses am I going to take.I would like to do professional databases with graphes and so on I would of course like to automate the daily routines activities.
I appreciate an answer.
Thanks.

Reply from Richard Rost:

If you want to work with databases, you definitely want to start with Access 101.

 Jan P on 1/9/2011: How do you allow the form to lookup and select and the info flows into the form?


Reply from Richard Rost:

Jan, you can control what information appears in the form by using a QUERY. You set your criteria in the query which limits the records that are displayed. Then, you can use the QUERY as the record source (instead of a table) when building the form.

 Colleen Valdez on 1/9/2011: I apologize, but I am not sure which lesson you covered column titles under. I understand about not using spaces in the column titles, but can an underscore be used instead to separate words in titles of columns?

Reply from Richard Rost:

Colleen, they're not "column titles." They're called FIELD NAMES in Access. Columns are what we call them in Excel. Access has Fields and Records. Excel has Columns and Rows. :)

Sure, you can use the underscore character. And remember, you CAN use spaces in your field and table names. I just prefer that you DON'T. You'll thank me when you get into advanced lessons.

I prefer to just capitalize different words in my field names, like FirstName. But you can certainly use First_Name if you want to.



Colleen Valdez on 1/11/2011: Thank you for clarifying the verbiage between Excel & Access (something I need to make a habit). Request clarification on your response. As you state you CAN use spaces or underscores, but was wondering if underscores alleviate the issue that spaces cause later in the advanced courses. Or is the underscore as problematic as the space?

Reply from Richard Rost:

The underscore DOES alleviate the problem. In a nutshell, the reason why I strongly urge AGAINST using spaces is because it becomes a nightmare later on if you get into advanced topics like writing your own SQL statements or VBA programming. For example, to refer to a field on an open form, you can just say:

MyValue = Forms!FormName!FieldName

However, if you have spaces in your form and field names, this has to become:

MyValue = Forms![Form Name]![Field Name]

You have to remember to put square brackets around EVERYTHING which does become a pain. Same thing with SQL statements:

SELECT * FROM CustomerTable WHERE CustomerID=5

becomes...

SELECT * FROM [Customer Table] WHERE [Customer ID]=5

So it's just MUCH easier to teach people in the beginning NOT to use spaces. But yes, to answer your question, underscores are just fine. This is perfectly valid:

MyValue = Forms!Customer_Form!First_Name

Hope this helps.

Steve Henderson on 2/8/2011: In your tutorial on your seminar on Work Orders you showed a combo box where you selected an item from one db and it was placed in the order db. Which of your lessons covers this type of function?

Reply from Richard Rost:

By "db" do you mean "table?" The whole concept of a combo box is to select an item from one TABLE and store it's ID in another table. For example, choosing a customer from your list of customers (Customer table) and storing that as the customer ID in an Order Table. This is covered in Access 201 which is the foundational class on relationships in your database.

Kim on 2/8/2011: I am interested in purchasing additional sessions. Having just purchased 101 and finding it is a tutorial for version 2003 I am curious when the tutorials switch over to 2007 version. Thanks

Reply from Richard Rost:

I'm working on new 2007 and 2010 tutorials right now. Any 2003 lessons that you purchase right now will qualify you for FREE upgrades to the 2007/2010 versions as soon as they're released. Click here for details.

 Orlando on 3/23/2011: Hi Richard,
Do you have a searchable "index" of access 100's and 200's subject matter covered. The reason being that I find it hard to pinpoint specific topics after the fact. Thanks. Great courses!

Reply from Richard Rost:

Yes, there is a searchable online index available for most of my classes. Access is here. Plus you can use the big search box at the top of every page on my site.

 Shane on 4/4/2011: Hi Richard,

I read your post about the problems with creating a form before the table. You said: "First, you have to open up the FORM PROPERTIES. Double-click on the square where the two rulerbars meet. This will open up the form properties. Now set the RECORD SOURCE property to your new table. Now you've bound the table to the form.

Now you have to go to EACH CONTROL (text box, check box, etc.) on your form and set the CONTROL SOURCE property to the matching field in your table."

What else do I need to be concerned about? (field types, how does the form update the table, other issues?) Thanks for your patience and help!

Reply from Richard Rost:

Once you set the form's control source, and the control sources of all of the objects on the form, they're bound to the table. You don't have to do anything else to save records to the table. Once again, that's why it's better to build the table FIRST.

Kelwyn Suite on 4/25/2011: Hi, where can I locate the database used in this lesson? It's not on my hard drive.


Reply from Richard Rost:

All of the student databases were moved here.

Cameron Eller on 5/10/2011: is there anyway to expand the viewer to full screen?

Reply from Richard Rost:

If you're using my Video Player software, click on the EXTERNAL box. When you play a video it will then launch in the external Windows Media Player viewer which you can resize as much as you want.

Alex Hedley on 5/10/2011: Hi Cameron,
If you double click on the video whilst in the 599CD Video Player the video will go to full screen,
there isn't the option to resize as you would have in External mode but it allows the videos to play in sequence still,
Alex

Wei on 5/14/2011: Every time I type something in a field it always shifts to another language, and I have to change it back to english every single time, how do I set english as the primary data entry language? thanks.

Reply from Richard Rost:

I've never seen this happen before, and I do have to admit I'm not the greatest when it comes to multi-language support in Office. I've only used English myself in the past. What version of Access are you using?

 Buba on 6/16/2011: Hello Richard i want know the different between microsoft Access 2003 and 2007.
 arif naseem on 6/16/2011: Its really a nice and helping task.I appreciate it a lot.may you have more time to help humanity.
eddy geijselaers on 9/16/2011: Hi Rick

On the subject of labels; how about a sheet of labels that is not (new) complete. How to skip a number of labels for starting. Otherwise we are left with a lot of partial sheets. Maybe start with a number of "blanc" labels?

Milo Hughes on 11/19/2011: Access 101 time incdx 1:04
None of the Title bar or other demonstrated areas are the same in my Access 2007 !
I had to shut down Access while processing the instructions and could not get back to the proper screen.
The only way to access the starting table tools was to erase the original and start over from scratch.

May be if the instruction video showed the modern, not just slight differences it could help.

Reply from Richard Rost:

Yep - this tutorial is for Access 2000 to 2003. Access 2007 has a radically different menu interface. You'll be better off with the new Access 2010 tutorial that will be out in a couple of days. Access 2007 and 2010 are very similar.

Gabriel Riddell on 11/21/2011: I'm making a database for characters in a game I'm making, and I want to be able to set up a search so I can find each character easily. How can I do That?

Reply from Richard Rost:

You can always just click on the SEARCH button to find a record.

I would create a List Form like I do in Access 103. This will show you a list of all of your characters and then you can click on one of them to open up the form for just that character.

 subramanian on 12/25/2011: when can i learn access forms and queries.
 Sattar Rahimi on 12/31/2011: Hello Mr. Rost. I have been trying to do this in Access for a long time but have failed every time. I want to link a form which is built either in Word, Excel or PDF with Access. We have a form in PDF and I want the personnel information to be printed in that form. Is there any way we could do that? Thank you very much.
 kim on 1/3/2012: Great Class Richard. You do a very good job explaining and showing how to get the job done. I enjoyed it very much.

Reply from Richard Rost:

Thank you.

 LadyPurple on 1/17/2012: I am starting a database and wanted to feel assured that I am starting correctly. So here we go.
I plan to have an employee table to consist of PersonnelID (Primary Key), First Name, Last Name, Address, City State, Zip, HPhone, CPhone, Years worked, HireDate, Dept, Term Date, Hrly Rate, Work Status (Full or Part time), Manager ID (Foreign Key. Next Table - Paid Time Off Table - TimeOffID (Auto/Primary), PID (Foreign Key for PersonnelID), PTO Available, PTO taken, Date Taken
Occurrence Table - OccurrenceID (Auto/Primary), PID(foreign key for PersonnelID), Occurence Type, Occurrence Date
Last Table is for Wellness, WellnessID (Primary/Auto), Wellness Earned, Month Earned, Wellness Taken, Date Taken, PID(Foreign key for Personnel)
Later I plan to set up queries run and/or create reports from this.
I think I am on the right track, but this part is important as i will want to create sub-forms later. Please help. Thanks.

 Darlene on 1/24/2012: Mr. Rost,
My question pertains to dynamic combo boxes. When entering the query criteria to filter the second combo box there are no results in the query once it's run. Also, the dropdown list on the form is empty or there is a message asking for parameter value. Any suggestions on what might be the problem?? It shouldn't matter but I am using Access 2003. Time index is 2:07; thanks.

Alex Hedley on 1/25/2012: Hi Darlene

Have you seen this Tip on Filtered Combo boxes?
There is a more in-depth tutorial in Access 307.

(The version of Access doesn't matter.)

Al

Darlene on 1/27/2012: Hi Alex, I saw the update prior to reading your comment. Tried it and works perfectly. I've been struggling with this for quite some time because of the query criteria. This is a powerful tip to have when working w/ forms and now am happy I've got it. Many thanks to Richard's speedy reply and to you for the heads up!!
Dindo on 3/19/2012: Nice sir, very helpful for beginners like me
 G on 5/25/2012: This is absolutely fantastic!
Thank you so much

Reply from Richard Rost:

Thanks!

 Mrk on 5/30/2012: Can another person find out what table or query was used to run a report or form?

Reply from Richard Rost:

Yes. Open up the form/report and go to design view. Open up the FORM/REPORT PROPERTIES and look for the RecordSource property.

 Beranrd on 6/14/2012: I would like to enter data into an empty form that is not connected to any table in order to enter data into a table or a join table. How do I do it? For example: Student enrolls in courses. I like to be able to use a blank form to enter data in (Student/Course) form.

Reply from Richard Rost:

Working with an UNBOUND form is beyond the scope of Access 101. You essentially would have to save the data into the table yourself using either an SQL STATEMENT, APPEND QUERY, or a RECORDSET.

Amos  Amosse on 8/19/2012: Thank you Mr Rost for putting me in, I am just new to your lessons, I just want to know if its possible to have a course with tests and Exams for data base programing that later provides a certificate?

Hank MarkWeier on 9/29/2012: How would I be able to install and use this training programme on my samsung pad? Thank you.

Reply from Richard Rost:

I don't officially support non-PC platforms, but I can tell you that my Online Theater works just perfectly on my Samsung Galaxy S3 phone and my Android tablet (Viewsonic). Try it yourself before buying (you can generally watch Lesson 1 of any class free before you buy). If it doesn't work, read this.

jadalee74 on 10/31/2014: i am trying to calculate percentage but i am at a loss the dbase is already done but how do i move along do in make a query then place a formula in the field

Reply from Alex Hedley:

Hi Jadalee,
You will need to use a Calculated Query Field to achieve this.
Check out this Tip to see how they work and you can amend it to your needs.

Lynne Duff on 6/10/2015: Hi Richard. I am learning how to use Access with your brilliant videos, thank you so much. my question is your video shows how to move a column in a table but cant see any instruction on how to move a row, only to delete, can you tell me how to do it, I have tried what I thought would be the right way but to no avail

Reply from Alex Hedley:

Why do you need to move the Row?

The data can be reordered using a SORT or ORDER BY clause.

Anonymous on 9/22/2015: how did companies keep track of customers info before database

Reply from Alex Hedley:

In Beginner Level 1 Rich mentions Index Cards as a way of keeping track of info.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP