Creating A Table By Using Application Parts Access 2013

Application parts are predefined templates for creating popular layouts by using different Access objects. Depending on the part you select, you could have a series of steps that further integrate the chosen part with your application. These parts can enable you to add a great new feature to your database with minimum effort, although the choices for tables here are quite limited. 

Choosing an application part can add several objects into your database, including supporting forms and reports. 

Add an application part:

1. Click the Create tab. 
2. Click Application Parts. 
3. Select an application part. 
4. In the Create Relationship popup window, select the table to relate this part to, if one is available, and click Next. 
   
5. Add an application part 
6. Choose the field to look up. 
7. Type a name for the lookup column. 
8. Click Create. 
9. Three new design objects have been added to the database. 
10. The new lookup field now displays data
    
    from our existing Contacts table.

CAUTION: If you decide to use a primary key, such as an ID, rather than, for example, the contact name, you will need to later use the table design view to adjust your lookup field width because the drop-down lookup box has 0 width when it is a numeric key field. 

TIP: Access supports lookups that can store multiple values. This feature saves you from creating additional tables, which would otherwise be required to hold the multiple selections. In the example, we could have made this selection store multiple contacts against a single task. 

TIP: It is also possible to create your own application part to allow you to quickly add a set of objects to your database. The following article from the Access Team provides details on how to do this: blogs.office.com/b/microsoft-access/archive/2010/03/31/access- 2010-application-parts-and-quick-start-fields.aspx.

Create A New Data Part Access 2013

Create A New Data Part:

1. With the table in layout view, click the titles of one or more fields. (If necessary, you can use the Shift key to select multiple fields that are next to each other.) 
2. Click the Fields tab. 
3. Click More Fields. 
   
4. Scroll to the bottom of the list, and select Save Selection As New Data Type. 
5. Type a name for the data type. 
6. Type a description for the data type. 
7. Select a category in which to display the data type. 
   
8. Click OK. 
9. In the message that appears, noting where your new data type has been
   
   saved in the file system, click OK. 
10. If you click More Fields again, you can see that your new data type has been added to the list of available data types. You can now add this new data type to a different table by following the steps described in the preceding exercise, “Add a data part,” on page 50.

Working With Data Parts In Layout View Access 2013

Data parts, which are shown in layout view in the More Fields drop-down list, allow you to quickly add one or more fields to a table from a list of prepared fields. For example, adding an Address will add the fields Address, City, StateProvince, ZipPost, and CountryRegion to your table.

You can also select any number of existing fields that you have added to a table, and add them to this list of data parts so that you can easily add those fields to another table.

Add a data part:

1. With a table open in layout view (for the Customers table in our example), click the Click To Add box at the right of the datasheet. The cursor will then be positioned to add the new fields at the end of the datasheet. 
2. Click the Fields tab. 
3. Click More Fields. 
   
4. Scroll down the list of available fields, and click the Address field. 
5. The new fields are automatically added to the design of the table.

Change Field Captions And Descriptions Access 2013

Change Field Captions And Descriptions:

1. Click the Fields tab. 
   
2. Click the ContactName field. 
3. Click Name & Caption. 
4. In the Enter Field Properties popup window, change the Caption field to Contact Name, and for the Description type Name of company contact. 
5. Click OK. The title for the column is now displayed with a space via the Caption property, and the description will be displayed on the lower left on the status bar. 

TIP: One of the most common field data types to add is Short Text. The default field size is 255 characters (shown on the ribbon below Default Value). If you need only 20 characters, reducing the field size can help prevent you from accidentally storing more information than can be displayed—for example, when you have positioned a field on a form or report to be a specific size. Otherwise, do not worry about making it smaller just to save space.

Creating A Table In Layout View Access 2013

The layout view in Access can be used for tables, forms, and reports, and it enables you to make design changes without using the more complex design view. You can make changes and, at the same time, see how the design will finally look. In layout view, you have less control over detailed choices, but you have a faster and more intuitive interface through which to interact with the table.

In layout view, Access automatically adds a unique AutoNumber primary key, called ID, to your blank table design. After working in the layout view, you can always switch to the design view to refine your choices. 

Create a table:

1. Click the Create tab. 
2. Click Table. 
3. Click the Click To Add drop-down list. 
4. Select a data type. 
   
