- What Is a Relational Database?
- What Types of Things Can I Do with Microsoft Access?
- A Preview of the Database Components
This chapter is from the book
This chapter is from the book
This chapter is from the book
A Preview of the Database Components
As mentioned previously, tables, queries, forms, reports, macros, and modules combine to compose an Access database. Each of these objects has a special function. The following sections take you on a tour of the objects that make up an Access database. The examples use the sample Northwind database to illustrate the use of each object. If you want to follow along, you can create the Northwind database as covered in Chapter 2, “Getting Started with Microsoft Access.” You can log in as any user, which will take you to the Home form. Close the Home form to follow along.
Tables: A Repository for Data
Tables are the starting point for an application. Whether data is stored in an Access database or you reference external data (such as data in an Excel spreadsheet) by using linked tables, all the other objects in a database either directly or indirectly reference tables.
To view all the tables that are contained in an open database, you select Tables from the list of objects available in the database (see Figure 1.1). A list of available tables appears (see Figure 1.2).
){kind=link}
)
Figure 1.1. To view the tables in a database, select Tables from the list of available objects.
Figure 1.2. You can view the tables contained in a database.
To view the data in a table, double-click the name of the table you want to view. (You can also right-click the table and then select Open.) Access displays the table’s data in a datasheet that includes all the table’s fields and records (see Figure 1.3). You can modify many of the datasheet’s attributes and even search for and filter data from within the datasheet; these techniques are covered later in this chapter.
)
Figure 1.3. A table’s datasheet contains fields and records.
If the table is related to another table (such as the Northwind database’s Customers and Orders tables), you can also expand and collapse the subdatasheet to view data stored in child tables (see Figure 1.4).
)
Figure 1.4. Datasheet view of the Customers table in the Northwind database.
As an Access user, you often want to view the table’s design, which is the blueprint or template for the table. To view a table’s design (see Figure 1.5), right-click the table name in the Navigation Pane, and then select Design View. In Design view, you can view or modify all the field names, data types, and field and table properties. Access gives you the power and flexibility you need to customize the design of tables. Chapter 3, “Tables: The Repository for Your Data,” and Chapter 9, “Creating Your Own Tables,” cover these topics.
)
Figure 1.5. The design of the Customers table.
Relationships: Tying the Tables Together
To properly maintain data’s integrity and ease the process to work with other objects in a database, you must define relationships among the tables in a database. You accomplish this by using the Relationships window. To view the Relationships window, select Relationships from the Database Tools tab of the Ribbon. The Relationships window appears. In this window, you can view and maintain the relationships in the database (see Figure 1.6). If you or a fellow user or developer have set up some relationships, but you don’t see any in the Relationships window, you can select All Relationships in the Relationships group on the Design tab of the Ribbon to unhide any hidden tables and relationships.
)
Figure 1.6. The Relationships tab, where you view and maintain the relationships in a database.
Many of the relationships in Figure 1.6 have join lines between tables and show a number 1 on one side of the join and an infinity symbol on the other. This indicates a one-to-many relationship between the tables. If you double-click a join line, the Edit Relationships dialog box opens (see Figure 1.7). In this dialog box, you can specify the exact nature of the relationship between tables. The relationship between the Customers and Orders tables in Figure 1.7, for example, is a one-to-many relationship with referential integrity enforced. This means that the user cannot add orders for customers who don’t exist. Notice in Figure 1.7 that the Cascade Update Related Fields check box is not selected. This means that if the user cannot update the CustomerID field. Because Cascade Delete Related Records is not checked in Figure 1.7, the user cannot delete from the Customers table customers who have corresponding orders in the Orders table.
)
Figure 1.7. The Edit Relationships dialog box, which enables you to specify the nature of the relationships between tables.
Chapter 10 extensively covers the process to define and maintain relationships. For now, you should establish relationships both conceptually and literally as early in the design process as possible. Relationships are integral to successfully design and implement your application.
Queries: Stored Questions or Actions You Apply to Data
Queries in Access are powerful and multifaceted. A query retrieves data from your database based on criteria you specify. An example is a query that retrieves all employees who live in Florida. Select queries enable you to view, summarize, and perform calculations on the data in tables. Action queries enable you to add to, update, and delete table data. To run a query, first close the Relationship window if you still have it open. Next select Queries from the Objects list and then double-click the query you want to run. Or you can click in the list of queries to select the query you want to run and then right-click and select Open. When you run a Select query, a datasheet appears, containing all the fields specified in the query and all the records meeting the query’s criteria (see Figure 1.8). When you run an Action (Append, Update, Delete, or Make Table) query, Access runs the specified action, such as making a new table or appending data to an existing table. In general, you can update the data in a query result because the result of a query is actually a dynamic set of records, called a dynaset, based on the tables’ data. A dynaset is a subset of data on which you can base a form or report.
)
Figure 1.8. The result of running the Product Orders query.
When you store a query, Access stores only the query’s definition, layout, or formatting properties in the database. Access offers an intuitive, user-friendly tool that helps you design queries: the Query Design window (see Figure 1.9). To open this window, select Queries from the Objects list in the Navigation Pane, choose the query you want to modify, right-click, and select Design View.
)
Figure 1.9. The design of a query that selects data from the Customers table.
The query pictured in Figure 1.9 selects data from the Customers table. It displays the Company, Job Title, Work Phone, Home Phone, and Mobile Phone from the Customers table. Chapter 4, “Using Queries to Retrieve the Data You Need,” Chapter 11, “Enhancing the Queries That You Build,” and Chapter 12, “Advanced Query Techniques,” cover the process of designing queries. Because queries are the foundation for most forms and reports, they are covered throughout this book as they apply to other objects in the database.
Forms: A Means to Display, Modify, and Add Data
Although you can enter and modify data in a table’s Datasheet view, you can’t control the user’s actions very well, nor can you do much to facilitate the data-entry process. This is where forms come in. Access forms can have many traits, and they’re flexible and powerful.
To view a form, you select Forms from the Objects list. Then you double-click the form you want to view or right-click in the list of forms to select the form you want to view and then click Open. Figure 1.10 illustrates a form in Form view. This Customer Details form is actually two forms in one: one main form and one subform. The main form displays information from the Customers table, and the subform displays information from the Orders table (a table related to the Customers table). As the user moves from customer to customer, the form displays the orders associated with that customer. When the user clicks to select an order, the form displays the entire order.
)
Figure 1.10. The Customer Details form, which includes customer, order, and order detail information.
Like tables and queries, you can also view forms in Design view. The Design view provides tools you may use to edit the layout of your form. To view the design of a form, you select Forms from the Objects list, choose the form whose design you want to modify, and then right-click and select Design View. Figure 1.11 shows the Customer Details form in Design view. Chapter 5, “Using Forms to Display and Modify Information,” Chapter 13, “Building Powerful Forms,” and Chapter 14, “Advanced Form Techniques,” cover forms in more detail.
)
Figure 1.11. The design of the Customer Details form.
Reports: Turning Data into Information
Forms enable you to enter and edit information, but with reports, you can display information, usually to a printer. Figure 1.12 shows a report in Preview mode. To preview any report, select Reports from the Objects list. Double-click the report you want to preview or right-click the report want to preview from the list of reports in the Navigation Pane, and then click Open. Notice the report in Figure 1.12. It shows the Monthly Sales Report which outputs the sales by product for a month. If you attempt to run this report, Access loads the Sales Reports Dialog form. Here you select how you want to view the sales, the sales period, and the year, quarter, or month as appropriate. For the example, I selected Sales by Product, Monthly Sales, 2006 for the year, and June for the month. Like forms, reports can be elaborate and exciting, and they can contain valuable information.
)
Figure 1.12. A preview of the Monthly Sales report.
As you may have guessed, you can view reports in Design view, as shown in Figure 1.13. To view the design of a report, select Reports from the Objects list, select the report you want to view, and then right-click and select Design View. Figure 1.13 illustrates a report with many sections; in the figure, which shows the Design view of the Invoice report, you can see the Page Header, Order ID Header, Detail section, Order ID Footer, and Page Footer (just a few of the many sections available on a report). Just as a form can contain subforms, a report can contain subreports. Chapter 7, “Using Reports to Print Information,” Chapter 15, “Building Powerful Reports,” and Chapter 16, “Advanced Report Techniques,” cover the process of designing reports.
)
Figure 1.13. Design view of the Invoice report.
Macros: A Means of Automating a System
Macros in Access aren’t like the macros in other Office products. You can’t record them, as you can in Microsoft Word or Excel, and Access does not save them as Visual Basic for Applications (VBA) code. With Access macros, you can perform most of the tasks that you can manually perform from the keyboard, Ribbon, and QuickAccess toolbar. Macros enable you to build logic in to your application flow.
To run a macro, select Macros from the Objects list, and then double-click the macro you want to run. Or you can right-click the macro and click Run. Access then executes the actions in the macro. To view a macro’s design, you select Macros from the Objects list, select the macro you want to modify, right-click, and select Design View to open the Macro Design window (see Figure 1.14). The macro pictured in Figure 1.14 opens the form called Startup Screen, and then opens the form called Login Dialog. Chapter 17, “Automating Your Database with Macros,” and Chapter 18, “Advanced Macro Techniques,” cover the process of building and working with macros.
)
Figure 1.14. The design of a macro that opens two forms.
Modules: The Foundation of the Application Development Process
Modules, the foundation of any complex Access application, enable you to create libraries of functions that you can use throughout an application. You usually include subroutines and functions in the modules that you build. A function always returns a value; a subroutine does not. By using code modules, you can do just about anything with an Access application. Figure 1.15 shows an example of a module called PurchaseOrders. You can double-click the module in the Navigation Pane to access the module code. This will take you to the Visual Basic Editor (VBE) where you can view and modify the programming code. To return to the Access environment, click the View Microsoft Access toolbar button, or use the Alt-F11 keystroke combination.
)
Figure 1.15. The PurchaseOrders module in Design view, showing the General Declarations section and the Generate and Create functions.