2  Exploring Candy database

OBJECTIVES

  • Understanding sample database candy365.accdb.
  • What to do with security alerts.
  • The windows and navigation options within Access.
  • The display capabilities of tables, forms, reports and queries.
  • How a command button works on a form.
  • How to search, sort and filter a record.
  • Printing tables, queries, forms and reports.

The course is built around the sample file candy365.accdb. This chapter explains the design and use of the tables in the database. You are going to experiment with this sample database, you go some experimenting to discover different aspects of working with Access. Some of the features are further explored in other chapters.

2.1 Example database Candy

The example database candy365.accdb contains data of the company SNOOPY, a company that sells boxes with pralines to customers. In the boxes are different types of pralines. The information is kept in 6 tables:

  • Customers
  • Orders
  • OrderDetails
  • Boxes
  • BoxDetails
  • Pralines
Figure 2.1: Design database Candy.

The customer data are in the table Customers. The orders of the customers are stored in the tables Orders and OrderDetails. The data of a box pralines, for example, name and price, are stored in the table Boxes. In the table BoxDetails you can find which pralines and how much of them are in each box. The table Pralines contains information about the name of the pralines, chocolate type, filling, and even includes a picture.

Each table should include a column or set of columns that uniquely identifies each row stored in the table. This is often a unique identification number, such as an article number or an employee number. In database terminology, this information is called the[primary key]of the table. The value of the primary key can occur only once in the table. Duplicate values for the primary key are prohibited. Most of the tables have a primary key consisting of one field, but sometimes it is necessary to combine fields to achieve a unique combination. In the tables Customers, Orders, Boxes, and Pralines the key is one field. In the tables OrderDetails and BoxDetails two fields are required for the key. See Figure 2.1.

The six tables are shortly discussed below.

2.1.1 Table Customers

Figure 2.2: Table Customers.

Various details of a customer are stored in the table Customers. Every customer has a unique customer code. The field CustomerCode is the primary key of the table. Each row of a table is called a record. Customers are sorted by the values of the primary key.

Check that there are 325 customers in table Customers.

2.1.2 Table Orders

Figure 2.3: Table Orders.

In the table Orders is OrderCode the primary key, the value of OrderCode is unique. A certain value of CustomerCode can appear more than once in this table because a customer can have multiple orders. An order is always associated with one customer.

Check that there are 784 orders in table Orders.

2.1.3 Table OrderDetails

Figure 2.4: Table OrderDetails.

An order can contain multiple boxes, but each BoxCode can only occur once in an order. However, a certain box can be ordered in multiple orders, so that a BoxCode can belong to multiple values of OrderCode. table OrderDetails has 1537 records (order rows).

Check that two different boxes were ordered on the order with order code 3, because the table contains two lines with order code 3. A total of three boxes were ordered on this order, two boxes with the box code CHER and 1 box with the box code NOWI.

The combination of OrderCode and BoxCode is always unique. Therefore, the key in the table is a combination of these two fields.

The total number of ordered boxes on all orders is the sum of all the numbers in the column Quantity. This number is not present in the table. In another chapter of this course will be explained how you can calculate it.

2.1.4 Table Boxes

Figure 2.5: Table Boxes.

In the table Boxes is BoxCode the key. There are 18 different boxes, each with a unique BoxCode. This table is the Snoopy company’s article list.

2.1.5 Table BoxDetails

Figure 2.6: Table BoxDetails.

In table BoxDetails is stored what kinds of pralines and how many of them are in a certain box. So you can read that there are six kinds of pralines in box ALLS, two of each, a total of twelve pralines. In this table, the key is a combination of the fields BoxCode and PralineCode. The table has 84 records.

2.1.6 Table Pralines

Figure 2.7: Table Pralines.

In table Pralines, some properties of each kind of praline are stored. The key is the field PralineCode. The table has 41 records (praline types).

You can easily make some calculations based on the data from the tables.

  1. What is the average number of praline types per box? For this you have to divide the total number of records in the table Box details by the total number of box types. So \(\frac{84}{18}= 4.7\).

  2. What is the average number of box types per order? This can be calculated from the total number of order rows divided by the total number of orders. So \(\frac{1537}{784} = 2.0\).

You can’t easily calculate the average number of pralines in a box. To do that, you need the total number of pralines in all boxes. This is the sum of all numbers in the column Quantity in the table BoxDetails.

2.2 Security and macros

How to deal with security warnings when opening a database.

Some databases such as candy365.accdb contain macros. In most cases, these macros are made to carry out certain tasks in the database. However, virus writers can use these opportunities to spread a virus. When such a database is not on a trusted location or signed by a trusted publisher, then Access displays when opening the database a security warning on the opening of the database. The macros in the database are disabled.