5. Replace the default caption Field1 with a name for the field. 
6. Click Save. 
7. Provide a name for the table, and click OK. 
   

TIP: At this point, you will find it easier both to close the Field List pane on the right and to switch from the Table Tools tab to the Fields tab, which displays the full set of choices for setting properties on each field.

Creating A Table In Design View Access 2013

Creating tables in design view gives you the greatest control over how the fields and properties are set, but it also involves more work than other techniques. Each field in the table design has a set of properties that you can safely leave at the default values. You can change these properties as you gain a deeper understanding of the product’s features. 

A fantastic feature of Access is that the design view can always be used to modify an existing table structure, regardless of which technique you use to create the table. 

Create a table:

1. Click the Create tab. 
2. Click Table Design. 
3. Type a name in the Field Name column for each field you want—for example, CustomerName. 
4. In the Data Type drop-down list, select a
   
   data type for each field—for example, Short Text. (At the end of this exercise, a table describes the data types that are available.) 
   
5. Click Save. 
6. Provide a name for the table, and click OK. 
7. If you have not created a primary key, you will be prompted to add a key. Click Yes to add a primary key. 
8. After saving your table, the new ID primary key is added to the table. 
9. The table now appears in the navigation pane. 

TIP: Designers sometimes begin table names with a prefix, such as tbl—for example, tblCustomers—to distinguish tables from queries (which are prefixed with qry) when both appear in one list. Having a naming convention is a good idea when you’re creating complex databases. 

TIP: Because there are several different choices here for numbers, you might find it easiest to use a Long Integer for whole numbers and use a Double for numbers with decimal places. 

TIP: If you know which field(s) would make a good choice for a unique primary key, select the fields and click the primary key button. A primary key must be unique for each record in the table and cannot be null (blank). You can use more than one field to create a composite primary key, where each field doesn’t contain unique values, but the combination of fields will always be unique.

Creating A Blank Desktop Database Access 2013

After starting Access as described at the beginning of Section 2, “Creating a custom Web App,” you can use one of the available template databases, or you can create a blank database as described in this section. 

Access offers you the flexibility to use templates of commonly encountered database structures or to start with an empty database into which you can either import existing data or create a solution tailored to your specific needs. 

Create a blank desktop database:

1. Click Blank Desktop Database. 
2. Type a name for your database. 
3. Select a file location in which to save the database. 
   
4. Click Create. 
5. When the new blank database opens in layout view (described later in this section), you will see that Access has created a table called Table1. At this
   
   point, we can close this table without saving any changes to the new table. Click the Close button to close the table. 

TIP: The templates for a desktop application all begin with the word desktop, and you can scroll down the list of templates to see these. Other templates are for creating a Web App.

The following table lists the available data types when you are adding fields to a table in a desktop database.

Data type	Description
Short Text	Text up to 255 characters. In earlier versions of Access, this data type was called Text.
Long Text	Large amounts of text, up to 65,536 characters. In earlier versions of Access, this data type was called Memo.
Number	Field Size property provides for Byte, Integer, Long Integer (default), Single, Double, Replication ID, Decimal.
Date/Time	Date and Time field.
Currency	Currency field.
AutoNumber	Sequential Automatic Number, Long Integer (default).
Yes/No	True/False (default is False).
OLE Object	Can hold images or other documents.
Hyperlink	Hyperlink to the Internet and local documents.
Attachment	Allows multiple documents to be saved.
Calculated	Calculated field.
Lookup	Creates a foreign key lookup.

Working With Reports Access 2013

Although a Web App does not have a reporting feature that can be displayed in a browser window, you can create reports as described in Section 10, “Preparing data to print using reports,” in a desktop database that is linked to the data held in the Web App. 

The first step is to use the Info option on the File menu to create a desktop database that is connected to your data. 

Create a report database:

1. On the File menu, select the Info option and then click Create Reports. 
2. Choose a location, and provide a name for the reporting database. 
3. Click Save. 
   
4. The new reporting database opens, and on the left, you can see a list of the tables that are linked to your Web App. Double-click a table in the navigation pane to display the data.

Modifying The Table Selector Access 2013

In a Web App, you interact with the data by selecting a table from the Table Selector and then selecting an available view associated with the table from a list of available views, called the View Selector. 

Each table in the Table Selector can be modified to rename captions or hide a table caption without affecting the underlying table. This means that the Table Selector works like a main menu for the application, and the View Selector works like a sub-menu. 

