ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 2010 Beginner Level 1 Tutorial: Database Concepts, Tables, Queries, Forms, Reports
 

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
 
 

< Previous: Excel Beginner 1

Next: Access Beginner 2 >

Access 2010 Beginner Level 1

Beginner Microsoft Access Tutorial - 2 Hours, 22 Minutes
 
 
Are you new to Microsoft Access 2010? This video tutorial will teach you all of the basics of how to build a Microsoft Access database. You will learn:
 
  - Database Terminology & Benefits
  - Planning Your Database
  - The Access Interface & Ribbon
  - Building a Customer Table
  -
Entering and Editing Data
  - Sorting & Filtering Records
  - Constructing Customer Queries
  - Designing a Customer Form Interface
  - Developing Reports & Mailing Labels

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first lesson of this course (free of charge), or scroll down for more information.




 

Access 2010 Beginner Level 1
Description: Access 2010 Beginner Level 1
Versions: Microsoft Office Access 2010
Access 2007 users should be able to follow along fine
Pre-Requisites: Working knowledge of Microsoft Windows required
Basic knowledge of Microsoft Word or Excel helpful
Running Time: 2 Hours, 22 Minutes
Cost: $5.99


This course is for the novice user who has little or no experience with Microsoft Access. We will begin by learning about databases in general, some important database terminology, the progression from paper filing systems to electronic databases, and the benefits of using a database.

 

Next, you'll learn the importance of planning your database before you build it. You'll learn how to determine what tables your database will need, and what fields to place in which tables. We'll also discuss laying out your forms to create a "roadmap" for your database.

 

We will then take some time to go over the Access development interface, including the new Ribbon menu, the parts of the screen, the Navigation Pane, and more.

 

We will next learn how to build our first table. We'll construct a table for a fictional company to store all of our customer data. You will learn how to build your table in design view. You'll learn about field names, descriptions, and most of the different data types.

 

 

You'll learn whether to use text or number fields, depending on the situation. We'll also discuss using AutoNumbers to create unique identifiers (IDs) for all of the records in a table. This is called a Primary Key field, and you'll learn why they're very important.

 

We'll learn how to enter data into our table, edit that data, resize columns, and rearrange fields. You'll learn some rules for storing your data - such as only typing in the digits of a phone number (no dashes or parentheses). You'll see how to use the Date Picker control to select dates. You'll also see how to use the Zoom window.

 

You will learn why no data is better than bad data. I'll show you how to copy sample records from a page on my web site, and then paste that data directly into your table. You'll see how to delete records, and we'll talk about where the missing AutoNumbers went. You'll learn about the Access Security Warning that you see whenever you download a database from somewhere else. We'll talk about dirty records, saving layout changes, and moving a column in datasheet view.

 

 

Next we'll learn how to sort and filter our records. You'll see how to perform a single-column sort. You'll learn the difference between text, numeric, and date sorts. You'll remove the sort. Then, we'll see how to filter records. If the boss only wants to see customers from New York, you'll know how to apply that filter to display the correct records. You'll learn how to print a table.

 

 

Sorting and filtering are OK, but you can't rely on them for daily use. This is where queries are useful. You can design queries to present your data in whatever filtered or sorted manner you want, and then save those queries for later use. You'll learn how to create a query from scratch using the query designer. You'll see how to add fields to your query, perform a sort, and add criteria to your query. We'll discuss multi-column sorts (left-to-right). You'll learn how to save multiple queries that each perform slightly different functions.

 

 

 

Forms are great for designing a nice, clean user interface for your database. They're especially perfect if other people need to work with your database because you can present to them only the data they need to see - and lock everything else. You will learn how to build forms to show a single record, multiple records (continuous forms), and a split form to show both. You'll learn about labels and text boxes, how to resize them, and how to save your form. We'll discuss editing data in your form, using record selectors to delete records, and how to use navigation buttons to move around.

 

 

