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.