Modify the Table Selector to hide or show a table:

1. Click the table in the Table Selector. 
2. Click the Settings/Actions charm. 
3. Select Hide. 
   
4. The table name caption in the Table Selector is now grayed out. 

CAUTION: If an item is deleted, the underlying table will also be deleted. 

TIP: After you hide a table, the table name caption will be grayed out and moved to the bottom of the Table Selector. The table name caption will be hidden when you are viewing results in a browser. 

Change a table icon:

1. Click the formatting charm. 
2. Choose an icon for the Table Selector item. 

TIP: When you change a table name caption, it does not change the name of the actual table but instead changes only the table name displayed on the Table Selector.

Creating A Query Access 2013

A query provides a powerful mechanism for filtering and sort­ing data. It can also be used to bring together data from several tables or other queries in a single presentation. 

Like a table, a query can be opened to display data in the design interface without showing the results in the browser. To display the results in the browser, you need to create a view that uses the query. 

In Section 7, “Modifying data using queries,” we describe how you can create action queries that modify data in a desktop database—for example, updating a set of rows. These action queries are not available in a Web App. 

Create a query:

1. Click Advanced on the Home tab of the ribbon. 
2. Select Query. 
3. From the Show Table Dialog box, select one or more queries or tables from the Show Table dialog box by double-clicking each table. 
   
4. Click Close when you have finished making selections. 
5. Add the fields from the source data into the query grid by double-clicking each field listed in the table. 
6. Click Save. 
   
7. Type a name for the query, and click OK to save it. 
8. Click View. 
9. Select Datasheet View to test the query. 

SEE ALSO: For a detailed explanation of how to construct select queries, see Section 6, “Selecting data using queries.”

Open, Rename, Duplicate, Or Delete A View Access 2013

When you display a view in Access, you can use the Settings/ Actions charm next to the view name to perform basic operations on the view. The options are Open In Browser, Edit, Rename, Duplicate, and Delete. 

When you choose Open In Browser, the view immediately displays in the browser window, saving you from having to locate you to change the name of the view, the Duplicate option makes a copy of a view, and the Delete option removes the view. 

This task will demonstrate how, when editing a view, you can switch to the table design and modify the structure of the table. 

Change a table design when editing a view:

1. Click the Settings/Actions charm. 
2. Select Edit. 
3. If you are editing a view and displaying the Field List, you will see a link to edit the design of the table. Click the Edit Table option to change the design of the table. 
   
4. Add a new field name. 
5. Click Save. (You will not be able to save changes until you close any open views that refer to this table.) 
6. Click the tab with the open view. 
   
7. Close the view. If you now repeat step 5, you can save the changes to the design of the table.

TIP: If you switch to design view on the table, you will find that you need to close any open views before you can save any changes that you make to the table. 

Designing With A Blank View Access 2013

A blank view will create a presentation that does not display any fields from the table. This can give you a greater amount of control over what data is displayed, although it might involve more work in creating the view. 

Normally, a view displays information from the table associated with the selected table and related tables, but you can add a view that displays information from an unrelated table. In this example, we will add to the Products table View Selector a view that allows you to browse through the Category records but not display any associated record from the Products table. 

Create a blank view:

1. Locate the object in the Table Selector. 
2. Click the plus sign. 
3. Type a name for the view, select Blank as the view type, and then select the record source. 
4. Click Add New View. 
   
5. When the new view is displayed, click Edit. 
6. Click the Data icon. 
7. Change the record source. 
8. Close the Data popup window. 
9. In the Field List, select a field. 
   
10. Drag the field onto the blank view. 
11. After you have saved the changes, the View Selector for the table has a view that displays information from another table without involving the table associated with the selected table name caption in the View Selector.

Designing With A Summary View Access 2013

A summary view allows you to summarize data by calculating a sum or average. You need to choose a table or query with numerical fields before you can work with many of the best features in this type of view. 

The Record Source field (where the view gets the data) allows you to choose from the table or any queries that include the table. 

If you have been creating tables by using templates, you might already have seen examples of a summary view, which would appear as a third available view on some of the tables. When you create your own tables, a Web App will automatically create a List view and a Datasheet view. 

Create a summary view:

1. Locate the object in the Table Selector. 
2. Click the plus sign. 
3. Type a name for the view, select Summary as the view type, and select the record source. 
4. Click Add New View. 
5. When the new view is displayed, click Edit. 
   
6. Click the control list shown on the left. 
7. Click the Data charm next to the list control. 
8. In the Data popup window, choose the field to group by, type a header for the calculation, and choose the field and calculation type. 
   
9. Close the Data popup window. 
10. Click the details area on the right. 
11. Click the Data charm next to the details area. 
12. Define the fields to summarize. 
13. When displaying a summary view in a browser, click each record in the control list on the left to see the summary details on the right change to match the selected record. 

CAUTION: After you have closed any Data popup windows, you will see the fields and summary options displayed on the control list, but in the detail section on the right, you will not see any information displayed. You might be unsure about whether you have defined any choices in this detail section, so to check this, you need to re-display the Data popup window.

Designing With A Datasheet View Access 2013

A Datasheet view is the simplest view to create. This view tabu­lates the data and displays your fields as columns. Although it is extremely simple, when combined with the abilities to filter data, sort data, and reorder the columns, it is a very powerful method to quickly present a tabulation of your data. 

In a List Details view, before you can edit data, you need to use the action bar to put the record into edit mode. However, in the Datasheet view, you can edit data without doing this and move to the next record to continue editing another record, which automatically saves the changes from the previously edited row. This is a unique and very powerful feature of a Web App datasheet. 

The Record Source property (where the view gets the data) allows you to choose from the table or any queries that include the table. 

Create a Datasheet view:

1. Locate the table in the Table Selector. 

2. Click the plus sign. 

3. Type a name for the view, select Datasheet as the view type, and select the record source. 

4. Click Add New View. 

5. When the new view is displayed, click Edit. 
Format a field:

1. Click a field to format.

2. Click the formatting charm.

3. Change the formatting.

TIP: You can delete fields by pressing the Delete key. You can select multiple fields and move them as a group by using the Ctrl key, and add new fields by using the Field List.

Designing With A List Details View Access 2013

A view in a Web App is similar to a form in a desktop database. There are four types of views; List Details, Datasheet, Summary, and Blank. The List Details view is a powerful feature. In its simplest form, it has a vertical tabulation of the available records on the left side of the view, and when a record is selected, the record details are displayed on the right side of the view. 

The layout capabilities of this view mean that as you delete, add, or move fields, the layout automatically moves other fields to accommodate the position of the new fields, simplifying the process of altering the view. The Record Source field (where the view gets the data) allows you to choose from the table or any queries that include the table. 

Create a List Details view:

1. Locate the object in the Table Selector. 

2. Click the plus sign. 

3. Type a name for the view, select List Details as the view type, and select the record source. 

4. Click Add New View. 

5. When the new view is displayed, click Edit. 

TIP: In the lower part of a view, you may have a related item control (Orders in our example). To resize the control, click to select it (it will change color when selected); you can then grab the top edge of the control (the mouse shape will change when hovering over the edge) and resize the control. 

Remove fields:

1. Click a field. 

2. Hold down the Ctrl key to select multiple fields and labels. Press the Delete key to remove the fields. 

3. Select any other fields to be repositioned, and drag the fields to close up white space. 

TIP: By using the Ctrl key, you can select multiple controls and move them around the layout. After selecting the controls, move the mouse over the area until a crossing arrows symbol is displayed, and then click the left mouse button and drag the controls to a new position. As you move the controls around, other controls will change position, opening up a gap into which you can position the new controls.

Add fields:

Select a field from the Field List on the right side. 

Drag the field to a new position on the display area. 

As you move over other fields, they will make space to allow the new control to be positioned. 

CAUTION: Although you can select multiple fields in the Field List, when you use the dragging action to add the fields, only the first field will be added.

Creating A Lookup/Relationship Access 2013

A Web App allows you to create a lookup, which ties or relates data between tables. (The relationship concept is described in Section 4, “Creating a desktop database,” for a desktop database.) The Web App does not have a separate area for viewing relationships. You can view them for individual tables by selecting Modify Lookups in the Table Tools group on the Design tab. 

A Web App simplifies the design process by keeping you focused on creating lookups between tables. The terms relationship and lookup mean the same thing in a Web App, and in the popup window used to create the lookup, you will see options similar to those available for creating relationships in a desktop database.

Create A Lookup:

1. On a blank line in the Field Name column, type a name for the lookup. 

2. In the Data Type column, select the Lookup data type. 

3. Select the option to look up data from a table or query. 

4. Select the table from which you want to get a value. 

5. Select the field to display. 

6. Choose how the related records are managed. 

7. Click OK. 

TIP: The lower part of the Lookup Wizard screen displays the optional rules for defining a relationship between the tables. 

Create a lookup 

8. Click Save. 

9. Click View. 

10. When viewing the data, you can see the Autocomplete control displaying the lookup data. 

11. In the browser, you can type data into the new field that was automatically added to the List Details view. 

TIP: If you have not changed the design of any views associated with your table, the added field automatically displays in the updated List Details view and Datasheet view for the table.

Changing The Design Of A Table Access 2013

In Section 2, “Creating a custom Web App,” we demonstrate how the desktop interface can allow you to display content both by using the Table Selector and by showing the navigation pane. You can use either of these interfaces to enter design view and change the design of your table. Here we show how to enter design view from the table in the Table Selector. (If you prefer the navigation pane, right-click an object to see similar options.) 

In Access 2013, the available data types for fields have changed, and the most obvious change is that a Text field is now called a Short Text field and a Memo field is called a Long Text field. However, there are some other differences between the avail­able data types in a Web App compared to a desktop database.

Change the design of a table:

1. Right-click a table, and select Edit Table. 

2. Click the Data Type drop-down arrow to see the available choices of data types. 

Change the design of a table:

The following table describes the data types that are available. For each data type, we show in parentheses the SQL Server data type that is used to store the data. 

Data type	Description (SQL Server data type)
Short Text	Unicode Text field allowing a maximum of 4,000 characters (nvarchar(4000)). The default is 220 characters.
Long Text	Large amounts of text (corresponds to a nvarchar(max), 2^30–1 bytes in SQL Server 2012).
Number	Whole number with no decimal places (int), Floating-point number (float), and Fixed-point number (decimal(28,6), six decimal places).
Date/Time	Date (date), Time (time(3)), and Date with Time (datetime2(3)).
Currency	Money (decimal(28,6)).
Yes/No	(bit) True, False (default is False).
Hyperlink	Large amount of text (nvarchar(max)).
Image	Binary Image data (varbinary(max) 2^31-1 bytes in SQL Server 2012).
Calculated	Calculated field using SQL Server functions. Storage depends on expression.
Lookup	Creates a foreign key lookup. (Lookup foreign key stored as integer (int)).

TIP: Behind the scenes in Office 365, your data is stored in Microsoft SQL Server for a corporate solution or using Micro­soft SQL Azure in conjunction with Office 365. SQL Server is similar to Access in being a relational database but it is more scalable (so it can handle more data that the normal Access Database), and SQL Azure is a version of SQL Server that it is the platform that Microsoft use to deliver data on the web. We only explain the underlying SQL Server data types here for reference; you don’t need to learn anything new to use this.

Importing From An Access Desktop Database Access 2013

If you have an existing Access Desktop database, you can import the tables and, optionally, the data from that database into a new Web App. The data can be imported from a variety of sources, including an Access desktop database. 

Where possible, the process of importing data will also import relationships between your tables and convert these to look­ups. If you don’t have an appropriate database available to investigate the techniques described in this section, you can refer to Section 1, “About this book,” for information about how to download the sample data that we have used in preparing this section of the book. 

We start with a new custom Web App into which we will import existing data. 

Import an Access database:

1. Click Table on the Home tab of the ribbon. 
2. On the Add Tables screen, click Access. 
3. Click Browse. 
4. Locate the Access desktop database file. (If you do not already have an accessible database file, you will not be able to complete any further steps until you have installed or created one.) 
5. Click Open. 
6. Click OK. 

Import an Access database:

• 7 Select the tables to import, or click Select All. 
• 8 Click Options to display further options when importing data. 
• 9 Choose to import or exclude the relationships. 
• 10 Choose to import either definitions only or definitions and data. 
• 11 Click OK. During the import process, your existing relationships will be converted into Lookup data types. 
• 12 Right-click the table that has relationships to other tables, and select Edit Table. The relationship is now shown as a Lookup data type. 
• 13 Close the table. 

CAUTION: Data types such as Attachments or OLE Objects are not supported in a Web App and will be excluded from the import process.