Finally, you'll learn how to construct reports. Reports are good for presenting data to people who aren't using your database - whether its printed, sent via email, or given as a PDF file (all of which we'll talk about in class). You'll learn how to create a simple customer report, how to modify the report title, how to use Print Preview, the Zoom controls, how to change the paper size and margins, and how to switch between layout view and report view. You'll also learn how to use the Report Wizard and create customer mailing labels.

 

 

Again, this is the perfect class for anyone who is new to Microsoft Access 2010 (or 2007). If you have little or no experience with databases, or you just want to get familiar with how Access 2010 works, this class is for you. You should have some basic knowledge of Windows beforehand, and a little bit of Microsoft Word or Excel helps (so you're familiar with some of the basic, common features), but we'll teach you everything you need to know to use Access to build a simple database. 

Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access 2010 Beginner Level 1

00. Introduction (10:17)

01. Database Terminology (11:56)
What is a database?
Progression from paper
Benefits of Access
Parts of an Access database
Table
Fields / Columns
Records / Rows
Query
Form
Report
Macro
Module

02. Planning Your Database (9:04)
Consider your needs
What tables do you need
What fields are in each table
No order info in customer table
Separate tables
Draw your forms out
Gather your printed reports

03. The Access Interface (11:02)
Starting Access
Create a new blank database
Parts of the screen
Title bar
Window control buttons
The Ribbon
Collapse the Ribbon
Quick Access Toolbar
Navigation Pane
Status Bar
Object Pane

04. Customer Table, Part 1 (10:52)
Create Table
Datasheet View
Table Design
Field Name
Data Type
Description
Field naming conventions
Text
Memo
Numbers
Date/Time
Currency
Yes/No
AutoNumber
OLE Object
Attachment
Hyperlink
Calculated Field
Lookup Wizard

05. Customer Table, Part 2 (12:14)
Text or Number?
Long Integer
Double
Why use an ID field?
How to move a field in the table
Save your table
Table naming conventions
Primary Key

06. Entering Data, Part 1 (11:09)
Typing sample data
CustomerID (New)
Resize a column
Two-character states
Only digits in phone numbers
Percentages are fractions of 1
Date or Time or Both
##### in a field
Date picker
mm/dd defaults to current year
Two-digit year defaults 00-29
Spacebar to check box
Horizontal scroll bar
SHIFT-F2 to open Zoom window
Switching back to Design View
07. Entering Data, Part 2 (9:10)
No data is better than bad data
Copy sample data from web site
Selecting an entire record
Paste data into your table
Delete a record
AutoNumbers are NOT reused
Download copy of database
Security Warning
Enable Content
Do you have to save records?
Pencil indicates editing record
Dirty records
Saving layout changes
Moving a column in datasheet view

08. Sorting & Filtering (7:18)
Sorting a single column of data
Text, numeric, date sorts
Remove Sort button
Filtering records
Show just customers from NY
Remove Filter button
Printing a table
File > Print
Unfiltered
Why Queries are better

09. Customer Queries (15:16)
Create > Query Design
Show Table Window
Adding a table to your query
Drag fields to the query columns
Run the query
Design View
Add field with a double-click
Sort a field
Multiple field sort
Sort by Last Name then First Name
Sort order left to right
Moving a query column
Saving a query (CTRL-S)
Adding a Criteria
Show only customers from NY
File > Save Object As
Opening multiple queries
Switching between tabs
Change "NY" to "PA"
Can I edit data in a query?
Must I make a query for each state?
What is the "*" asterisk for?

10. Customer Form (14:21)
Why use forms?
What are forms good for?
Create a simple customer form
Different ways to build forms
Single Form
Label and Text Box
Form View, Layout View
Resizing Text Boxes
Layout View uses Rows & Columns
Save Form
Delete Form Title & Logo
Open in Form View
Editing Data
Using Navigation Buttons
Pencil indicates Dirty record
Record Selector
Delete a Record
Create a Multiple Items form
Continuous Forms
Create a Split Form

11. Customer Reports (14:08)
Create a simple customer report
Types of reports
Modify report title
Print Preview
Zoom Controls
Paper Size, Margins
Send as Email Attachment
Save as PDF
Layout View, Report View
Build report from a query
Report Wizard for Single Report
Delete a report
Mailing Labels
Label Wizard
Setup a Prototype Label

12. Review (5:33)

 


 
Keywords: Database Concepts, Tables, Queries, Forms, Reports, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, ribbon, database, table, field, query, queries, form, report, macro, module, fields, records, navigation pane, datasheet view, design view, text, memo, number, date/time, currency, yes/no, autonumber, ole object, attachment, hyperlink, calculated field, lookup wizard, long integer, double, ID field, primary key, date picker, zoom, security warning, dirty record, layout changes, sort, filter, print, print preview, criteria, label, text box, record selector, navigation button, split form, continuous form, multiple item form, single form, paper size, margins, email attachment, pdf, label wizard, mailing labels
 
 

Student Interaction: Access 2010 Beginner 1

Richard on 11/23/2011:  If you are new to Microsoft Access 2010, this video tutorial will teach you all of the basics of how to build a Microsoft Access database. You will learn: - Database Terminology & Benefits - Planning Your Database - The Access Interface & Ribbon - Building a Customer Table - Entering and Editing Data - Sorting & Filtering Records - Constructing Customer Queries - Designing a Customer Form Interface - Developing Reports & Mailing Labels Click here for more information on Access 2010 Beginner Level 1, including a course outline, sample videos, and more.
Richard on 11/24/2011: Now that you're finished with this class, please take a few short minutes to complete our STUDENT SURVEY and let us know what you thought of the lessons!
 Chris Bezant on 11/26/2011: Hello Rick
This is perfect for getting me from 2003 to 2010. I am an advanced user and have taken all existing Access 2003 courses. This course is ideal to introduce me to the new (initially confusing) interface.
More than that, it has made me WANT to move to 2010. I researched the net to discover that I can continue to produce 2003 compatible databases.
My monthly MYOLP subscription is fully justified with your continued top class material.
Best Regards
Chris Bezant, Southampton, UK.

Reply from Richard Rost:

Glad you like it! More coming soon.

victor victor on 11/30/2011: I can audio of this lecture is loud and clear but the video is not showing.

The video is standing still without showing what you are doing.

Reply from Richard Rost:

Try switching to the FLASH player (setting, bottom left)

Richard on 12/5/2011: Around time index 4:00, I talk about the Export to PDF feature. This was added in Access 2010. If you are still using Access 2007, you can still get this, but it's an extra feature you have to download from Microsoft's web site.
Gramse on 12/19/2011: Hi Richard,
I am literate in ms access and now I wish to prepare a database for school mark sheet for my own school. No idea how many tables do i need to create and how to establish the relationship. Basically there are classes, sections, and four terminal examinations every year.
I will really appreciate if you could guide me to start this.
Thanks

William M Meikle on 12/24/2011: Interesting, I have a flash player program but nothing on your player to show any way to switch to a flash player.

Reply from Richard Rost:

In the online theater, bottom left corner in the SETTINGS box, you'll see an option to switch between Windows Media Player and Flash. Click on Flash and then reload the course by clicking on it's title.

 Julie on 12/31/2011: Hi. I have just started this course and am really enjoying it. I'm wondering when you think you might have the other courses for Access 2010 available. Thanks.

Reply from Richard Rost:

Coming very soon. I've been busy because of the holidays, but starting the first week of January the new courses will begin rolling out.

 julie on 1/8/2012: IF you add or change the data in the table, will it automatically update in your query?
manuel zurbano on 1/13/2012: Hi Richard,

what will be the effect of the database created in access 2003. will it work in 2010 version.

Jose on 1/17/2012: Hello,

I was wondering at which learning level will I be able to design a db whereby the EndUser will be able to enter "Table" data or Add records without actually opening the actual "Table" as through sometype of form. Thank you.

Reply from Richard Rost:

Level 1... we cover simple form building in lesson 10. Much more to follow in Level 2. You never want your "end users" working directly with your tables.

Jose on 1/17/2012: Thank you for the quick reply. I do realize that I do not want my EUs to directly deal with my db tables thus my original question: At which learning level will I be able to learn how to present the EU with a UserForm in order for that EU to enter data. In other words, I want to create DB whereby I can hand it over to the EU and only work on "Maintenance" of the DB or "Troubleshooting it".
 Lynn Robbins on 1/22/2012: Hope your feeling better;
I enjoyed the Flashcards
Will have to spend more time

Stefanie Magalhaes on 2/2/2012: How can I make it full screen?
Stefanie Magalhaes on 2/2/2012: I am recreating my tables because i didn't not built it correctely on the begining. Since my old tables already have massive data on it(almost 1,000 customers. Am I able to copy and past this info/data into my new tables?
 Stefanie on 2/2/2012: How can I add fields on a query?
Stefanie on 2/3/2012: I am trying to create a Query that would import data from a table, new fields that would be for new e others fields that would make some calculations. How can I do that? Should I create another table instead?
Joseph Crouch on 2/24/2012: The picture stays the same throughout this video. Obviously - from the narrative - it was meant to have different screens.

Reply from Richard Rost:

Yes, absolutely. Try switching to the Flash video player instead of Windows Media.

Joseph Crouch on 2/24/2012: Importance of DB planning.
The USPS Address DB has address fields for prefix (N, S, W...etc.); Street, Apt or Suite number, street
type (Rd, Ave, Pkwy.. etc.), suffix (again N, W, etc.) and odd or even side of street (so you can sort to have carrier go up even side of block, then down odd or vice versa). All this is needed for machines to sort the mail in delivery order, of course, in addition to city, state, zip and +4.

Joseph Crouch on 2/24/2012: Finally got it to work. Using downloaded courses so I checked the EXTERNAL box. WMP came up. Closed that and unchecked the box. All works well now.
Joseph Crouch on 2/25/2012: That would be pretty advanced for this course. I have done it, but I had to set up some new primary fields to get the relationships right and then remove the old primary fields once I was finished. Kinda rough, maybe Richard knows a better way.
 Davie Robinson on 2/26/2012: Hi Richard, I have a question regarding Access I purchased Access 101 thru 329 plus the workbook, which I really never got started, I now have upgraded to 2010 and only see see beginner 1 & 2 for 2010. have I missed anything or when are your plans for more 2010 access, I am ready to purchase all you have.


Thanks very much

Reply from Richard Rost:

Right now I only have Beginner 1 and 2 available. 3 to 5 should be out this week. More to follow. Once you get past the beginner material, however, the rest of it is very similar.

  on 2/27/2012: good presentation. Thank you so much.

Reply from Richard Rost:

You're very welcome.

Bonnie on 2/28/2012: Richard, in Lesson 10, you say @ 7:20 that the entire row will move as a unit once you highlight the first name field. As I follow along on my own database, the entire row does NOT move. In fact, when I open a new simple form, the rows are not even aligned to begin with. Is there a setting in Access I have to change to get the behavior you describe? Thanks! Bonnie

Reply from Richard Rost:

If you create a simple form following the method that I used, you should be in LAYOUT VIEW, and your form controls should be enclosed in something called a "Layout" which treats the text boxes and labels as if they're in rows and columns. You can REMOVE this Layout (in fact, I personally can't stand it and will show you how to remove it in a future class) but by default it should be ON. I wouldn't worry too much about it, though. In the next class (Level 2) I'm going to show you how to build a form from scratch WITHOUT a layout. You'll have direct control over the size and position of each text box and label on the form.

cindy on 3/1/2012: Thank you so very much
 steve on 3/8/2012: how do I edit a table once I have saved it
Alex Hedley on 3/9/2012: Steve,
If you'd like to edit the structure of the table (add/delete/edit/rename fields) you can go into "Design View".
Top left hand corner of Access there is a drop down box which lets you change between different views.
Alex

 gopal on 3/12/2012: can i use formulas to calculate total amt=rate*qty in form

Reply from Richard Rost:

Sure, you can, but you're better off doing it in an underlying query using a CALCULATED QUERY FIELD. Then build your form off of this query. However, if you want to do it directly in a Form text box, just make the ControlSource equal to: Field1*Field2

 Missy on 4/5/2012: Queries: If you add a customer that lived in NY (for example) to your table, will it automatically show up in the Query table you saved, or will you have to rerun it everytime you add to it?

Reply from Richard Rost:

Remember, a QUERY doesn't contain ANY DATA. It only displays data from the TABLE. If you add a new customer, the NEXT TIME you run the query, it will show up there.

 Nidhi on 5/3/2012: Does Contol Z work in access?


Reply from Richard Rost:

Control-Z for UNDO works some of the time. It works most of the time during data entry, and some of the time during form design. You just have to try it and see. Usually when you're about to do something that is UN-UNDO-ABLE Access will warn you. (See what I did right there... I just made up a word).

 Jeff on 5/4/2012: If i would like to track people by a spicific number; would I still do an auto number field but make the specific number the primary key?

Reply from Richard Rost:

If YOU have a specific number that YOU'VE assigned to them, then make this a SECOND field. Still use the AutoNumber to have Access assign a number to them, but you can use YOUR number as well. You want to use AutoNumbers to form the relationships in your database (we'll get into those in the Expert classes soon). Since AutoNumbers never change, you don't have to worry about relationships getting messed up.

 Lynn on 5/6/2012: How do I cahnge the year quickly with Date Picker.
