Once the storyboarding and brainstorming processes are completed, learn how the salesperson can turn the results into a database—a series of related tables—packed with the information that helps them manage and improve their sales process.
- [Instructor] After starting a new database, using the blank database template, Access gives me my starting table, table one and I'm going to add my first fields to it. As I do so, I'm going to show you a few tips to speed up the table building process. First, let's switch to design view. I'm gonna right click the tab for table one and choose design view. I could also do it from this view button here on the ribbon but I'm going to choose design view here. It forces me to save the table and that's a good idea.
So I'm gonna call it customers and click OK. Here I am in design view of now the customer's table. The first field that it gives you, and all fields that you create in Access, start with this field. It's the ID field. It's the primary key field. I'm going to suggest that you append the table name to the ID field. So I'm gonna type customer. And the reason I'm doing this is so that if I ever see all the ID fields for all of my tables in one place I can tell them apart because each one of them, following this tip, will include the table name as part of the ID name.
Now I'm gonna use the down arrow which puts me in the next row and I'm ready to put in my own first field that's gonna hold data in this table. And I'm going to call it company name. Notice that I did not put a space between company and name. The reason for that is that if I ever want to export this database to another database application, by making the field names one string I eliminate the possibility of the other application perceiving a single field name as two. If I had a space between company and name it could think that those were two separate fields.
By running the two words together I eliminate that possibility. Now watch what happens when I hit the down arrow to do my next field. It makes the data type for company name Short Text. And that is the default data type for all fields in all Access tables. The reason that it's the default is because the vast majority of the fields you build will, in fact, be Short Text fields. Now I'm gonna put in my next field, contact first name, another Short Text field, and then contact last name.
Now my next tip pertains to the size of these fields. If I go back and click on company name notice that the field properties area fills up and I'm seeing that the field size for this field, as well as contact first name and contact last name, all of the fields that I create that are left as Short Text fields, their size will be 255 characters. That's much more space than we need. No company name is going to have that many characters. So I'm gonna reduce this to 50 and reduce the two name fields to 25 characters.
So why am I doing that? Access has a limit of two gigabytes for the total size of the database file. You can still put in hundreds of thousands of records. That doesn't mean that you can't store a lot of data. But you're going to make sure that you don't hit that two gigabyte limit if you keep all of your fields pretty lean. So whenever possible, reduce them to roughly the size that you think you'll need. Don't go crazy and try and make it a perfect size, just a round number that's a few more characters than you would possibly need but way less than 255.
So that's it. Some basic tips that I hope you'll keep in mind as you build the rest of your tables in our sales database and in the databases you build on your own in the future.
- Planning your sales database
- Building tables
- Choosing data types
- Setting up field validations
- Creating lookup tables
- Building and editing relationships
- Creating forms
- Designing queries
- Creating sales reports
- Generating useful macros
- Designing a convenient navigation form