Figure 2.8: Security warning on opening of a database with macros.

You can enable the macros in one of the following ways.

Enable macros temporarily

Click in the area with the security alert on the buttonEnable Content.

This method is not recommended because every time you open the database this warning appears you to enable macros.

Ad publisher to list with trusted publishers

If the database creator that has provided a digital certificate, you can add the creator to the list Trusted Publishers. Access automatically enables all macros that are created by this maker in all databases. This is a very secure method, but usually, only the larger companies use a digital certificate. The database candy365.accdb doesn’t have a digital certificate.

Move database to a trusted location

Access provides trusted locations (folders). Macros in databases on a trusted location are automatically enabled. This is the most convenient way to work safely without interruptions by security alerts and this method is also recommended for the database candy365.accdb. Perform this action as follows.

Choose File > Options > Trust Center > Trust Center Settings… > Trusted Locations > Add new location….

Figure 2.9: Dialog box Microsoft Office trusted location.

In the example the folder C:\temp is added as a trusted location.

Security settings for all macros

Optionally you can change the way Access handles all macros in all databases. Perform this action as follows.

Choose File > Options > Trust Center > Trust Center Settings… > Macro Settings

Figure 2.10: Dialog box Trust Center.

It is not recommended to enable all macros, then you will lose the security control.

2.3 Exploring database

For this exploration you must have opened database candy365.accdb.

When a security warning is displayed, you should you take action, see Section 2.2.

Figure 2.11: Access with the Snoopy database with opened group Tables.

The Navigation Pane is on the left side and in it are all objects of the database. The objects are divided into groups: Tables, Queries, Forms, Reports, Macros. The pane of the group Tables is unfolded in the example. The navigation pane itself and the panes of the groups can be folded and unfolded.

  • - selection of showed objects

  • - unfolding group pane

  • - folding group pane

  • - folding navigation pane

  • - unfolding navigation pane

Double-clicking on an object in the Navigation Pane will open the object and displays the content in a document window. When you have opened several objects then these are displayed as tabbed documents.

Figure 2.12: Document window with the Customers and Orders tables in tabs.

You can change this setting through File > Options > Current Database. In Figure 2.13 you see the possibilities for the document windows.

Figure 2.13: Document Window Options.

When you select Overlapping Windows, then the objects are displayed in separate windows, above each other.

If you need more space in width you can fold up the navigation pane. Do you need more space in height, then you can hide the ribbon by double-clicking on a ribbon tab. Double-clicking again on a tab unhide the ribbon.

2.4 Exploring Tables

Tables are the key components of a database since all data is stored here.

The two most important views of a table are

Datasheet View

In this view, you can see the contents of the records, change it, and also allows you to add new records.

Design View

In this view, you can see the design of the table and customize it.

Task 2.1 File: candy365.accdb

  1. Open the database.

  2. Open the table Customers. The table opens in Datasheet View.

  3. Put the table Customers in the Design View on one of the following ways:

    • Click in the bottom right of the program window on the button Design View
    • Choose tab Home > View (group Views) > Design View
    • Right-clicking on the table name in the navigation pane and then choose Design View from the shortcut menu.

  4. Put the table Customers in the Datasheet View again on one of the following ways:

    • Click in the bottom right of the program window on the button Datasheet View
    • Choose tab Home > View (group Views) > Datasheet View
    • Right-clicking on the table name in the navigation pane and then Open

  5. Close the table Customers via the Close button X, top right of the document window.

  6. Open the table Orders in the Datasheet View.

In the table Orders is a unfold button[+]in front of the records. By clicking on it you see the details of the order. These data come from table OrderDetails. That the associated details of a particular order can be found is possible because in both tables the field OrderCode is present.

  1. Click the unfold button at a few records to see which boxes and how much of them are ordered.
Figure 2.14: Orders details of order 3.
  1. Close the table Orders.

2.5 Exploring Forms

Forms are especially important in displaying, adding, and editing data.

The most important views of a form are:

Form View

In this view, the information is displayed, you can edit it and enter new data.

Datasheet View A view similar to that of the table.

Design View

In this view, you can see the design of the form and customize it.

Task 2.2 File: candy365.accdb

  1. Open the database.

  2. Open form Pralines. The form opens in the form view.

Figure 2.15: Form View of form Pralines. The data of the first record is displayed.

  1. Put form Pralines in Design View on one of the following ways:

    • Click in the bottom right of the program window on the button Design View .
    • Choose tab Home > View (group Views) > Design View
    • Right-clicking on the form name in the navigation pane and then choose Design View from the shortcut menu.