Would not like to slowly scroll year by year. Say to quickly change from 2011 to 1960.

Reply from Richard Rost:

How about an InputBox?

I = InputBox("Enter Year")

Then you could use the DateSerial function to set the year based on the user's response.

 John on 5/16/2012: If I add new information into my Table. How can I Put this new information into an existing Form?

Reply from Richard Rost:

Forms don't hold data. Forms DISPLAY data from tables. When you add information to your table, it will automatically show up in any forms that are bound to that table. If the form is open when you add the data to your table, however, you might need to CLOSE and REOPEN the form for it to refresh (requery) its data.

 LindaO on 5/19/2012: Just getting started. Really looking forward to the classes.
chhanonn on 5/20/2012: Thanks for this tutorial,it's the good.
 Adham on 5/28/2012: Dear Sir, your lession is realy nice, detailed and I really enjoyed learning the basics of Access 2010.
I am looking forward to see more cources in the futur. thanks a lot

Reply from Richard Rost:

Thank you very much for the compliments. Access Levels 2 and 3 are now available.

rich garabedian on 5/30/2012: I want to import an old database file into access. It is a mdb database file, but access says unrecognized format when I try to open it. Any ideas please.

Reply from Richard Rost:

Is the file OK? Can you open it in Access? Perhaps try a compact and repair?

Richard G on 6/1/2012: the mdb file is OK. It actually runs under an old dos program that manages my customers and tracks their spending with me. I want to bring it into access if possible
 Ferdous on 6/30/2012: how we can download the videos from access learning zone.com
thanks
for improving of young gerneration.

Reply from Richard Rost:

I recommend you watch them in the Online Theater, but if you must download, see this page for instructions. You can use my video player software or download the videos manually.

samuel on 7/27/2012: I am using access 2010 beginner 4
I deleted the PCresale customer database could you please send me the link

Reply from Richard Rost:

You can find the database on the Student Databases page.

Becky on 7/28/2012: I recently learned how to link tables together, and now need to populate these tables. Can you provide step by step instructions on how to do this?

Reply from Richard Rost:

This is a more advanced topic. I don't cover it in my Beginner courses. It will be covered in the first couple Expert courses. If you want to learn how to do it right now, get my Access 201 course, which is the first of the "Expert" Access 2003 tutorials.

 BIBI on 8/7/2012: GREAT EXERCISE
 Emmanuel on 8/21/2012: Emmanuel on 21/08/2012: Richard in lesson 10 you say @10:56 that to get a new blank form, you click the new blank form button in the navigation bar. My question is how can one create the customer form view to start with a blank form anytime the CustomerF is double clicked in the navigation pane

Reply from Richard Rost:

Emmanuel, you could do this with a little VBA code when the form opens to force it to start on a new record, but that's way beyond Beginner Level 1.

For now, I would recommend making a separate data-entry form. Just copy your Customer form and set the DATA ENTRY property of the 2nd form to "YES". This will only allow users to enter new records, and starts on a blank new record. Then you could use the 2nd form for looking up records.

Hilary Dillah on 9/6/2012: How to specify which item will be opened when double click the complete database in window, for example main menu form of the database?

Reply from Richard Rost:

You can find that in Access Options under the File tab. I cover this in the upcoming Access Beginner 7 class.

vhie on 10/26/2012: can i copy data from excel sheet and then paste it in access during..entering data for database?

Shirlee on 10/29/2012: Thank you, thank you, thank you for the clear instruction for access 2010. I have taken course after course and really didn't understand how to use 2010. I started at the beginning course and have and idea on how to design the data base I need.
Erick on 10/31/2012: Hi Rick,
I'm trying to watch Lesson 1 of your Access 2010 Beginner 1 seminar and I'm not able to watch it in flash because I believe our firewall at work blocks it. Is there anyway else to watch it? When I click the Windows link, all I get is a blank screen... I was able to watch the Introductory no problem.

Yours truly,
Erick

Reply from Richard Rost:

You can switch the Theater over to DOWNLOAD MODE and just pull the video files to your local computer and watch them there.

vannak on 11/5/2012: Customer Form. when I tried to re-size the column in the "Layout Mode", sometimes I can get just a(text column) to re-size but not the label or vice verse. Why can't I re-size the whole block horizontally? The mouse arrow changes to allow the re-size. I have to hold down a "Shift" key and then select the columns in order to re-size those columns.

Reply from Richard Rost:

I hate, HATE Layout Mode. As I mention in the videos, I use DESIGN VIEW whenever possible.

Yusarin on 11/11/2012: i would like to relate my table1 with table2. How can i do that? I was told to play around with the id?

Reply from Richard Rost:

Relating tables isn't something that I cover in the Beginner series. You'll have to wait until Expert 1, or you can pick up my Access 201 class. It's for Access 2003, but the material translates just fine to 2010. I also have a whole seminar on Access Relationships. In a nutshell, it's not hard to relate multiple tables together, but unless you understand the underlying theories, you'll probably do it wrong. It's more than a quick 5-minute answer to your question.

Cindy Schmidt on 12/2/2012: I understand that you would not want customers entering data in the tables and you would use forms for that. However since I would be the creator, I would work from the tables to enter or change data, correct? Similar to updating a spreadsheet?

Reply from Richard Rost:

It's good practice to avoid working directly with tables as much as possible. Even I prefer to make myself an ADMIN FORM to work with. Editing directly in tables tends to cause problems later on when your database gets more complex and you've got relationships set up, and rules programmed into your forms. Sure, you CAN use the tables if you want, but I don't recommend it.

ezra on 12/22/2012: have u shown how to deal with attachments


Reply from Richard Rost:

I have covered them in my Access Imaging Seminar, but not yet in the normal Access series. I'll get to them in the Expert classes in more detail. As a general rule, I try NOT to attach files INSIDE the database. It causes bloat, and makes your databases big and slow.

George Stefanidakis on 12/22/2012: The videos I dowmloaded have a problem at
some points It particularly happens when panels containing instructions are shown the screen becomes distorted and the information is hidden by a partially black foregound. Can you offer an explanation or a better way to view these videos. I am using your 599CD External Video player which opens Quick-Time player on a Vista Machine

Reply from Richard Rost:

Don't use the QuickTime player. I've had lots of problems with it in the past. I strongly recommend you download the VLC PLAYER. That's my #1 choice. #2 would be Windows Media Player, but make sure you have the most up-to-date version.

Severina Coppola on 12/30/2012: Hi Richard,
I think you tutorials are great thanks. I have a question as I am going through the tutorials I am trying to create an asset database and understand that I would have to create a table for the assets eg make, model, serial number etc and a table for the customer/user eg firstname, lastname, emailaddress, owner of asset. but where do I put eg return of asset, date returned, type of asset, who returned it and reissue asset, date reissued, type of asset to new customer.
Thanks for your help.
Serina

Reply from Richard Rost:

You would need a third table to track transactions. Table 1 would be customers. Table 2 would be assets. You would need a JUNCTION TABLE to track the history of each of your assets. This creates a MANY-TO-MANY relationship.

ID, CustomerID, AssetID, TransactionDate, Notes, etc.

I cover this in detail in my Relationship Seminar.

sardar on 1/3/2013: Hello thanks for teaching well
Diane on 1/11/2013: I cannot seem to copy and paste your sample data into my customer table. I get a message that the content is too long to edit. Also, now my Customer ID #s start at 15 for the record after 3. Please help.

Reply from Richard Rost:

Make sure you're not INSIDE a field (cell) when you paste. You are pasting whole records. Click the little gray box to the left of the row, just like I do in the video. THEN paste.

As far as the AutoNumbers go, don't worry about that. They don't matter. If you added and deleted some extra customer records, your ID will go up. It's OK.

John M Martin on 1/18/2013: I'm entering data in my first table, first database. I have a field in which the entries are the same for many records. In Excel, typing the first (few) characters will cause Excel to fill in the rest of the entry, thus greatly saving keystrokes. Is there a similar function in Access I can turn on?
John M Martin on 1/18/2013: Edit Field Names? I'd like to make edits to some field names such as change "InstrumentOne" to "Instrument1." Can't find anything in the pull-down menues that let me do that. Is it possible?

Reply from Richard Rost:

You have to open the properties and change the name there. If it's a TABLE FIELD, you need to change it in the table FIRST.

Roger Izzard on 1/22/2013: I am using Access at work to do Inventory of Stock, my question is can access be used with a barcode reader to identify a product, also can it change the figures of goods in and whats in stock and subtract what is being dispatched?

Reply from Richard Rost:

Yes, yes, and yes. Easy questions to answer... not quite so easy to explain how to do it. Read this on BARCODES and INVENTORY.

Rohit Prajapati on 1/27/2013: I don't have any question.
I am so much thankful to you
the way you describe it amazing.
i have never been taught like this.
thanks again.

Jennifer on 2/20/2013: Thank you for Q and A. If your own number is absolutely unique why should it confuse relationships? Also have read that the primary key can include more than one field. Could this be the unique number and auto number?

Reply from Richard Rost:

You CAN use your own number for an ID field. You don't HAVE to use an Autonumber, but I recommend it. If you use an Autonumber you don't have to worry about assigning unique values or dealing with changes in the value. If you want to have a 2nd number (your custom number) on the record too, that's fine - but don't use it for relationships. Again, just MY OPINION from my 20+ years of building databases.

Yes, a "key" can contain more than one field. This is called a composite key. I cover them in one of my lessons... I forget which one though. :)

Jennifer H on 2/21/2013: Thank you so much . Am enjoying your lessons. I have taught myself and am enjoying going through your lessons and learning all the tricks. Wish I had started with your lessons. My database is to capture medical data. I therefore have multiple tables with a large amount of fields. I gave each patient a unique patient number and called it the primary key in the first table where I linked it to name . Can I now add a separate autonumber even after data has been added? I hope to be able to split the database for data entry via forms. Am putting all on hold while I do all your lessons as far as expert 1!!!

Reply from Richard Rost:

Sure... you could keep your old number, just use a new ID field for RELATIONSHIPS.

Muhammad Idrees on 2/23/2013: Hi, i really like your teaching videos.
I have a problem
1 21-Feb-13
2 21-Feb-13
3 21-Feb-13
4 23-Feb-13
these same dates i want to count for 1 time in ms access report how i can do so? i will be great full.

Jeff Robinson on 3/2/2013: when are the properties and the property sheet talked about

Reply from Richard Rost:

Access Beginner 2, Lesson 5 is when I start talking about it.

Sotie S Dempster on 3/2/2013: If I deleted a customer data from a table, the customer ID is also delete and I want to rearrange the customer in order, that is I had customer from 1 - 20 and I deleted customer ID# 12, now the numbering is now from 1 - 11 and jump to 13, how can I change #13 to 12?

Reply from Richard Rost:

You can't change AutoNumbers. Once they're deleted, they're gone. You shouldn't rely on them for anything except making relationships between your tables.

Lisa on 3/25/2013: What video is relationship building in?

Reply from Richard Rost:

I start covering relationships in Access Expert 1. Then in Expert 2 we spend a lot of time on database normalization.

Raihan Amin on 3/25/2013: Richard,

You are peerless when it comes to elucidation!

Raihan

Reply from Richard Rost:

Why... thank you very much. Your praise is well appreciated. I shall now go and float around the house with my inflated head... :)

James Freeman on 3/27/2013: Don't have a calulate field, Does 2007 have it?


Reply from Richard Rost:

I believe this feature was added in 2010. I don't have 2007 installed anymore to tell you for sure. However, you're better off in most cases using a Calculated Query Field.

Karl Nunne on 4/7/2013: I tried to replicate your Query using the from PA criteria and noticed the sample Customer Table data did not contain customers from PA. I simply changed the criteria to OH and everything worked as expected. I just thought I would bring that to your attention in case someone else ran across it as well
Marty on 4/23/2013: How to pull data from another database based on an entry into the current database? ie enter a customer name and then pull customer information into the database from another database.

Reply from Richard Rost:

LINK the table from the other database into your current one, then use a DLOOKUP to pull in the data.

Julie Martin-Beaulieu on 5/30/2013: Hi, is there much functional difference between 2010 and 2013? I have just purchased 2013.

Reply from Richard Rost:

As far as desktop databases go (the kind most people make) there are very few differences between 2010 and 2013. The only major change for a beginner is that TEXT fields are now called SHORT TEXT fields in 2013, and MEMO fields are LONG TEXT fields. That's about it. All of the rest of the changes are on the WEB-database end, which I haven't even gotten into in my lessons... yet.

Srinath Nandyal on 6/4/2013: I recently purchased a small quantity of "Return Address Labels". It is Avery 42895 (Size is 2/3 X 1 3/4). Unfortunately, this Avery number is NOT in the drop down selection list. How do you overcome this limitation?
Thanks?

Reply from Richard Rost:

You would have to manually adjust the label size (width, height, margins, columns) to accommodate your new label size. Select the closest label to what you have to adjust it from there.

Carolyn Cwik on 6/4/2013: A quick question for planning. Our database includes many married couples. Is it best to add the spouse information in another record as a subform to include the 2nd phone, cell phone, email address etc. or just have double fields in the main record. That is for example, FirstName1, FirstName2, Phone1, Phone2 etc.

Reply from Richard Rost:

This is completely up to you and your needs. You can store the spouse as a related, second record and just link them together somehow OR if you don't collect that many of them (or need to use the data that often) then just add additional fields. It's all based on YOUR database needs.

Personally, I prefer to use a SELF JOIN as I cover in my RELATIONSHIP SEMINAR. Each "person" in the database is in the PersonT table, and in a subform I specify all of the relationships. This way you can create separate records for everyone in the family (mom, dad, kids, grandparents, etc.) and everyone gets related to everyone else.



Chris S on 6/4/2013: If I created a database of, let's say, students enrolled in a school. Suppose out of 200 records, I have 25 students withdraw enrollment. Is there a way to archive those student records?

Reply from Richard Rost:

Unless you're dealing with HUGE amounts of data, I would just have an "IsActive" yes/no field to indicate whether a student is active or not.

Courtney Schreifels on 6/10/2013: Can you connect more then 2 tables in a relationship? I have a customer table, a services table, and amounts they pay for services can I connect them both to the customer table

Reply from Richard Rost:

Absolutely. You can have lots of tables associated via relationships.

kelly on 6/10/2013: Will this tutorial work for Access 2013?

Reply from Richard Rost:

Yes. This tutorial is 99% the same for Access 2013. I'll be releasing a new Access 2013 Beginner 1 class very soon to show the differences... and there are very few.

ESimons on 6/18/2013: I am using Access 2013...will these learning sessions apply?

Reply from Richard Rost:

Yes. My Access 2010 lessons are perfectly fine for 2013. I'm going to be re-recording a 2013 Beginner 1 lesson for absolute beginners, but if you have any experience with Access whatsoever, you won't have any problems following along with the 2010 lesson.

ESimons on 6/18/2013: Question: I am trying to build a database that holds client information, demographics,household information, etc. Would I do a different table for each type of information and how would I link the information to an individual record?

Reply from Richard Rost:

You could store the information in the customer table if you want to. Depending on the volume of data, you may want to consider a second table. I cover working with multiple related tables in my Access EXPERT Level 1 class.

Lyn Mallet on 6/25/2013: How do I get a number in a field to show 2 decimal places? Thanks heaps. This is great

Reply from Richard Rost:

Set the "decimal places" property in the table and/or the form to "2".

Shawgi Sukumaran on 6/28/2013: In the online theatre, how can you skip through - to go ahead ?

