Practice files for Step by Step Access - Microsoft Community.examples / Microsoft Access Step By Step · GitLab
Looking for:
Microsoft access 2013 step by step practice files free |
Microsoft access 2013 step by step practice files free. Microsoft Access 2013 Step by Step ebook - Home
Microsoft access 2013 step by step practice files free
- Microsoft access 2013 step by step practice files free
With Step by Step, you set the pace—building and practicing the skills you need, just when you them! Includes downloadable practice files and a companion eBook. Relational Database Design and Implementation: Clearly Explained, Fourth Edition, provides the conceptual and practical information necessary …. Your all-access guide to all things Access If you don't know a relational database from …. The complete guide to Excel Whether you are just starting out or an Excel novice, ….
Master database creation and management Access Bible is your, comprehensive reference to the world's most …. Skip to main content. Start your free trial. Book description Experience learning made easy—and quickly teach yourself how to build your own database with Access Build a database from scratch or ready templates Create easy-to-use data-entry forms Write queries to extract and manipulate data Design reports to summarize data in effective ways Import data from other databases and documents Show and hide more.
Table of contents Product information. Table of contents Introduction Who this book is for How this book is organized Download the practice files Your companion ebook Get support and give feedback Errata We want to hear from you Stay in touch I.
Simple database techniques 1. Explore Microsoft Access Identifying new features of Access If you are upgrading from Access If you are upgrading from Access If you are upgrading from Access Working in the Access user interface Identifying program window elements Working with the ribbon Understanding database concepts Exploring tables Exploring forms Exploring queries Exploring reports Previewing and printing database objects Key points 2.
Create databases and simple tables Creating databases from templates Creating databases and tables manually Manipulating table columns and rows Refining table structure Defining relationships between tables Key points 3. For example, you might want to embed an Orders datasheet in a Customers table so that the orders each customer has placed are visible in the context of the customer record. In this exercise, you'll open existing database tables and explore the table structure in two views.
For practice purposes, you might have saved this database with a different name. Open the database, ensure that tables are listed in the Navigation pane, and then follow the steps. The record navigation bar shows that the selected record is 1 of TIP To make the graphics in this book readable, from now on we will often work in a program window that is smaller than full screen with the Navigation pane closed.
More fields and records might be visible in your tables than those shown in our screen shots. Exploring tables 29 Let's adjust the width of a couple of columns to accommodate their longest entries. TIP You can also resize a table column by pointing to the border and dragging it to the left or right. Now let's open a second table. Notice that the Products table is still open and available if you need it. TIP From now on, open the Navigation pane whenever you need to work with a dif- ferent object, but feel free to close it if you want to display more of the data.
For information about relationships, see "Defining relationships between tables" in Chapter 2, "Create databases and simple tables.
Click the Close button at the right end of the page tab bar not the Close button in the upper-right corner of the program window to close the Categories table. Close the Products table, and when Access asks whether you want to save your changes to this table, click Yes. TIP In steps 3 and 4, you changed the look of the table by changing the widths of columns.
If you want those changes to be in effect the next time you open the table, you must save them. Next let's use a table containing order-fulfillment information to practice moving among records. In the Navigation pane, double-click the Orders table. On the record navigation bar, click the Next record button several times to move the selection down the active OrderlD field. Press the Page Up or Page Down key to move one screen at a time. Finally, let's view the structure of the open table.
Notice that the Design tool tab now appears on the ribbon. Press F1 for help on field names. Design view. Datasheet view displays the data stored in the table, whereas Design view displays the underlying table structure. Keep the GardenCompanyOl database open for use in later exercises. Working directly with tables in a database you have created might be quite simple for you, but it might be overwhelming for people who don't know much about databases in general or about this database in particular.
To make it easier to enter, display, and print information, you can design forms. A form acts as a friendly interface for a table. Through a form, you can display and edit the records of the underlying table, or create new records. Most forms provide an interface to only one table. However, by embedding subforms within a main form, you can use one form to interact with multiple tables that are related through one or more common fields. Forms are essentially collections of controls that either accept information or display infor- mation.
You can create forms by using a wizard, or you can create them from scratch by manually selecting and placing the controls. Access provides the types of controls that are standard in Windows dialog boxes, such as labels, text boxes, option buttons, and check boxes. With a little ingenuity, you can create forms that look and work much like the dialog boxes in all Windows programs. As with tables, you can display forms in several views. If necessary, open the database, and then follow the steps.
Click the arrow adjacent to the Supplier box to display a list of all the company's suppliers. This is an example of a list box control.
Now let's open a form that indudes a main form and a subform. In the Navigation pane, double-click Categories to open that form on its own tabbed page. Notice that the main form displays information from the Categories table, and the subform, which looks like a datasheet, displays information from the Products table for the current record.
Exploring forms 5 On the main form's record navigation bar, click the Next Record button a few times to display the next few records. Notice that the subform changes with each click to display the products in each category.
Next let's display a form containing customer information in various views. Notice that three tool tabs Design, Arrange, and Format appear on the ribbon. Switch between Form view, Layout view, and Design view, noticing the similarities and differences. Finally, let's take a look at the controls available for designing forms.
Either method displays a menu containing the Controls gallery. Exploring queries You can locate specific information stored in a table, or in multiple tables, by creating a query that specifies the criteria you want to match. They can also be quite complex — for example, you might want to locate all out- of-state customers who have purchased gloves within the last three months.
For the first example, you might be able to sort and filter the data in the Products table fairly quickly to come up with a list. For the second example, sorting and filtering would be very tedious.
It would be far simpler to create a query that extracts all records in the Customers table whose billing addresses are not in your state and whose customer IDs map to records that appear in the Orders table within the last three months and whose item IDs map to records classified as gloves in the Products table.
The most common type is the select query, which extracts matching records from one or more tables. Less common are queries that perform specific types of actions. Processing a query, commonly referred to as running a query or querying the database, displays a datasheet containing only the records that match your search criteria.
You can use the query results as the basis for further analysis, create other database objects such as reports from the results, or export the results in another format, such as an Excel spreadsheet. If you create a query that you are likely to want to run more than once, you can save it. It then becomes part of the database and appears in the list when you display the Queries group in the Navigation pane.
To run the query at any time, you simply double-click it in the Navigation pane. Each time you run the query, Access evaluates the records in the spec- ified table or tables and displays in Datasheet view the current subset of records that match the criteria defined in the query.
To set up a query, you work in Design view. Each box represents one table. In a query that works with more than one table, lines between the boxes indicate that before the query was created, relation- ships were established between the tables based on common fields. The relationships enable the query to draw information from the tables.
Each column of the grid refers to one field from one of the tables in the top pane. Each row defines a different aspect of the query. Don't worry if this all sounds a bit complicated at the moment.
When you approach queries logically, they soon begin to make perfect sense. In the Navigation pane, right-click the Delete Discontinued Products query, and then click Object Properties to display the properties of the query, including a description of its purpose. Let's run a query. Right-click the Products By Category query, and then click Open to run the query and display its results in a datasheet. If necessary, close the Navigation pane so that you can see all the results.
The Products table contains records. To find out why 18 of the records are miss- ing in the query results, let's look at this query in Design view.
Exploring queries 41 The two boxes in the top pane of the Query Designer list the fields in the Categories and Products tables. The line between the boxes indicates a relationship based on their common CategorylD field.
The design grid in the bottom pane defines a query that matches information from both tables. As an experiment, let's make a small change to the query design. Then on the Design tool tab, in the Results group, click the Run button to find all the records that have been discontinued. J 1 Products By Category. Bonsai mixed garden 1 ea. TIP You can also run a query by switching to Datasheet view. When a message asks whether you want to save your changes to the query, click No.
Chapter 1 Explore Microsoft Access ploring reports You can display the information recorded in your tables in nicely formatted, easily accessible reports, either on your computer screen or on paper.
A report can include items of informa- tion selected from multiple tables and queries, values calculated from information in the database, and formatting elements such as headers, footers, titles, and headings.
You'll also examine another report in Design view. Exploring reports 43 TIP Access provides a wizard that can help you create a mailing label report. You can also create labels like these by using the Customers table as a data source for the Microsoft Word mail merge tool. Move the pointer over the report, where it changes to a magnifying glass. Garden Company? Tejani Oak St. You can also adjust the zoom per- centage by clicking the Zoom In or Zoom Out button the plus or minus sign at the ends of the Zoom slider or by dragging the Zoom slider.
To set a specific zoom per- centage, click the Zoom arrow in the Zoom group on the Print Preview tab and then click the percentage you want.
Now let's look at another report. This report generates several pages of information by combining data from the Categories table and the Products table. On the page navigation bar in the lower-left corner of the page, click the Last Page button to move to the end of the report.
Click the Previous Page button a few times to view a few more pages of the report. Let's look at the structure of this report in Design view. On the View Shortcuts toolbar, click the Design View button. Notice that in this view, the report looks similar to a form. Keep the GardenCompanyOl database open for use in the last exercise. Previewing and printing database objects Because Access is a Windows application, it interacts with your printer through standard Windows dialog boxes and drivers.
This means that any printer that you can use from other programs can be used from Access, and any special features of that printer, such as color printing or duplex printing, are available in Access. The commands for printing database objects are available from the Print page of the Backstage view. Display the active object in Print Preview. Access will not print data that is not visible on the screen, so let's first make sure all the columns display all their data.
Don't worry about showing the complete column heading; just focus on the values. Print Select a printer, number of copies, and other printing options before printing. Print Preview Preview and make changes to pages before printing From the Print page of the Backstage view, you can print the current database object with the default print settings, change the settings, and preview the object. On the Print page, click Print Preview to preview the first page of the Employees table.
TIP This is the only way to preview a table, a query results datasheet, or a form. There is no Print Preview command available when you right-click one of these ob- jects, and there is no Print Preview button on the View Shortcuts toolbar or in the View button list, as there is for reports. Then click the First Page button to move back to page 1. With the current settings, this datasheet will print as two short, vertically oriented pages.
Let's adjust the settings. Then click the Next Page button. Notice that the datasheet still occupies two pages, with only one field on the second page. In the Page Size group, click the Margins button, and then click Narrow. In the Print group, click the Print button to open the Print dialog box. Previewing and printing database objects 49 10 11 12 13 14 Click Cancel to close the Print dialog box, and then in the Close Preview group, click the Close Print Preview button.
Now let's take a look at a report. On the View Shortcuts toolbar, click the Print Preview button to display the report information as it will be printed.
On the Print Preview tab, in the Zoom group, click the Two Pages button to display the first two pages of the report side by side. Employees j 13 Alphabetical List of Products. On the View Shortcuts toolbar, click the Report View button to return to that view. Then close the GardenCompanyOl database. They organize data in columns and rows, called fields and records.
For example, you view data in a table in Datasheet view and define how the data is structured in Design view. Microsoft Access takes a lot of the difficult and mundane work out of creating and customizing a database by providing database templates. Access also provides templates for common elements that you might want to plug into a database. These application parts consist of sets of objects — a table and related forms, queries, or reports — that together provide a complete, functioning part of a database, ready for you to customize.
If none of the templates meet your needs, you can create databases manually. However, an empty database is no more useful than an empty document or worksheet.
It is only when you fill a database with data referred to as populating a database , that it starts to serve a purpose. In this chapter, you'll examine web app templates and create a desktop database from a template. You'll also create a table manually. Next, you'll adjust the display of a table to meet your needs. Finally, you'll define relationships between tables. By the end of this chapter, you'll have a desktop database that contains a few tables and you'll understand a bit about how the database tables you will use for the exercises in the remaining chap- ters of the book were created.
By using predefined templates, you can create a database in far less time than it used to take to design one, because someone has already done the design work for you. After using a desktop database template to create the database, you can view and modify it by using Access on your local computer. They are designed to provide an online interface through which people can view and enter data from a web browser. Web apps make it possible for people to access company information from wherever they are and from any computer, whether or not it has Access installed.
Several of the templates that come with Access and many of the templates available from the Office website are for web apps. For more information, see the sidebar "Getting started with Access web apps" later in this chapter.
Although using an Access template might not produce exactly the database you want, it can quickly create something you can customize to meet your needs. However, you can customize a database only if you know how to manipulate its basic building blocks: tables, forms, queries, and reports. Due to the complexity of these templates, you prob- ably shouldn't try to modify them until you're comfortable working with database objects in Design view and Layout view.
By the time you finish this book, you will know enough to be able to confidently work with the sophisticated pre-packaged database templates that come with Access. In this exercise, you'll explore the templates that come with Access, and you'll create a desk- top database based on the Desktop Task Management template. Close any open databases, and then follow the steps. With either the Access starting screen or the New page of the Backstage view dis- played, scroll the page to display the range of available templates.
Notice that the icons of desktop database templates are designated by a blank page, and the icons of web app templates are designated by a stylized page with a globe. In the list of templates, click the Task management web app template icon.
Datasheet By Status Task management Provided by; Microsoft Corporation Plan and managetasks, and then assign them to employees, Like all Access 3 apps, the Asset tracking template requires Sharepoint so you can share content with others. Customize by adding more tables, new views of table data, or adding logic for your particular needs. Download size 18 KB Create your app, then use it and share it on the App Name My New App Web Location Get help finding your web I oca tin Clicking a web app template icon displays a description of the template and asks you to name the database and identify the SharePoint site on which it will be stored.
Creating databases from templates 55 TIP If no template seems to be a good starting point for the database you want to create, you can search for additional templates on the Office website by entering a category in the Search For Online Templates box and then clicking the Start Search- ing button.
To the left and right of the creation window, click the back and forward arrows to scroll through the list of available templates, reading their descriptions.
Stop when the creation window for the Desktop task management template is displayed. A i Desktop task management Provided by: Microsoft Corporation Create a tasks database to track a group of work items that you or your team need to complete.
Although you can use spaces between words, because database files are sometimes referenced in program- ming code, most database developers use words with initial capital letters and no spaces between them. Click the adjacent Browse button, and then in the File New Database dialog box, navigate to the Chapter02 practice file folder. You can change the location when you create each database, or you can change the default folder. To specify a different default folder, open the Access Options dialog box, and then on the General page, in the Creating Databases area, click the Browse button to the right of Default Database Folder.
In the Default Database Path dialog box, browse to the folder you want to be the default, and then click OK in each of the open dialog boxes. With the path to the specified folder displayed below the File Name box in the window, click the Create button. We recommend that you watch these two short videos to help you understand this database and Microsoft Access.
Once you've had a chance to use the Tasks database, please let us know how we're doing. Provide Feedback 0 Show Getting Started when this database is opened This Getting Started window is a form that provides two videos to help you understand how to use and modify your MyTasks database.
Creating databases from templates 57 8 If you want, watch the videos to understand how this template works. Then clear the Show Getting Started when this database is opened check box, and close the window. Then if any of the groups are collapsed, click their chevrons to open them. All Arrpss Ohie TIP Below the form name is a toolbar that has commands created by embedded macros.
A database that has commands like these is called a database application. The topic of macros is beyond the scope of this book. For information, search for macros in Access Help. Notice that the task you just entered in the form is the first record in this table. Let's use an application part to add a form to this new database. It is now easier to focus on just the seven forms in the database.
On the Create tab, in the Templates group, click the Application Parts button to display the Application Parts gallery. TIP These ready-made objects give you a jump start on creating a fully functional database application.
But like templates, they involve behind the scenes functionality that you might not know how to manage yet. You can come back and explore appli- cation parts more fully when you have a better understanding of them. Creating databases from templates 59 15 Point to each thumbnail in turn to display its description, and then in the Blank Forms area, click 1 Right. Notice that when you add this form to the database, Access adds an eighth form called SingleOneColumnRightLabels to the Forms group in the Navigation pane.
It also runs a macro that opens the Task List form. X Search. For each type of object, first filter the Navigation pane to display only that group, and then explore its objects. Office plans that include SharePoint Small Business Premium and Enterprise are ready to host Access web apps with no extra setup required.
To create an Access web app, you must first log in to Office with a SharePoint enabled account. This account might not be the same as your Windows account. Deployment, management, and security of the web app are all controlled within the SharePoint infrastructure. To create a web app: 1 Start Access, and in the upper-right corner of the program's starting screen, verify that you are logged in to Office with a SharePoint-enabled account.
If the account shown is not a SharePoint-enabled account, click Switch account, and then click the account you want to use; or click Add Account and enter the appropriate credentials.
TIP If you aren't sure which template to choose, enter a keyword in the Search box at the top of the pane, and search online for suggestions. Then click Create to download the template and create the application on the designated SharePoint site. Service, Sell Jiz it!
CI ckan item in ttie result list and "tw"l be added c. From the initial web app screen, you can add or view existing objects. The tables you add to the web app are displayed as tiles in the Tile pane on the left side of the page. For each table, Access automatically creates an associated Datasheet form and a List form. Clicking a table shows you an image of the table's form in the right pane. To edit a table's form, display the form in the view you want, and click the Edit button.
You can manage the data source and actions of a form's fields, in addition to the formatting of the form itself, in the List and Datasheet form views. To edit a table, right-click the table's tile, and click Edit Table. You can also open the Navigation pane and edit the tables and forms from there. The wizard guides you through the process of establishing the relationship. For in- formation about using this wizard, see "Allowing only values in lists" in Chapter 6, "Maintain data integrity.
To make the Access web app available to other users, simply share the web address of the SharePoint site with them. When they launch the web app in their web browser, they can view and manipulate its data by using the buttons on the Action bar located to the right of the Search box to add, delete, edit, save, and cancel record edits. Their changes are saved in the centrally stored SQL database. TIP If you need to make more extensive changes to the database, you can click the Settings button in the upper-right corner of the web app and click Customize In Access to open the database in Access on your local computer.
Creating databases from templates 63 Creating databases and tables manually Suppose you need to store different types of information for different types of people. For example, you might want to maintain information about employees, customers, and suppli- ers. However, cramming all this information into one table would soon get messy. It's better to create a new database based on the Blank Desktop Database template and then man- ually create separate tables for each type of contact: employee, customer, and supplier.
When you create a new blank database or insert a new table into an existing database, the table is displayed on a tabbed page in Datasheet view with one empty row that is ready to receive data.
TIP When you create a new database, Access displays its name and a path to its storage location in the title bar. For information about how to reduce the clutter caused by the path, see "Controlling which features are available" in Chapter 11, "Make databases user friendly.
If you close the table at this point, Access discards the table. The simplest way to make the table a permanent part of the database is to create at least one record by entering data. TIP When you enter data in a new table, Access assigns placeholder field names and cre- ates a basic table structure to hold the data.
You can also define the structure of the table without entering data. For information about table structure, see "Refining table structure" later in this chapter. For information about adding new blank fields to a table, see "Specify- ing the type of data" in Chapter 6, "Maintain data integrity. Creating databases and tables manually 65 Every table has an empty row that is ready to receive a new record, as indicated by the as- terisk icon in the record selector at the left end of the row.
By default, the first field in each new table is an ID field designed to contain an entry that will uniquely identify the record. Also by default, this field is designated as the table's primary key. No two records in this table can have the same value in this primary key field. Behind the scenes, the data type of this field is set to AutoNumber, so Access will enter a sequential number in this field for you. TIP As you'll discover in a later exercise, the primary key field does not have to be the default AutoNumber data type.
If you need to you create your own primary key field, anything meaningful and unique will work. For information about data types, see "Refining table structure" later in this chapter.
The first field you need to be concerned about is the active field labeled Click To Add. You enter the first item of information for the new record in this field, and then press the Tab or Enter key to move to the first cell in the field to the right. Access then assigns the value 1 to the ID field, assigns the name Fieldl to the second field, and moves the Click To Add label to the third field.
The icon in the record selector at the left end of the record changes to two dots and a pencil to indicate that this record has not yet been saved, and the New icon moves to the record selector of the next row. When creating a new table in Datasheet view, you need to save the first record after enter- ing the first item of data. If you don't, Access increments the ID value for each field you add to that record.
For example, if you add seven fields, Access assigns the value 7 to the ID field of the first record. To avoid this problem, you simply click the icon in the record selector after you enter your first value in the first record. This saves the record with the value 1 assigned to the ID field, and subsequent records will be numbered sequentially. Having entered the first item of data and saved the record, you continue entering items of information in consecutive fields and pressing Tab or Enter.
When you finish entering the last item for the first record, you click anywhere in the row below it to tell Access that the record is complete. After you complete the first record of a new table, you might want to change the default field names to something more meaningful.
To rename a field, you simply double-click its field name and then enter the name you want. At any time while you are entering data in a new table, you can save the table by click- ing the Save button on the Quick Access Toolbar and naming the table. If you try to close the table without explicitly saving it, Access prompts you to save the table.
If you click No, Access discards the table and any data you have entered. After you have saved the table for the first time, Access automatically saves each record when you move away from it. You don't have to worry about losing your changes, but you do have to remember that most data entries can be undone only by editing the record. Databases almost always contain more than one table. You can create additional empty tables by clicking the Table button in the Tables group on the Create tab.
If you need to create a table that is similar in structure to an existing one, simply copy and paste the exist- ing table to create a new one.
When you paste the table, Access gives you the option of naming the table and of specifying whether you want the new table to have the existing table's structure or both its structure and its data.
Creating databases and tables manually 67 For some kinds of tables, Access provides Quick Start fields that you can use to add com- mon sets of fields or kinds of fields to a table. The Quick Start options take the work out of defining these fields and can be very useful when you know exactly what type of field you need. In this exercise, you'll create a blank database, enter information in the first record of its default table, assign field names, add another record, and save and close the table.
Then you'll copy that table to create a second one. Finally, you'll create a new table and experi- ment with Quick Start fields. Close any open databases, display the New page of the Backstage view, and then follow the steps.
Then click the Browse button, navigate to the Chapter02 practice file folder, and click OK. TIP Remember, you can't create a blank database without saving it. If you don't pro- vide a file name and location, Access saves the file with the name Database followed by a sequential number in the default location your Documents folder, unless you have changed it. Let's enter data in the first record. The value 1 appears in the ID field, the name of the second column has changed to Fieldl, and the Click to Add label has moved to the third column.
TIP Clicking the record selector is necessary only after you enter the first value in a new table. This action sets the ID field value to 1.
Press Tab after each entry. Chapter 2 Create databases and simple tables Bishop E. TIP Don't be concerned if your screen does not look exactly like ours. In this graphic, we've scrolled the page and adjusted the widths of the columns to display all the fields. For information about adjusting columns, see "Manipulating table columns and rows" later in this chapter. Before we move to the next record, let's make the field names more useful.
TIP Field names can include spaces, but the spaces can affect how queries have to be constructed, so it is best not to include them. Creating databases and tables manually 69 10 Add another record containing the following field values to the table, pressing Tab to move from field to field: 11 12 13 FirstName John LastName Yokim Street 43 rue St.
At the right end of the tab bar, click the Close button. When Access asks whether you want to save the design of the table, click Yes to open the Save As dialog box. TIP Clicking No will delete the new table and its data from the database. All Access Obje.. Tables H Customers The database now contains one table. You can delete a table by right-clicking it, clicking Delete, and then con- firming the deletion in the message box that appears.
You can also delete a table by selecting it in the Navigation pane and then clicking the Delete button in the Records group on the Home tab, or by pressing the Delete key. In the Navigation pane, click the Customers table to select it. On the Home tab, in the Clipboard group, click the Copy button. Then click the Paste button to open the Paste Table As dialog box. In the Table Name box, enter Employees.
In the Paste Options area, click Structure Only to capture the fields from the Customers table but not the customer records. Then click OK to create the table and add it to the Tables group in the Navigation pane. TIP You can also use the Copy and Paste commands to append the information in the selected table to another existing table.
Double-click Employees to open it in Datasheet view so that you can view its fields. Then close the table again. This is because Access has automatically assigned this default value to this field.
For information about de- fault values, see "Allowing only values in lists" in Chapter 6, "Maintain data integrity. Then scroll to the bottom of the gallery. In our practice databases, we use the field name Country. However, you might want to use the more explicit Country Region field name if the tables you create will store in- ternational addresses.
If you're capturing the same information in multiple places, that is a sure sign that you need to analyze the data and figure out a way to put the duplicated information in a separate table. For example, an Orders table should not include information about the customer plac- ing each order, for two significant reasons. First, if the same customer orders more than once, all his or her information has to be repeated for each order, which inflates the size of the Orders table and the database.
Second, if the customer moves, his or her address will need to be updated in the record for every order placed. The way to avoid this type of problem is to put customer information in a Customers table and assign each customer a unique identifier, such as a sequential number or unique string of letters, in the primary key field.
Then in the Orders table, you can identify the customer by the unique ID. If you need to know the name and address of the customer who placed a particular order, you can have Access use the unique ID to look up that information in the Customers table. The process of ensuring that a set of information is stored in only one place is called normalization. This process tests a database for compliance with a set of normalization rules that ask questions such as "If I know the information in the primary key field of a record, can I retrieve information from one and only one record?
A detailed discussion of normalization processes is beyond the scope of this book. For more information, see Access Help, or search for Database design basics on the Office website. Creating databases and tables manually 73 nipulating table columns and rows In Chapter 1, "Explore Microsoft Access ," we showed you how to quickly adjust the width of table columns to efficiently display their data.
In addition to adjusting column width, sometimes you might want to rearrange a table's columns to get a better view of the data. For example, if you want to look up a phone number but the names and phone numbers are several columns apart, you will have to scroll the table to get the information you need. You might want to rearrange or hide a few columns to be able to simultaneously view all the fields you are interested in.
You can manipulate the columns and rows of an Access table without affecting the under- lying data in any way. You can size rows and size, hide, move, and freeze columns.
You can save your table formatting so that the table will look the same the next time you open it, or you can discard your changes without saving them.
In this exercise, you'll open a table and manipulate its columns and rows. If necessary, open the database. Then follow the steps. TIP This technique is particularly useful in a large table in which you can't easily determine the length of a field's longest entry.
Now let's adjust the height of the table's rows. Row Height? Row Height: In the Row Height dialog box, select the Standard Height check box, and then click OK to reset the height of the rows to the default setting.
Next let's experiment with hiding columns. Click anywhere in the FirstName column. Then in the Records group, click the More button, and click Hide Fields. You can select adjacent columns by clicking the field name of the first one, holding down the Shift key, and then clicking the field name of the last one. The two columns and any columns in between are selected.
To restore the hidden column, in the Records group, click the More button, and then click Unhide Fields to open the Unhide Columns dialog box. TIP If you want to hide several columns that are not adjacent, display the Unhide Columns dialog box and clear their check boxes. Let's freeze the first three columns so that they remain in view as you scroll the table. With these three columns selected, click the More button in the Records group, and then click Freeze Fields. TIP The commands to hide, unhide, freeze, and unfreeze columns are also available from the shortcut menu that appears when you right-click a field name.
Suppose we want to always display the customer's phone number next to his or her name. Let's move the Phone column. Then drag the field to the left, releasing the mouse button when the thick black line appears to the right of the LastName field.
If a message box appears, warning you that this action will clear the Clipboard, click Yes. Refining table structure Although you can create the structure of a database in Datasheet view, some structural refinements can be carried out only in Design view. When you are familiar with tables, you might even want to create your tables from scratch in Design view, where you have more control over the fields.
You can open a new table in Design view by clicking the Table Design button in the Tables group on the Create tab. When you display an existing table in Design view, the page shows the underlying structure of the table. Refining table structure 77 HOME if.
The table design page consists of two parts: a field definition grid and a field properties area. Click any field selector to select the entire field. You can then insert a row above the selected one, delete the row thereby deleting the field , or drag the row up or down to reposition its field in the table. The field selector also identifies the primary key field of the table by displaying the Primary Key icon a key with a right-pointing arrow. TIP If you don't want a table to have a primary key for example, if none of the fields will contain a unique value for every record , select the field designated as the pri- mary key, and on the Design tool tab, in the Tools group, click the Primary Key button to turn it off.
If you want to designate a different field as the primary key, select the new field, and click the Primary Key button to turn it on. You don't have to remove the primary key from the current field first; it will happen automatically.
You can edit the names by using regular text-editing techniques. You can add a new field by entering its name in the first empty cell in this column. By default, the ID field in a new table is assigned the AutoNumber data type, and if you add a new field in Design view, it is assigned the Short Text data type. If you add a new field in Datasheet view, it is assigned the data type that most closely corresponds with the kind of data you enter in the field. With the exception of fields with the OLE Object and Attachment data types, you can change the type of any field by clicking its Data Type entry, clicking the arrow that appears, and clicking a new data type in the list.
Clicking an entry in the Data Type column displays a list of the available data types. The Field Properties area in the bottom part of the table design page displays the proper- ties of the field selected in the top part. Different properties are associated with different data types. They determine attributes such as the number of characters allowed in a field, the value inserted if the user doesn't enter anything, and whether an entry is required.
Properties can also assess whether an entry is valid and can force the user to select from a list of values rather than entering them manually with the inherent risk of errors.
Refining table structure 79 All fields, no matter what their data type, can be assigned a Caption property that appears in place of the field name in tables or in other database objects.
For example, you might want to use captions to display spaces in the names of fields, such as First Name for the FirstName field. In this exercise, you'll open a table in Design view, add and delete fields, change a data type, set field sizes, and add a caption.
This table was created by copying the Customers table. Let's make a few changes to the fields. Then click the Data Type cell, which indicates that the default Short Text data type is assigned to the new field. Press F1 for help on data types. The Field Properties area shows the properties you can set for the Short Text data type. Now let's change some of the properties for the PostalCode field. In the box to the right of Field Size, double-click , and enter 10 to limit the entries in this field to 10 characters.
If you make a change to a field property that might cause data to be lost for example, if you make the Field Size property smaller than one of the field's existing values , Access warns you of this problem when you attempt to save the table.
For more information, see Chapter 6, "Maintain data integrity. If you don't enter a caption, the field name is used as the label. Press F1 for help on captions. You have changed the Field Size and Caption properties of the State field. Notice that in the top part of the design page, the entry in the Field Name column remains State. Then switch to Datasheet view. When Access tells you that you must save the table before leaving Design view, click Yes to save the table.
If necessary, widen the State field, and notice that in this view, the field name is replaced by the State or Region caption property. While the table is displayed in Datasheet view, let's add another field. Click the LastName field name. TIP You can also click the Click To Add label to the right of the last field in the field name row, and then in the list that appears, click the data type you want. Then on the Fields tool tab, in the Properties group, click in the Field Size box to select it, enter 50, and press the Enter key.
Defining relationships between tables In Access, a relationship is an association that links the primary key field in one table to a field that contains the same information in another table. The field in the other table is called the foreign key. For example, if customer accounts are assigned to specific sales employees, you can establish a relationship by linking the primary key EmployeelD field in the Employees table with the foreign key EmployeelD field in the Customers table.
Each customer account is assigned to only one employee, but each employee can manage many customer accounts, so this type of relationship — the most common — is known as a one-to- many relationship. Similarly, if every order is associated with a customer, you can establish a relationship by linking the primary key CustomerlD field in the Customers table and foreign key CustomerlD field in the Orders table.
Each order is placed by only one customer, but each customer can place many orders. So again, this is a one-to-many relationship. This type of relationship isn't commonly used because it is easier to put all the fields in one table.
However, you might use two related tables instead of one to break up a table with many fields, or to track informa- tion that applies to only some of the records in the first table. You might find this relationship in a database that contains Products, Orders, and Order Details tables. The Products table has one record for each product, and each product has a unique ProductlD.
The Orders table has one record for each order placed, and each record in it has a unique OrderlD. However, the Orders table doesn't specify which products were included in each order; that information is in the Order Details table — the table in the middle that ties the other two tables together. Products and Orders each have a one-to-many relationship with Order Details. Products and Orders therefore have a many-to- many relationship with each other. In plain language, this means that every prod- uct can appear in many orders, and every order can include many products.
The most common way of creating a relationship between two tables is to add the tables to the Relationships page displayed when you click the Relationships button in the Relationships group on the Database Tools tab. You then drag a field in one table to the common field in the other table and complete the relationship definition in the Edit Relationships dialog box.
In this dialog box, you are given the opportunity to impose a restriction called referential integrity on the data, which means that an entry will not be allowed in one table unless it already exists in the other table. After you have created a relationship, you can delete it by deleting the line connecting the tables on the Relationships page. You can clear all the boxes from the page by clicking the Clear Layout button in the Tools group on the Design tool tab. TIP The coverage of relationships in this topic is deliberately simple.
However, relationships are what make relational databases tick, and Access provides a number of fairly complex mechanisms to ensure the integrity of the data on either end of the relationship. Some of these mechanisms are covered in Chapter 6, "Maintain data integrity.
Chapter 2 Create databases and simple tables TIP Access web apps don't use the Relationships page to create and manage relationships.
Instead, they use fields set to the Lookup data type. For information about this data type, see "Allowing only values in other tables" in Chapter 6, "Maintain data integrity. Then you'll test the referential integrity of one of the relationships. Before we add fields to this table, let's save it. Repeat this step to create a second field that has the Number data type. Each order in the Orders table will be placed by one customer and will be handled by one employee.
Let's create relationships between the Orders table and the Customers and Employees tables so that we don't create records for orders from customers who don't exist or that seem to have been handled by employees who don't exist. TIP You cannot create a relationship for an open table. To indicate that you want to create a relationship for the selected Customers table, click Add.
Then double-click Orders, and click Close. Then on the Relationships page, notice that the CustomerlD field appears in the field lists of both tables. Chapter 2 Create databases and simple tables In the Customers field list, click CustomerlD, and drag it down and over CustomerlD in the Orders field list. Release the mouse button to open the Edit Relationships dialog box. Select the Enforce Referential Integrity check box, and then click Create.
Notice on the Relationships page that a line now connects the two field lists, linking the primary key in the Customers table and the foreign key in the Orders table.
Defining relationships between tables 87 Let's add the Employees table to the Relationships page so that we can create a relationship that links that table to the Orders table. Then in the Show Table dialog box, double-click the Employees table, and click Close. TIP You can also add tables by dragging them from the Tables group of the Naviga- tion pane to the Relationships page. Now let's test the relationships. Then in the CustomerlD field of the first record, change the 0 value to 11, and click below the record to complete it.
Access displays a message box telling you that you cannot add the new record to the table. Microsoft Access You cannot add or change a record because a related record is required in table 'Customers'. Help Was this information helpful? The value in the CustomerlD field in the Orders table must match a value in the primary key CustomerlD field in the Customer table.
Then change the value to 1. Access accepts the record because there is a record with the value 1 in the primary key CustomerlD field of the Customers table and a record with the value 1 in the primary key EmployeelD field of the Employees table. You can also set the data type and certain properties. Caution: changing some properties might affect the data.
A database that contains the day-to-day records of an active company is useful only if it is kept current and if the information stored in it can be found quickly. Entering, editing, and retrieving information from tables in Datasheet view is fairly easy for someone who is famil- iar with Access. But for occasional users or people who are not familiar with Access, these tasks might be tedious and inefficient and leave far too much room for error, especially if details of complex transactions have to be entered into several related tables.
The solution to this problem is to create and use forms. A form is an organized and formatted view of some or all of the fields from one or more tables. Forms work interactively with the tables in a database. You use controls in the form to enter new information, to edit or remove existing information, or to locate information. Think of a text box control as a little window through which you can insert data into the corre- sponding field of the related table or view information that is already in that field.
TIP An Access form can also include a variety of other controls that transform the form into something very much like a Windows dialog box or wizard page.
Comments
Post a Comment