MS Access Guide

Posts

Part 2: Setting the Table in Access 2013

In this part . . . Continue building a well-structured database through the use of multiple tables. Customize your table fields to keep data consistent. Learn to efficiently edit your data. Create primary keys to keep your table data unique. Set up relationships between your database tables. Understand relationships for any kind of database and create and maintain indexes.

Adding and Removing Tables in Access

Nobody’s expecting perfection at this stage of the game. Certainly not in your first foray into database creation, and not even on your second or third attempt. Even seasoned experts forget things now and then, realizing after they’ve built a table that they didn’t need it, or after they’ve started setting up reports and queries that they’ve forgotten a table that they needed. It can happen to anyone. What to do? Use Access’s simple interface to add the tables you want and delete the tables you don’t. One more, please If, after you start building your database, you decide that your database warrants more than one table — in other words, if you realize you need a relational database — then you need to add another table. If you already knew that your database was going to need multiple tables, then — after building the first one — the only thing to do is build the rest, one by one. To add new tables to an existing database, repeat the following steps for each new table: 1. Click

Building a Database in Access

So you’ve read a few posts here at the beginning of the blog, maybe you’ve leafed ahead where I’ve referred to other chapters, and now you feel ready. You want to dive in and start building a database. Keeping in mind my previous advice to take it slowly, you can take a whack at it here. In the following procedure, you set up a new database and then use the Table Wizard to build the first table in the database. Ready? Here we go . . . 1. If Access is not already running, take a moment to start it. Chapter 1 shows you how to do this. In the Access workspace, a series of large template icons appears, below a Search for Online Templates box, accompanied by links to likely searches for templates that store Assets, Business, Contacts, Employee, and so on. 2. Click the Blank Desktop Database icon. A Blank Desktop Database dialog box appears, as shown in Figure 3-1. Figure 3-1: New blank databases need names. Give yours one here. 3. Type a name to replace the generic Databa

Choosing Between Flat and Relational Databases

Unlike ice cream, databases come in just two flavors: flat-file and relational. Also unlike ice cream, it’s not really a matter of preference as to which one you choose. Some databases require a relational approach; others would be overwhelmed by it. Read on to figure out how to tell the difference. Isolationist tables In a flat system (also known as a flat-file system), all the data is lumped into a single table. A phone directory is a good example of a flat-file database: Names, addresses, and phone numbers (the data) are crammed into a single place (the database). Some duplication occurs — if one person has three phone lines at home, his or her name and address are listed three times in the directory — but that’s not a big problem. Overall, the database works just fine. Tables that mix and mingle The relational system (or relational database) uses as little storage space as possible by cutting down on the duplicated (also known as redundant) data in the database. To a

Access Field Types and Uses

A field, you remember, is where your data lives. Each field holds one piece of data, such as Last Name or Batting Average. Because there are so many different kinds of information in the world, Access offers a variety of field types for storing it. In fact, Access puts the following field types at your disposal: Short Text Long Text Number Currency Date & Time Yes/No Lookup & Relationship Rich Text Attachment Hyperlink OLE Object Calculated There’s also an Autonumber field type, which is applied automatically to the first field in a new, blank database. The types just listed are those available for fields you create in addition to that first field — the ones that will contain your data. For now, suffice it to say that the Autonumber field is a field that contains an automatically-generated number so that each record is unique in that it has a unique autonumber, or ID. You get the word about the need for (and ways to create) unique fields later on, in Chapter

Access Database Lingo

Now, if the section heading (“Database Lingo”) is making you panic because you think I want you to memorize a bunch of database jargon, don’t worry. Just relax. Breathe normally. The next section, and many throughout this chapter, simply uses some terms you need to know so you can figure out what Access is referring to in its various dialog boxes as well on the various tabs it uses to give you access to commands in the Access workspace. Knowing these terms will, therefore, help you get around and get things done in Access. Unfortunately, you simply must know technical terms — there are no two ways about it. I’m talking about only a handful of words, though — some of which you probably already know and maybe even use in reference to information in general — words like record and database. See? Nothing high tech, just some basic words and concepts you really need to absorb so you can move on and use Access effectively. The terms in this section appear in size order, starting with t