Reply from Richard Rost:

You have to wait until the whole video loads, then you can skip around in it by clicking on the slider bar.

Carmen Shrum on 6/28/2013: if I have an existing table with a date field set as "text" (Apr1) can that be changed to a "date/time" field

Reply from Richard Rost:

As long as all of the values in that text field conform to a valid date/time format (like 2:00 PM) then you should be able to switch it just fine. Any data that does NOT conform will be lost. "Apr1" is not a valid date format.

Camie Richno on 7/1/2013: When you talk about creating a table, you mention that there are two different ways to create a table. At time index :31 sec in the video..when I click create I do not see the "table design button" How can I get it to show up in my database ribbon? Thanks,
Camie

Nazeer on 7/9/2013: sir

i want to enter Product ID in sub form , then
item name, pirce are coming automatically in sales form, how i can do it pls help me sir....

Reply from Richard Rost:

Any time you say "automatically" you're almost always talking about event programming with either a macro or VBA code. This is WAY beyond my beginner and even expert series. I start covering this in my advanced lessons (Access 301).

Raul Pendas on 7/16/2013: How can I add a field after the table was save?


Reply from Richard Rost:

Go back into Design View and add as many more fields as you'd like.

Martie Jacobs on 7/19/2013: I have designed a BirthDay field with date/time. Problem is I do not have everyone's year of birth. Would like to have the choice of dd/mm or dd/mm/yyyy but when I just enter dd/mm, Access automatically inserts current year.

Reply from Richard Rost:

That's a problem. You have to have a YEAR component to enter a valid date. Your options are to either (a) use separate fields to capture MONTH and DAY, or (b) enter in some old, impossible value, like 1900 as the year if you don't know the year.

Martie Jacobs on 7/23/2013: I want a table field for Departments with a Form ComboBox, but one person might be involved in several departments. What is the best way to build this?

Reply from Richard Rost:

A many-to-many relationship with multiple tables, as I demonstrate in Access Expert 7 by placing customers into multiple groups.

Shawgi Sukumaran on 8/8/2013: Can forms be used to enter data ?

Reply from Richard Rost:

Yes, that's what forms are used for: entering and editing data. Remember, though, that the data is stored in a TABLE, not the form. The form is just your interface.

Shawgi Sukumaran on 8/8/2013: can the yes no filed be used for analysis later or do you need numbers eg 0,1

Reply from Richard Rost:

You can work with yes/no values OR convert them to numeric values. The choice is yours. It all depends on what you're trying to do.

Lim Siew Hong on 8/11/2013: after going through this section on planning the databases, I am not too sure after learning access, it can help me to plan and keep track of my students clinical posting to the hospital, failures, report of perfomance in hospital etc etc. can't figure out how a table can relate to another table in this case. I will appreciate if you can tell me if i have chosen the right course to help me in my work more effectively.?????

Reply from Richard Rost:

Oh, I'm sure Access is the right tool for the job. Don't worry... sometimes it's difficult to plan out your database perfectly until you've built a few databases! It sounds like a Catch-22, but you really need to build a few simple databases first BEFORE you "get it." That's why I recommend you build the sample database with me in class before embarking on your own project.

Now, I don't start covering RELATIONSHIPS between tables until the EXPERT series, so you've got nine Beginner levels to get through before you really have to worry about that. Don't worry... it will come to you.

Richard Rost on 8/19/2013: Because the classes are almost identical, I'm merging the Access 2013 Beginner 1 and Access 2010 Beginner 1 forms. This way all of the people taking the 2013 edition can benefit from the questions and answers asked before.
Lyra on 8/20/2013: Hello Richard,
I have a problem with the Form. I write all my data in mongolian language. But when I want to save the Form it doesn't work. there comes a script: microsoft access cannot save the form or report because it displays characters from a language that cannot be saved in your language...but it worked before. And I can save a Query written in my language...please help

Reply from Richard Rost:

Sorry, I have little to no experience working with other languages.

bpost bpaid on 8/25/2013: I am busy working on an access database. To keep everything constant I would like to make a drop down menu for some of my field such as for a company name. How can I make a drop down menu within a data entry form?

Reply from Richard Rost:

You're talking about a COMBO BOX and I cover them extensively in several of my lessons, starting with Access Beginner 8.

haider on 9/2/2013: how to filtering the tables in the Report,, like I want to filter the Customer ID that not all IDs shown on the Report


Reply from Richard Rost:

Use a query criteria. I cover this in my Access Expert 5 class.

Ute Ingerl on 9/3/2013: While trying to import sample data the copied data had lost the "Type" of the field names and access would not let me paste it. Instead it created a "Error" file in which I have tried to enter the correct field types (worked). But in this case it is much easier to import the complete file. Just for your info. Don't understand why this happened.

Reply from Richard Rost:

I'm not sure what you did either. So far over ten-thousand people have taken this class, and you're the first to say something about it. :)

steve on 9/9/2013: Is it possible to have auto fill or drop down table in certain fields? Lets say I have a group of 6 company names I use over and over. Can i get the field to auto fill when I start typing or can I get a drop down list when I click on the field?

Reply from Richard Rost:

Yes. What you're looking for is a COMBO BOX and I do cover that in my later classes.

Brian Merrick on 9/24/2013: I want to track miles on a vehicle, can I create a seperate table for miles or can i put it in the vehicle table?

Reply from Richard Rost:

If you want to see each entry (date, miles added) then yes, you'd need a second table. If you just care about the current total miles, then no, you could just make that a single field in your vehicle table.

