Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Access 2010 Essential Training, Alicia Katz Pollock gives a comprehensive overview of creating databases in Access 2010, whether using predefined database templates or building from scratch. This course covers each step of constructing and modifying databases for custom purposes, as well as working with tables, forms, queries, macros, and reports and charts for record keeping and analysis. Exercise files are included with the course.
Once you have your fields set up, good database designs suggests that you set field properties to control what data can be entered into the fields. The more specific you are the fewer data entry errors you have. You can set the most common field properties in Datasheet View, and all of them within Design View. Let's start with out Customers Table. Double-click on it and then come up to Fields Ribbon and click on that. First, we have our Company name field. Let's come over here and make it Required by checking this box.
That means that you have to enter something into it. You can't leave it blank. Next let's look at the Field Size. Access reserves this number of spaces for every field, even if you don't use it. Limiting the Field Size to the actual number of characters needed will help your database file size stay small. Field Size is 255 characters by default, which, for this field, is way to big. Let's edit that down to 50 characters, but be sure not to make this Field Size too small, or you'll cut off your information. I'll click back on Company to accept the value.
I'll get an error message that some data may be lost. "The setting for the Field Size property of one or more fields has been changed to a shorter size. If data is lost, validation rules may be violated as a result. Do I want to continue anyway?" Now since I haven't actually entered much data into this database there is not going to be a problem. I'm just going to go ahead and click Yes. So now let's take a look at another field and its properties. Scroll over to the right until you find State/Province and click on it.
First, let's change its name and caption. We don't need Province, and our reports will look better if it just says State. So click on the Name & Caption button. Now notice that Name is StateProvince with the words run together and no punctuation. Field names cannot contain certain punctuation marks that are used in programming: periods, commas, question marks, slashes and asterisks. You also can't start along with a space. This convention of using capital letters for each word and running them together is sometimes called Camel Caps.
A caption, on the other hand, can have spaces and punctuation because it's not used in behind-the-scenes programming. It only shows up in your forms and reports. So let's change both the Name and the Caption to State and click OK. There it is. Now let's set as Default Value. Most of my customers are in New York, so instead of having to type New York, New York, New York every time, I can have this field autofill. To do that, click in it, and then on this Default Value button. The equal sign is used in Access coding to tell the program that you are about to enter in a data value.
Type in a quotation mark. This is used in Access coding to indicate that you are entering letters. Type in capital NY and another quotation mark. When you click OK, you'll see New York in the field. If a customer is from a different state, you can just write over the New York without issue. This just saves some time. Last, all states will have just two characters, so change the Field Size to 2. You'll get that same error message, and yes, we do want to continue.
Close the table and if asked to save, say Yes. So now let's open up the Sales Reps Table to set a few more Field Properties, and this time we'll use the Design View. Double-click on Sales Reps, and we'll use this button. Now notice that there's a top-half and a bottom-half. The top-half will toggle back and forth between Datasheet View and Design View. The bottom-half will let you choose them off of the list. You can also toggle back and forth using these buttons down here. Right now we're in Design View, and I can toggle back and forth with datasheet by using these.
Now click on the LastName field and make it Required using this setting right down here. Use the dropdown and click Yes, and let's do the same thing for FirstName, Required > Yes. And then let's make the same changes to State that we did before, so that you can see how they look in this view. Make the Field Size 2. Change the Caption to State, and while we're at it, let's change the Field Name up here as well.
Make the Default Value "NY" and notice when you click off, that will add quotation marks by itself. Another property we can set here is to force the State to appear in capital letters, no matter how it's typed in. Click in Format and type a Greater Than sign - that's a Shift+period - which will force the characters to capitals. If you want lowercase, use a less then sign. I also want to point out the date formats. Scroll down and click on StartDate and then on the Format dropdown. You can see different Date and Time formats.
Long Date gives us the day of the week, the month, day and year. Medium Date writes it out like this and Short Date, like this. Let's set the StartDate to Short Date, and then let's do the same thing for End Date, Format, drop down, Short Date. Now lets close and Save the Sales Reps Table, and now let's open up the Products Table. Another way to get directly into Design View is to right-click on the Object name and then choose Design View off the shortcut list.
Let's start with the SKU. None of our SKU's are more than six characters, so let's change the Fields Size here to 6, skip down to Required and make it Yes, and also make sure that Indexed says Yes. This will cause Access to process the SKU field so that searches happen more quickly. Now click on the Size Field. Numbers have several different Size options. Click on Field Size and drop it down. Long Integer is the most common for whole numbers.
Single is the most common for numbers with decimals. We want our size to be an Integer. Now Save and Close the Products table, and let's set our last properties in the Orders table. Double-click on Orders, and this time right-click on the Orders tab to toggle over to Design View. Let's work with the Order Date. First, make the Format Short. Now, let's set it up so the default date is today's date. Click on Default Value, and then on the far right use this little builder button, type an equal sign, and then double-click on Functions, and then double-click on Built-In-Functions.
Click on Date and then scroll down and find Now. And it will put it up in the top with parentheses, and click OK. Now, when I switch over to Datasheet View, I'll Save it, and it will put today's date in the Order Date field. We'll test all these Field properties at length in Chapter 4 of this course. By taking the time to lock down your Field properties, you'll be sure that your data entry will be as clean as possible.
After all, a database is only as good as the data entered into it.
Find answers to the most frequently asked questions about Access 2010 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.