Figure 2.16: Design view of form Pralines.

In the design view, you can change the layout of the form and add controls like text boxes, labels, list boxes, checkboxes, etc.

  1. Put the form Pralines in the Form View with .

  2. Browse through the records with the navigation buttons on the left bottom of the document window.

    • - First record

    • - Previous record

    • - Next record

    • - Last record

    • - New (empty) record

  3. Close the form.

2.6 Using command button

The form Boxes can be used for data entry but also for viewing data. On this form is a command button with the caption Sales

Task 2.3 File: candy365.accdb

  1. Open the database.

  2. Open the form Boxes.

  3. Click the button Sales on the form. The form Box sales is displayed. You will see all orders related to the selected box in the form Boxes and even the total sales of this box.

  4. Close all forms.

2.7 Searching a record

A form may also be used to search for a record. In the following steps, you will search for a box with “autumn” in the name.

Task 2.4 File: candy365.accdb

  1. Open the database.

  2. Open the form Boxes.

  3. Click in the field Box name and choose on the ribbon tab Home > Find (group Find). The dialog box Find and Replace is displayed.

  4. Type autumn in the text box Find What and select Any Part of Field in the text box Match.

Figure 2.17: Dialog box find and replace.

  1. Click Find Next. The content of the box Autumn Surprise is displayed.

  2. Click Cancel to close the search window.

  3. Close the form.

2.8 Exploring Queries

Queries are important if you want to select or summarize data from tables. A query is a specified question to the database to provide certain information.

The two most important views of a query are:

Datasheet View

In this view, you can see the result (data) of the query.

Design View

In this view, you can see the design of the query and customize it.

Task 2.5 File: candy365.accdb

  1. Open the database.

  2. Open the query Pralines per box.

Access executes the query and produces a data sheet containing the results of the query, see Figure 2.18.

The results of this query are derived from multiple tables. To know which tables are used, you had to go to the design view of the query.

Figure 2.18: Query pralines per box in datasheet view.

  1. Put the query Pralines per box in the Design View on one of the following ways:

    • Click in the bottom right of the program window on the button Design View .
    • Choose tab Home > View (group Views) > Design View.
    • Right-clicking on the query name in the navigation pane and then choose Design View from the shortcut menu.
Figure 2.19: Design query Pralines per box.

The upper three rectangles represent the tables that are used for this query. The line with arrows connects the field names by which two tables are related to each other.

In the lower part of this window, you will find in the first row the names of the used fields. In the third column is an expression, a kind of formula with operations on the fields. The expression formulated here is Sum([Pralines].[PralineCosts]*[BoxDetails].[Quantity]). Between the brackets, you see the names of the tables and fields.

To see the whole expression the column must be widened by dragging the column to the right edge.

  1. Put the query in the Datasheet View.

  2. Close the query

2.9 Exploring Reports

With reports, you can print the information from the tables nicely laid out on screen, display, or paper. The displayed data may be derived from multiple tables and/or queries. Also, calculated values are possible. Furthermore, you can provide a report with titles, headings, headers, and footers.

The most important views of a report are:

Report View

In this view, you can see the contents of the report.

Print Preview

The display of the report when it will be printed.

Design View

In this view, you can see the report of the design and customize it.

Task 2.6 File: candy365.accdb

  1. Open the database.

  2. Open the report Pralines per box. The report is opens in the report view.

Figure 2.20: Report pralines per box in report view.

  1. Put the report Pralines per box in Print Preview on one of the following ways:

    • Click in the bottom right of the program window on the button [Print Preview]
    • Choose tab Home > View (group Views) > Print Preview.
    • Right-clicking on the report name in the navigation pane and then choose Print Preview from the shortcut menu.

  2. Put the report Pralines per box in Design View on one of the following ways:

    • Click in the bottom right of the program window on the button Design View
    • Choose tab Home > View (group Views) > Design View.
    • Right-clicking on the report name in the navigation pane and then choose Design View from the shortcut menu.
Figure 2.21: Report pralines per box in design view.

In the design view, you can change the layout of the report and add controls like text boxes, labels, list boxes, checkboxes, etc.

  1. Put the report in [Report View] with .

  2. Close the report

2.10 Sorting

You can sort the records in a table based on the values in one or more fields. Sorting can be in ascending and descending order.

In the next practice you need to change the table Customers so that an overview is created of first the city, then the last name and then first name. The records must also be sorted in that order.

Figure 2.22: Table Customers sort on city, last name and first name.