Brian Jones on 10/7/2013: I want to create an database on airports covering Revenues, Operating Expenditure, Passengers, Terminal Specifications, Staff in each department etc for each airport. Would I need a table for Airport General info(Name,IATA Code, country etc) and then one table revenues(with fields of the types of revenues, expenditure (all the types of expenditure?). Please Help! Thanks.
Vincent Dyas on 10/8/2013: @ 3:53 did you mean to say Excell interface ?
Ramona Woitas on 11/7/2013: I just finished watching both your video courses on mailing labels (B1) and name badges (B6).

I used them to create a business card. However when i go to print, it only shows one card. Is there something else i need to do to print 10 of the same business cards on one page?

Jim on 11/10/2013: Rick/Alex

A general question about form size..I have developed my ambulance database on my home computer with a fairly large screen. All my forms and reports are therefore sized to that larger screen. I have transferred the database to each of the ambulances that use a tablet for electronic patient care forms as well as using the database's functions. The forms and reports don't fit onto the screens of the tablets. Is there any easy way to have the forms auto size to the screen being used or must I go in and manually change each form to fit?

Alonzo Dock on 11/16/2013: What the different between a blank date base and a web database.
Alonzo Dock on 11/16/2013: Can I create my own auto number
Alex Hedley on 11/17/2013: Hi Jim,

You probably need to look into Anchoring.

MS Article.

I turn this off and manually create the Form so I'd be in trouble but if you have left this on, and had Access automatically create your Form it might be possible.

Alex

Chad on 12/4/2013: Hey Richard,

Im building an inventory data base for my company. Im having trouble getting reports to fit properly on the screen.

I work on things with design view, and when i switch to form view they move.

Another problem is when im making reports
and i got to print them, the size of the report always creates extra pages. Is their a quick easy way to fit everything to 1 standard printer page.

Thank you

Chad

Steve Barnfather on 12/10/2013: Is there a way to add Barcodes to a form or report?

Reply from Richard Rost:

Yes. Get a barcode font. I'll be covering this in an upcoming seminar.

Bernard Burchell on 12/12/2013: Hi Richard. How many people can simultaneously use the Access database?
Brian R on 12/19/2013: Thanks for this Richard. I've been trying to teach myself with no luck. This has helped me build a database for my new business without spending money for a new program.
Anthony Nicolaides on 12/28/2013: Hi Richard,
A question on the email link, if you don't use outlook how do you get the link to open in yahoo or google email?

Regards

Anthony

Reply from Richard Rost:

Access doesn't control that. Windows will open the link using whatever your default "handler" application for email happens to be.

Anthony N on 12/30/2013: Hi Richard, thanks for the fast reply.
Sheena on 1/7/2014: In Access and most Microsoft Products when you want to email a report it automatcially defaults to outlook. My company uses groupwise. Is there a simple way to change this if it can be changed at all.
Daisy W on 1/18/2014: I am creating a database for a report that I do for work. It has Jan - Dec, do I need to create a ID for my report.
Katie on 1/29/2014: I have been using a data base for years but since we converted from 2003 to 2010 things have changed. I have tried to print my forms and they are now completely messed up. How can I print the header on every page and not just the first page?
Alex Hedley on 2/15/2014: Is there a reason you are printing Forms and not Reports?
Horace LONEY on 3/9/2014: Hi Richard
Let me start out by saying how much I really enjoy your excellent presentations, you are an awesome instructor truly amazing.
I've developed 4 different packages for customers to choose from; with each package have its own unique content. Do I need to create 4 different tables describing each content or can this be accomplished by having one table; I would also like to use a combo box drop down to make my selections. Please advice on how best to complete this task including any materials or tutorials you may have regarding this topic
Yours truly
Horace

sonam on 3/24/2014: I want to create database of profile and insert picture on respective profiles, so how can I create?

Reply from Richard Rost:

I cover this in Access Beginner 6.

carol on 3/26/2014: I have an existing Real Estate database that I am trying to use. I didn't set it up and now finding some inconsistencies in the fields and data type. I had started inputing information before I realized this. Can I go in and delete what I have input and then fix the tables? How best to do this?


Reply from Richard Rost:

It's hard to tell without seeing your database. In cases where people have existing databases and then they LEARN how to properly build a database, I usually recommend starting from scratch, and then importing whatever data you can. But, of course, this depends on how bad the existing setup is.

gabriel miranda on 3/27/2014: hi rick i am fairly new to database keeping and my boss asked me to transfer a report he has on Word over to Access for better datakeeping
which lesson do you believe i should download. that will help me with this task


Reply from Richard Rost:

Well, you just need to learn good report design. The basics are in Beginner Level 1 Lesson 11, of course, and there's more good report "stuff" in Levels 6 and 9. Of course, a lot of the design techniques for reports mimic forms, and I cover form design throughout the Beginner series. "Transferring" the report from Word to Access generally involves designing a NEW report that looks like the old Word doc. It's almost impossible to reliably IMPORT that file directly into Access.

Mahesh.B on 5/2/2014: I am happy the way you represented the topic and like to have still more details with regards to MS Access and what make it different from Excel.

Thanks
Mahesh.B



Michael Diamond on 6/11/2014: I am receiving errors when trying to watch #3 in Beginner 1
Video not found or access denied

Reply from Richard Rost:

Working fine for me. Anyone else having this problem?

Daniela Aresi on 6/12/2014: I have Access 2007, which is slightly different. I am not able to export a Report to Excel. The button is there, but it's greyed out.I can export Queries Tables and Forms, no problems. Only for Reports the option is grey. TIME INDEX 22:41 Thanks for the help

Reply from Richard Rost:

I don't even have Access 2007 installed on any of my machines anymore, so I can't see what the problem is or figure it out. Anyone else still using 2007 here?

Christine Lucas on 6/18/2014: Christine 19/06/2014 Hi Richard in Beginner 1 lesson 10 - Customer form 5.24 your form displays in two columns mine only display's in one column how do I get mine to display like yours?

Reply from Richard Rost:

That's how the "wizard" created the form for me automatically. If you don't have than many fields, you may not see 2 columns. Don't worry about it too much. In the next class (Level 2) you're going to learn how to design your own forms instead of having Access build them for you automatically.


Wendy Corby on 6/25/2014: In planning my database table, how do I enter Divisions, Classes (subdivisions), subclasses into a table? For my plant club, we have 25 divisions (categories) (labeled Roman I to XXV), most divisions (but not all) have subdivisions (A, B, C, etc.). Some classes have subclasses (1, 2, 3). A very few subclasses are subdivided (a, b, c). So does each division and subcategory get a row (ID)? Or do I create only rows (ID) for Divisions and then columns for the subdivisions? When a plant is entered into a show, how do I select the proper category (e.g., I.A.2. or X.B.1.)?
Jim Ewin on 7/17/2014: I'm feeling a little inadequate. I just bought the 2013 Access and put it on my computer.
I went through all of Richard's beginner course before I looked at it. Alas. I can't even find the place to start. Seems like I need to answer a lot of stuff 1st. Down the left side there is new, recent , options , account etc..Red and White??????
Can any one give me a hint on how to get past this to tables?

Hans Stingl on 7/30/2014: I'm just having the same problem as Chad. When I try to print out the report in print view sometimes empty extra pages appear. Ther is also a warning before printing that the report may not fit in the page. What can I do to overcome the problem? Thanks in advance for your help.

Reply from Richard Rost:

Adjust the page size. Your horizontal width is likely too large. Drag right right edge of the report to the left.

Peggy on 8/2/2014: How can I send someone else my database to view?

Reply from Richard Rost:

Just email them your ACCDB file. If it's large, you may want to ZIP it first.

Anthony P Mitchell on 8/19/2014: Good Morning Richard, I am enjoying these courses very much, I have a question (s) for you to help my understanding. I am building a training database by importing information from Excel spreadsheets. When I import employee information I have used their Computer ID as the primary key, no problems. When I import their training records, it shows many completed training items against each individual, I am having an issue with the primary key! if I use auto number this will give a different number for each individual which then does not work, the only way I think I can do this is to assigned a valve against each person and add this into my access table on a one by basis, I hope you can help? its hard to explain so hopefully you understand me? Any suggestions.

Reply from Richard Rost:

Anthony, since you're posting this under Access BEGINNER Level 1, I have to ask you which class you've actually finished watching. Have you finished Expert 1 and 2 yet? Those classes talk about relationships like this. Importing relational data is covered much later in Expert Level 22.

Dale Ferg on 9/13/2014: I have purchased Access 2013 with the intention of using it to create a database to use at work. The version that is in use at work is Access 2010. Will the 2010 version be able to run what I create at home in the 2013 version?
thank-you,

Reply from Richard Rost:

Yes, they share the same file format. You shouldn't have any problems UNLESS you start getting into 2013-specific VBA programming. If you stick to the "standard" features, you'll be fine.

Dale Ferg on 9/27/2014: I have just purchased Access 2013 and intend to design a DB in this version at home. At work the network there has Access 2010. Will I be able to use the 2013 design at work in version 2010?
thanks

Reply from Rick Rost:

Access 2010 and 2013 are nearly identical and share the same database format. You shouldn't have any problems working between the two.

Chas Phillips on 10/2/2014: Can I import an updated excel spreedsheet over a table. The update is only data not structure.

Reply from Alexander Hedley:

It's possible but is quite complex and isn't covered until Access Expert 20

Victor Jackson on 10/20/2014: Can we backup a mirror copy of our Access 2013 desktop data to our online Access 2013?
Peter on 10/22/2014: Hello Sir,
I am creating an accounting database but i'm having a problem on how to create a report with a comparison of current month balance the prior month balance to figure out the increase/decrease of the accounts.

Atat Joel on 10/24/2014: pls how can i hide the back page of my access database tables/query

Reply from Alexander Hedley:

Check out Beginner Level 7 to find out how.

Angelika Gutenberger on 11/22/2014: I tried to add an extra field I forgot when creating my table. I am trying to save the table again after making the change and it keeps telling me that I have too many fields selected???What do I do?

Reply from Alex Hedley:

Hi Angelika,
What's the exact error? Is it too many fields selected or defined?
Which version of Access are you using?
How many Fields do you have in your Table?
Try closing down the db, reopening and trying again.
If that doesn't work back up the db and then try a Compact Repair.
If any/all of that doesn't work you could try importing the Table into a new blank db.


Angelika Gutenberger on 12/9/2014: It says too many fields defined...I was trying to shrink my field size on an already saved db and it came up with the same problem. I am using access 2013 and have 100 fields. I am having this problem with all of the forms, queries, and tables I am making...Am I suppose to save them in a different format or something?


Reply from Alex Hedley:

That seems like a lot of Fields for 1 Table, it might need normalising.

One option would be to backup the database and then run a compact and repair.
If that doesn't work try importing the table into another blank database and trying again.
If that doesn't work you might need to split the table.

Angelika Gutenberger on 12/11/2014: thanks

Anonymous on 12/22/2014: its very help full


suresh
india

Max Spindler on 1/5/2015: The delivery is very good. I wish you could use some bright cursor (at times) so that when you make a selection I can spot it. I often have to rewind so as to see the cursor.
Bobby Joseips on 1/12/2015: What is the best way to enter a phone number into a data table? 1112222
(111)222-3333 111-2222 111-222-3333 1112223333?

Reply from Alex Hedley:

Hi Bobby,
Although a phone number is made up of numbers it's best to use a TEXT Field to store it. Number fields are used for Calculations. You're not going to add up a list of phone numbers.
You set an Input Mask on it it just straight numbers.
You can then set a FORMAT on the number to show brackets () or hyphens -

Kathleen Sundby on 1/26/2015: I am trying to create an access database from an excel spreadsheet. My boss wants to know what are the benefits for creating different tables instead of one. What should I tell him?

Reply from Alex Hedley:

Having each table represent a collection of things makes it easier to maintain.

Say you have a user that is mentioned multiple times and they change their name.
You would only need to change it once in the UserT and it would change everywhere else.
If not you would have to change it in every record it appears elsewhere.

Molly Brack on 2/10/2015: Hi Richard - thanks for the great instruction! I am struggling to figure out which things go in their own table versus being fields in one giant table. Is there somewhere in your lessons that goes into that? I know its kind of subjective, but am hoping there is some magical formula that will help! thanks - Molly

Reply from Alex Hedley:

The Beginner Lessons will explain this.

It all depends what database you are making but since Rich uses Company example a lot we could use that.
You have a Company and Employees.
You would have a Company Table which stores information about that.
You would have an Employee Table which stores information about them.
You can create a relationship to join them.
This would be better than storing them all in one Table and having to repeat the same company name for each employee.
It also makes it easier to update the company name in one place if it were to change instead of for every employee record.

Ingrid Laplanche on 2/26/2015: HI Richard,

What if you already have data with Customer numbers and you want access to continue with the Autonumber using the your current order for example the last customer# is 1149 and you want autonumber to continue with 1150.

Kind Regrards,
Ingrid


Reply from Alex Hedley:

The easiest way is to use this Tip.

Wayne Markel on 3/8/2015: Is there a way to set the level of the lesson audio without having to lower it each time I start a new lesson?

Reply from Alex Hedley:

I don't think there is one
You'd have to change your computer settings to be less but then that would be system wide.

Jovito Nad on 3/18/2015: Good morning. I am learning so much from this Access 2013 classes. I just finished the beginner level 1 and am ready to continue my studies to level 2. If I don't see the 2013 beginner level 2, is it the same to take the 2010 beginner level2?

thank you.

jojo

Reply from Alex Hedley:

B1 was re-recorded with 2013. The next few aren't, it's not later until 2013 is used.
There isn't much different between the versions so you'll have no problem following along.

Maxxarn on 3/20/2015: Hi,
When pasting your example data on a computer with an european setting for currency I get an error. Fix: Remove dollar sign from [CreditLimit] and just have a clean number.
We use commas instead of points to separate decimals from integers so, for example, [5,000.00] will end up as 5 on my computer. Fix: edit data to just say [5000].
Same problem with commas vs dot on the percentage data where, for example, [0.2] will end up as [200%] on my localized computer. Fix, change data to [20%] in the copy/paste data.
I understand that the format will not look exactly like a text file export, but it will have extended compability with localized versions.

Reply from Alex Hedley:

Are you copying from the Table or Form?

Daniel on 4/3/2015: Hi Rost I enjoyed your beginner class level one very much, in fact I will say I am very muck OK with it. But I will like to continue with level two classes, can't find it...pls help Prof. Roster...Daniel from Nigeria

Reply from Alex Hedley:

You can find the Course List here.

Beginner Level 2

Or order it here.

scott allen on 4/6/2015: I have several tables in my database that have the same information from different sources. I want to keep them seperate but also create a table with the merged data of these tables?

Reply from Alex Hedley:

Sounds like you need a UNION Query.

This is shown in Expert 16

George Blair on 4/9/2015: Hi Richard
When I click on the label button I get a "Invalid use of Null". I click OK and the label wizard opens but then I get an hour glass that is trying to load something but it never ends. What is wrong?

George

Reply from Alex Hedley:

Have your tried a compact/repair
Some suggestions are to update office

George Blair on 4/9/2015: I previously wrote about an error I was getting when I tried to set up labels. I re-installed Access and the problem went away.
George

randy richardson on 4/16/2015: I switched to the window instead of tab view, but I can only display one window at a time

Reply from Alex Hedley:

So when you open another form does the first close?

Jack Scott on 4/18/2015: Very good explanation of Queries!
Brenda Peterson on 4/25/2015: I have a HP Pavilion laptop and you said to make the notes area bigger press Shift+F2 but that doesn't work on my computer. F2 just dims the screen. Is there another way to make the notes area bigger? (Access 2013 Beginner 1 Video 6 Entering Data Time Index 18:15)

Reply from Alex Hedley:

You might need to use an FN or Function Key, usually in blue, just depends on your machine.

You could do it in code.

In a button Click or Got Focus event.

DoCmd.RunCommand acCmdZoomBox

Amy Fall on 5/11/2015: In Excell I've been able to sort my customers birthday by the month even though the whole date is in one field. Can i sort dates by month only in access 2010 or do I have to separate day, month, and year in different fields.

Reply from Alex Hedley:

You could split it by either the DatePart Function:
DatePart("m", [DATEFIELD])
Or using Month:
Month([DATEFIELD])
Then order by that.

Rosa on 5/23/2015: I would like to create a database for a work project which would would allow others to work inside of it simultaneously BUT on a specific day of the month I would like to "close" this database to prevent new entries. Is this possible?

Reply from Alex Hedley:

You could have a procedure that get's the Date() and then closes the db if it equals the date you don't want them in.

Make sure to set this on the Form you have as the StartUp Form.


Private Sub Form_Open(Cancel As Integer)
If Date = "24/05/2015" Then
MsgBox "You aren't allowed to use this Database today, please come back tomorrow", vbInformation, "Closing"
DoCmd.CloseDatabase
End If
End Sub


Or you could just disable button's on the Main Menu.
See the Security Seminar for an in-depth tutorial on this.

Rajasundar K on 5/24/2015: I would like to learning MS access from begninng

Reply from Alex Hedley:

This is the lesson to start with, then continue on with the rest in this series.

Bob Bush on 5/24/2015: I keep running into error messages when I try to import my Excel spreadsheet into Access 2013.

Reply from Alex Hedley:

What error message?

Anonymous on 5/31/2015: I also can't open the zoom box for a field by Shift F2 - this will adjust the sound volume. if use Fn F2 it will highlight all the text in the field. How can I open the zoom box on a Dell laptop running Windows 8.2?

Reply from Alex Hedley:

You could use

DoCmd.RunCommand acCmdZoomBox

Anonymous on 6/3/2015: I need to create a query, but the query has t be with a relationship between two different tables...no idea what to do here.

Reply from Alex Hedley:

These are covered in Expert Level 1
Or the Relationship Seminar

Leroy Workman on 6/7/2015: Richard, I have a problem building my database for my finance's new business. I soon realized after creating some queries, forms and and reports that at least one or more of my tables were carrying way too many fields so I split them into two or more tables to that each table would be more streamlined in their purpose. But as you can guess, after I did that, I broke the linkage to almost all of my forms, queries, and reports. Is there an easy way to restore the links to a database field's new table location without rebuilding the forms, queries, reports from scratch again. If it is easier to point me in the direction of a training video, that would be just fine too.

Reply from Alex Hedley:

If the Field names are still the same you could join the Tables in a Query and then update your Form's RecordSource and the Controls on the Form should still work.

Abubakar Abdulrahim on 7/5/2015: Hi, I would like to know how to wrap the text on the top of fields or in cells , and how to activate the text auto complete like what available in excel?
regards,
Abubakar/UAE

Reply from Alex Hedley:

Access doesn't work the same way as Excel so things available there aren't always available here
Combo boxes have an autocomplete feature but that's on a Form

francisco on 7/6/2015: I have a small jumpers rental business jumpers for partys I chated with a Microsoft office support they said if I get Microsoft office 365 home and not business I can get audited and sued is this correct also what is best for me for the data base what software or should I just pay a subscription
Richard Coll on 8/12/2015: I have gone through GCFLearnFree.com's Access class, and did good. The problem is they worked with a database they created and didn't cover creating a database from scratch. Now our class has created a jam business and I want to create a database to track inventory, customers and sales. Which of your classes will cover this?

Reply from Alex Hedley:

The whole series of courses cover this, you will build a database for tracking customers, orders, stock etc.
Start and Beginner Level 1 and work your way through them.

Michael Newton on 8/12/2015: Richard, I viewed the 1st 10 videos for the Beginners course. When I select the Customer Reports video, it does not play. It just keeps going around in circles like it's thinking. What could be the issue, as I can play any of the 1st 10 sections?

Reply from Alex Hedley:

Could you try logging out and logging back in.
Clearing your cache
Trying a different browser.

Ronald Griffin on 8/17/2015: I am looking for Access 2013 beginner 2
program

Reply from Alex Hedley:

There isn't one for 2013. Just get B2. It's recorded using 2010 but there aren't many differences so you'll have no problem following along. Rich switches to 2013 in the later courses.

g sims on 8/19/2015: One a query is started, is there any way to abort it when you know a mistake was made?


Reply from Alex Hedley:

Try Esc or Ctrl+Break

Gerry on 8/20/2015: Where do I find "break," as in control break?

Reply from Alex Hedley:

With the Home, Insert etc.
If needs by bring up the osk (on screen keyboard) (Ctrl+R osk)

Gerry on 8/20/2015: I find it!
Gerry on 8/20/2015: I meant I "found" it. I was late for a meeting and didn't look at what I wrote:-(
g sims on 8/25/2015: You mention one can add prefixes and suffixes to names. How about when you need a comma, Esquire. How do you append a comma Esquire to last names?

Reply from Alex Hedley:

Is this for every person?

Gerry Sims on 8/31/2015: Yes, for every person I need the comma, Esquire.

Reply from Alex Hedley:

You can use String Concat
Just use & ", Esquire" to your Name Field and give it an Alias.

Tim Chavez on 9/20/2015: I have a comment. I was given advise by an Access user to give myself grace when making mistakes while working through the development of a database. Making mistakes is going to happen but over time concepts will come more easier. Thanks for the lesson.

Reply from Alex Hedley:

Everybody makes mistakes, it's what makes us better programmers and one of the reasons Rich leaves them in, nobody is perfect, it's making sure you learn from them that makes you better.

Tim Chavez on 9/20/2015: Question: Concerning Date/Time fields, have past practices ever involved using text fields for date/time data? Would this cause problems for future queries?

Reply from Alex Hedley:

Access has a data type of DateTime especially for Date Times,
If you use Text you will run into lots of problems later.
Yes you can use CDate to convert your String to a Date, but why go through that hassle.

Michael M Stevens on 10/8/2015: Hi Richard. In the database terminology session (Lesson 1 in Access 2010 Beginner 1), you discuss forms and mention there that we never want users working directly with our tables and queries (and that we'll see why in future lessons). I would like to enable users of my database to see a main menu when they log on, and to be able to work only with a form. May I ask where these topics are discussed in more detail, please?

Reply from Alex Hedley:

Beginner 2 is here which talks about Forms.

The full listing is here.
Searchable Course Index

Michael M S on 10/11/2015: Hi Alex. Thank you for your reply. Beginner 2 describes how to design and format forms, but stops short of describing how one controls access to the database by users. I am seeking information on how to permit users of my database to access and edit information only with the form I have designed, and not be able to access the table. Thank you.

Reply from Alex Hedley:

If you want full control you could take the Security Seminar.
More info from the Blog.

Andrew Washington on 10/25/2015: I track approximately 250 members' training requirements. Every member already has a unique SID. Would you suggest using their SID as my primary key vice a random autonumber? I like the idea because SIDs are a lot easier to remember and identify to my guys than a random number, but what do you think?

Reply from Alex Hedley:

You could do yes, or you could use both.

Guadalupe Pinon on 11/12/2015: I sat back and relaxed and watch courses 1 thru 7. I got just one small question which has been on my mind all along. How would you setup when there's an election of whether the person is a male or female? Help! ~ Wally

Reply from Alex Hedley:

Add another Field of Gender
Add a dropdown to the Form which options of Male and Female, I'd add other too.

MARTIN Waples on 12/10/2015: Just for the record I used your tip for shift f2 button and it controls my screen brightness, I looked for away to turn that function off any suggestions. Although I will never use the notes box in datasheet view just wanted to learn the shortcuts while doing the exercise to become aware

Reply from Alex Hedley:

Do you need to press the FN key too? Is there a blue colour over it, sometimes you have to do more than shift if its a laptop

chris f on 12/14/2015: I just discovered your courses via youtube and they are excellent. I am hooked because the information is exactly what I need. I am trying to figure out the price structure so I can get the most bang for the buck, additionally I just saw your information on CD courses for Christmas. Excellent idea and I think I want to take advantage of that offer for my family.

Reply from Alex Hedley:

The Order form will automatically add discounts based on the so you can see what your saving is before even buying anything.

Sherrie Branson on 12/30/2015: In planning my database I have clients where there might be multiple providers caring for them, as well as other multiple parties that might be providing input that are not related to the providers. Am I correct that I would create at least three tables in this scenario? One for clients; one for providers; and one for non-provider entities. I would also like to track provider expenses for each client. Is this yet another table? Thank you!

Reply from Alex Hedley:

Yep, just add a Table for each.
You could also use a Junction table say ClientXProvider where you join them so you know which Client has which Provider
See this blog post.

Richard Werbelow on 1/9/2016: with access, when entering a street address vs PO Box, how do you make a field for either. I suppose we could just enter "PO Box" in street # field??

Reply from Alex Hedley:

This depends how you are storing your Addresses in your Table, you could make a Field multi use and just switch a label on a Form, then search in that Field

Santa L on 1/10/2016: i was pay for MS Access 2013 but i got only MS 2010 only. i have seen youtube MS Access 2013 which was very nice. can you let me access MS Access 2013?
thaks

Reply from Alex Hedley:

The Beginner 1 was re-recorded for 2013.
It is mostly the same, the rest of the Beginner courses are 2010 and then in Expert about half way through it goes to 2013.

Pamela Smith on 1/12/2016: I am running access 2010, when I saved my first query (using my own data) I do not get separate section on the left hand side.. IE: your tables, queries. It is just a listing with the correct names but not separated by types. Not sure what happened.

Reply from Alex Hedley:

What option is showing at the top of the Navigation Pane?
There should be a drop down you can change to "ALL OBJECTS"

Pamela Smith on 1/13/2016: Thanks got it... had to click objects and then the the drop down menu...

Joe Sabo on 2/8/2016: I run a business that has three types of customers. They are retail, commercial, and industrial, where ie, retail requires less fields than commercial or industial customers. Additionally, we perform multiple services to some or all customers (not labor/time trackable) along with selling various types of inventory. Could you steer me in the right direction to construct tables?

Reply from Alex Hedley:

You could create 4 Tables, 1 with the generic values that all will use, then a new table for each of the three,
Now link them with a key.
You could then have sub forms or panels and only show those if they choose a type

Anonymous on 2/20/2016: What is the best approach if you are creating a mailing list and you have multiple people under the same address or you have the same person that has multiple addresses (vacation home). If I need a record for each person in the family to track birthdays but I want to be able to send a single mailing for the entire family for a holiday card, how should I set up the database? Do I create separate tables (1 that includes names and birthday's, etc.. and another that includes addresses) and create a relationship between the two tables or can I include all of the information in a single table and there is a way that I can indicate which address it should go to or when its a single mailing to multiple people.

Reply from Alex Hedley:

I'd have separate tables, you could then have an AddressID in the person table if you only want one, or a junction table of personid and addressid with as many records as you want, you could then do a groupby and if they all match for a family then only one would show for your report you wish to send.

Muhammad Osama on 5/27/2016: How do I export a large set of data from excel in to access? Also, will access match the field headers with excel file?

Reply from Alex Hedley:

Expert 20 shows this.

 

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