The examples in this tutorial present common features of databases used on campus. Go to Start > Programs > Microsoft Office > Microsoft Access 1. Open the Windows Start menu and choose All Programs. 2. Select Microsoft Office then Microsoft Office Access Tip: You can drag the Microsoft Access . Microsoft Office Access Inside Track / John Pierce, Paul Pardi. p. cm. Includes Adobe Reader is required to view the PDF version of the book. The CD.
|Language:||English, Spanish, Dutch|
|Distribution:||Free* [*Registration needed]|
If your question is about Microsoft Office Access , and not about the content of this Microsoft Press book, your first recourse is Access's. What is a relational database? Microsoft Office Access —a relational database—is made up of groups of related data stored in individual tables. Users can. Microsoft Access is a powerful, yet easy to learn, relational database By using this tutorial, you will learn to perform the following operations in Access position the cursor over Microsoft Office and then click on Microsoft Access.
Many people start using Access when the program that they are using to keep track of something gradually becomes less fit for the task. For example, suppose you are an event planner, and you want to keep track of all the details that you need to manage to make your events successful.
If you use a word processor or spreadsheet program to do this, you can easily run into trouble with duplicate and inconsistent data. You can use calendaring software, but tracking financial information in a calendar isn't a good fit.
For example, an event planning relational database might contain a table with customer information, a table with vendor information, and a table with event information. The table with event information might have a field to relate it to the customer table, and a field to relate it to the vendor table. That way, for example, if a vendor's phone number changed, the information could be changed once in the vendor table, instead of in every event that involved the vendor.
Access is a tool that you can use to quickly and easily develop relational database applications that help you manage information. You can create a database to help you keep track of just about any kind of information, such as inventory, professional contacts, or business processes. In fact, Access comes with templates that you can use right away to track a variety of information, making things easy even for a beginner. When you open Access When you start Access , you see the Microsoft Office Backstage view, where you can get information about the current database, create a new database, open an existing database, and view featured content from Office.
Backstage view also contains many other commands that you can use to adjust, maintain, or share your databases.
Commands in Backstage view generally apply to entire databases, not to objects inside of a database. Top of Page Find and apply a template Access provides you with a wide variety of templates that you can use to speed up your database creation process. A template is a ready-to-use database that contains all of the tables, queries, forms, and reports needed for performing a specific task. For example, there are templates that can be used to track issues, manage contacts, or keep a record of expenses.
Some templates contain a few sample records to help demonstrate their use. Template databases can be used as is, or you can customize them to better fit your needs. To find and apply a template to your database, do the following: On the File tab, click New. To use a template that you already have installed, click My Templates, and then select the template that you want.
To find a template on Office. You can also search for templates on Office. However, you can sort them any way you want when you need to retrieve information later on. Based on the simple analysis you performed earlier, you know that you need to enter four fields of information for every doll.
Although you could start with any field, it makes sense to begin with the name, which is clearly an identifying detail. Then, hit Tab to jump to the second column, and then enter the second piece of information. Ignore the ID column for now—Access adds that to every table to identify your records. Press Tab to move to the next field, and return to step 2.
You may notice one quirk—a harmless one—when you add your first record. The only problem with this example so far is that as you enter a new record, Access creates spectacularly useless field names. You see its choices at the top of each column they have names like Field1, Field2, Field3, and so on.
The problem with using these meaningless names is that they may lead you to enter a piece of information in the wrong place. You could all too easily put the download price in the date column. If you make a mistake, you can backtrack using the arrow keys. Most people prefer to see the entire contents of a column at once. To expand a column, just position your mouse at the right edge of the column header. To expand a column named Field1, move your mouse to the right edge of the Field1 box.
Then, drag the column to the right to resize it as big as you want. Move the mouse over the right edge of the column, so it turns into a two-way arrow. Then, simply double-click the column edge. Double-click the first column title like Field1. The field name switches into Edit mode. Type a new name, and then press Enter.
To specify better field names, double-click the column title. Next, type the real field name, and then press Enter. You can always rename fields later, or even add entirely new fields. Type a suitable table name, and then click OK. The table is now a part of your database. As you can see, creating a simple table in Access is almost as easy as laying out information in Excel or Word.
But before you get to that stage, it makes sense to take a closer look at how you edit your table. Editing a Table You now have a fully functioning albeit simple database, complete with one table, which in turn contains one record. Your next step is filling your table with useful information. This often-tedious process is data entry. To fill the Dolls table, you use the same datasheet you used to define the table.
You can perform three basic tasks: Editing a record. Move to the appropriate spot in the datasheet using the arrow keys or the mouse , and then type in a replacement value. You may also want to use Edit mode, which is described in the next section. Inserting a new record. At that point, Access creates the row and moves the asterisk down to the next row.
You can repeat this process endlessly to add as many rows as you want Access can handle millions. Deleting a record. You have several ways to remove a record, but the easiest is to right-click the margin immediately to the left of the record, and then choose Delete Record. Every ounce of information is important. For example, imagine you have a database that lists the products that a mail-order origami company has for sale.
But it turns out that it makes sense to keep these old product records around. For example, you might want to find out what product categories were the best sellers over the previous year. Or maybe a manufacturer issues a recall of asbestos-laced paper, and you need to track down everyone who ordered it. To perform either of these tasks, you need to refer to past product records. This hang-onto-everything rule applies to any kind of database.
You need them all and you probably need to keep them indefinitely. You can then ignore those products when you build an order-placement form. So settle in. To make your life easier, it helps to understand a few details. As you already know, you can use the arrow keys to move from field to field or row to row. However, you may have a bit of trouble editing a value.
When you start typing, Access erases any existing content. Instead, you get to change or add to it. To switch out of Edit mode, you press F2 again. Top: Normal mode. Bottom: Edit mode. In Edit mode, the arrow keys move through the current field.
For example, to move to the next cell, you need to move all the way to the end of the current text, and then press the right arrow key again.
But in Normal mode, pressing the arrow keys always moves you from cell to cell. This key also turns off Edit mode. In Edit mode, this key moves the cursor through the text in the current field. Home Moves the cursor to the first field in the current row. End Moves the cursor to the last field in the current row. Page Up Moves the cursor up one screenful. This key works only if you use it in Edit mode. Once you move to the next cell, the change is applied.
For additional cancellation control, try the Undo feature, described next. This trick is handy when you need to enter a batch of records with similar information. An Access user has been on an site downloading binge and needs to add several doll records.
Cut, Copy, and Paste Access, like virtually every Windows program, lets you cut and paste bits of information from one spot to another. However, Access has a little-known ability that lets you copy an entire record.
To pull it off, follow these steps: Click the margin to the left of the record you want to copy. This selects the record. Right-click the selection, and then choose Copy. This copies the content to the Clipboard. Scroll to the bottom of the table until you see the new-row marker the asterisk. Right-click the margin just to the left of the new-row marker, and then choose Paste. Presto—an exact duplicate. Access updates the ID column for your pasted record, giving it a new number.
It automatically saves any edits you make to the records in a table. This automatic-saving process takes place every time you change a record, and it happens almost instantaneously. The rules are a bit different for database objects Understanding Access Databases. When you add or edit a database object, Access waits until you finish and close the object, at which point it prompts you to save or discard your changes.
Note Remember, when you click File, you enter Backstage view, which provides a narrow strip of commands on the left and a page with options for the currently selected command on the right. To open the database for read-only access, so that you can view it but cannot edit it, click the arrow next to the Open button, and then click Open Read-Only. To open the database for exclusive access, so that no one else can open it while you have it open, click the arrow next to the Open button, and then click Open Exclusive.
To open the database for read-only access, click the arrow next to the Open button, and then click Open Exclusive Read-Only Other users can still open the database, but they only have read-only access.
If you can't find the database that you want to open. In the list of drives, right-click the drive that you think might contain the database, and then click Search. If the database is found, open it by double-clicking it in the Search Results dialog box. Because the search was initiated from the Open dialog box, you must click Cancel in that dialog box before the database will open.
Access automatically creates a new Access database in the same folder as the data file, and adds links to each table in the external database. Depending on the template that you used, you might need to do one or more of the following to get started with the new database:.
If Access displays a Login dialog box with an empty list of users, use the following procedure to get started:. Select the user name you just entered, and then click Login. If Access displays an empty datasheet, you can begin typing data directly into that datasheet, or click other buttons and tabs to explore the database. If Access displays a Getting Started page, you can click links on that page to learn more about the database, or click other buttons and tabs to explore the database.
If Access displays a Security Warning message in the message bar, and you trust the source of the template, click Enable Content. If the database requires a login, you will need to log in again.
For desktop and web databases, you also want to begin with one of these steps:. Add a table and then input data into the table. Import data from other sources, creating new tables in the process. You can add a new table to an existing database by using the tools in the Tables group on the Create tab.
In a web database, only the Table command is available in the Tables group. Regardless of which view you start in, you can always switch to the other view by using the view buttons on the status bar of the Access window.
Field names are assigned numerically Field1, Field2, and so on , and Access sets the field data type, based on the type of data that you enter.
On the Create tab, in the Tables group, click Table. Access creates the table, and then places the cursor in the first empty cell in the Click to Add column. Give a meaningful name to each field, so that you can tell what it contains without having to look at the data. To move a column, select it by clicking its column heading, and then drag it to where you want it.
You can also select several contiguous columns and drag them all to a new location together. Then you either switch to Datasheet view to enter data, or you enter your data by using some other method, such as with a form.
Design view is not available for tables in a web database. On the Create tab, in the Tables group, click Table Design. For each field in your table, type a name in the Field Name column, and then select a data type from the Data Type list. You can enter additional information for each field in the Description column.
The description that you enter is displayed in the status bar when the insertion point is in that field, and is used as the status bar text for any controls that you create by dragging the field from the Field List pane to a form or report, and for any controls that are created for that field by the Form Wizard or Report Wizard. You can begin entering data into the table at any time by switching to Datasheet view, clicking in the first empty cell, and typing.
You can also paste data from another source, as described in the next section. Moreover, list data is stored on a server, which generally offers better protection against data loss than files stored on a desktop computer. You can either start with a new list, or you can link to an existing list. You must have sufficient permissions on the SharePoint site where you want to create the list; this can vary by site so contact your SharePoint administrator for details about your options.
This feature is not available in a Web database. You can use one of the list templates to create a standard SharePoint list, such as Contacts or Events. You can also choose to create a custom list, or to link to or import an existing list.
Click the option that you want. If you choose any of the list templates or to create a custom list, the Create New List dialog box opens to guide you through the process. If you choose to use an existing list, the Get External Data dialog box opens to help you.
For more information about tables, see the article Introduction to tables. If your data is currently stored in another program, such as Excel, you can copy and paste your data into an Access table. In general, this works best if your data is already separated into columns, as they are in an Excel worksheet. If your data is in a word processing program, you should first separate the columns of data by using tabs, or convert the data into a table in the word processing program, and then copy the data.
If your data needs any editing or manipulation for example, separating full names into first and last names , you might want to do this before you copy the data, particularly if you are not familiar with Access. When you paste data into an empty table, Access sets the data type of each field according to what kind of data it finds there. Access names the fields depending on what it finds in the first row of pasted data. If the first row of pasted data is similar in type to the rows that follow, Access determines that the first row is part of the data and assigns the fields generic names Field1, Field2, and so on.
If the first row of pasted data is not similar to the rows that follow, Access uses the first row as field names, and excludes the first row in the actual data. If Access assigns generic field names, you should rename the fields as soon as possible to avoid confusion.
Use the following procedure:. On the File tab, click Save to save the table. In Datasheet view, double-click each column heading, and then type a name for the column. You might have data that you've been collecting in another program that you would like to use in Access. Perhaps you work with people who store their data in other programs, and you want to work with their data in Access. Or, maybe you have multiple disparate sources of data, and need a "landing pad" where you can bring them all together for deeper analysis.
Access makes it easy to import or link data from other programs. You can bring in data from an Excel worksheet, from a table in another Access database, from a SharePoint list, or from any of a variety of other sources. The process differs slightly depending on the data source, but these instructions will get you started:.
For example, if you are importing data from an Excel worksheet, click Excel. If you don't see the correct program type, click More. In the Get External Data dialog box, either click Browse to browse to the source data file, or type the full path of the source data file in the File name box. Click the option that you want under Specify how and where you want to store the data in the current database. You can create a new table by using the imported data, or create a linked table that maintains a link to the data source.
Depending on your choice, the Link Objects dialog box or the Import Objects dialog box opens. Use the dialog box to finish the process.
The exact procedure you follow depends on the import or link option that you chose. If you chose to import, Access asks whether you want to save the details of the import operation that you just completed. If you think that you will be performing this same import operation again, click Save import steps , and then enter the details. You can then easily reproduce the import operation by clicking Saved Imports in the Import group on the External Data tab, clicking the import specification, and then clicking Run.
If you don't want to save the details of the operation, click Close. Access imports the data into a new table, and then displays the table under Tables in the Navigation Pane. To learn more about how to import data into Access, follow the links in the See Also section of this article. The rest of the design process varies based on your goals, but you probably want to consider creating queries, forms, reports, and macros.
This article does not provide information about creating additional database objects. For more information, see these articles:. Introduction to queries. Introduction to forms. Introduction to reports in Access. If you are familiar with other database or spreadsheet programs, you probably know the basics of how these applications work and what databases are used for. Access differs from many other database applications by allowing you to create relational databases, and it further differs from most spreadsheet programs by offering tools for developing sophisticated queries, forms, and reports.
Access also provides many options for working with other database programs, such as SQL Server. Many people begin to explore Access after first building a list in Excel.
Excel is a great place to start a list, but as the list grows, it becomes harder to organize and keep updated. Moving the list to Access is usually the next logical step. A database table is similar in structure to a worksheet, in that data is stored in rows and columns. As a result, it is usually easy to import a worksheet into a database table. The main difference between storing your data in a worksheet and storing it in a database is in how the data is organized.
Simply importing your entire worksheet as a new table in a database will not solve the problems associated with organizing and updating your data, particularly if your worksheet contains redundant data.