Task 2.7 File: candy365.accdb

  1. Open the database.

  2. Open the table Customers. The table opens in the Datasheet View.

  3. Select the column City by clicking on the column header and drag the column to the left so this will be the first column in the table.

  4. Same way, move the columns LastName and FirstName to respectively the 2e and 3e position in the table.

  5. Click in column City on the arrow at the right side in the column header and select from the shortcut menuSort A to Z

Access reorganizes the records in alphabetical order by city and shows a small upward pointing arrow () at the right side of the column header to indicate the sort order.

  1. Cancel the sorting through tab Home > Remove Sort (group Sort & Filter)

  2. To sort on multiple fields, select the columns City, LastName, and FirstName.

  3. Choose tab Home > Ascending (group Sort & Filter)

Access reorganizes the records in ascending alphabetical order, first by city, then by the last name and then by the first name. At the right side of each of the headers of these three columns, you can see the small upward pointing arrow, see Figure 2.22.

  1. Close the table Customers and choose No on the question to save the changes.

2.11 Filtering

Filtering is a useful way to see only the data that you want to be displayed. You can use filters to display specific records in a form, report, query, or table. For example, you can easily create a filter to show all of the pralines with chocolate type Milk and filling type Marzipan. There are several ways to filter a table. A few methods will be discussed in the following practices.

2.11.1 Simple filter

Information need: Show all pralines with the chocolate type White.

Task 2.8 File: candy365.accdb

  1. Open the database.

  2. Open table Pralines. The table Pralines opens in Datasheet View.

  3. Click in the column ChocolateType on a value White.

  4. Choose tab Home > button Selection (group Sort & Filter) > Equals “White”.

Access shows now the records (4 records ) whose chocolate type is white. You can see that a filtering has been applied to the table:

  • To the right of the column heading ChocolateType there is now a small symbol of a filter ()
  • On the status bar you see

2.11.2 Modifying filter

Information need: Show all pralines with the chocolate type Milk.

For this, the previous filter will be changed.

Task 2.9 File: candy365.accdb

  1. Click on the filter symbol at the right of the column head ChocolateType.
Figure 2.23: Dialog box filter.
  1. Select in the dialog box Milk and deselect White. Click OK. There are now 18 records shown, all with chocolate type Milk.

The showed options in Figure 2.23 depend on the field type. With a text field, there is a submenu Text Filters and with a numeric field, there is a submenu Number Filters.

2.11.3 Filter on 2 criteria

Information need: Show all pralines with the chocolate type Milk and filling type Marzipan.

A second selection criterion is added for this.

Task 2.10 File: candy365.accdb

  1. Click in column FillingType on a value Marzipan.

  2. Choose tab Home > Button Selection (group Sort & Filter) > Equals “Marzipan”.

    Three records are displayed with chocolate type Milk and with filling type Marzipan.

  3. Undo filtering through tab Home > button (group Sort & Filter).

Filtering is removed and all records are displayed again.

Another way to remove the filter is by clicking on the button Filtered in the status bar. The text on the button will turn into Unfiltered. Clicking again applies the last used filter.

2.11.4 Number filter

Information need; Show all pralines with costs from $0.25 to $0.35.

Task 2.11 File: candy365.accdb

  1. Click the arrow tip at the right in column head PralineCosts, choose Number Filters > Between….
Figure 2.24: Dialog box Between Numbers.

  1. Enter 0.25 at Smallest, 0.35 at Largest, then click OK. There are now 22 records shown with bonbon costs from 0.25 to 0.35.

  2. Close the table Pralines and choose No on the question to save the changes.

2.12 Print

For printing, Access uses the printers that are available in Windows.

Depending on the view in an Access object, the result can be printed. For example, you can print datasheet views at tables and queries, report views at reports, and form views at forms.

There is no option for printing the design view, but Access has a tool named Database Documenter with which you can print the design characteristics of database objects.

The print options are available through File > Print. You then have the following options:

Figure 2.25: Print options in Access.

With the choice Print Preview, you can customize the print, such as page size, portrait or landscape, margins, ….

2.13 Exercises

Exercise 2.1 Selection Pralines (expl001)

Create a filter to select Pralines with chocolate type Milk or Dark whose price is at most $0.30.

With numeric fields, you have several options for number ranges:

Number filter

The result is 18 records.

Exercise 2.2 Selection Customers in city (expl002)

Make a selection of the customers who live in Enschede, Hengelo, and Almelo

The result is 12 records.

Exercise 2.3 Selection Customers on zip code (expl003)

Make a selection of customers whose zip code begins with 20 and is living in Amsterdam.

ZipCode is a text field, so you can use text filters:

Text filter

The result is 3 records.