Extend Microsoft Access Applications to the Cloud: Creating Data Sources by Using Queries
In this chapter, we will look at how to create queries in a web app. These queries can then be used either with views to display information or with data macros to process information. If you are familiar with this subject, you will notice that in the contents there are no topics on creating queries to modify data. Queries that modify data are not supported in a web app; instead, you use data macros to perform equivalent operations.
In Chapter 5, “Displaying data in views,” I demonstrated creating a simple query for creating a jump list to summarize product data in our application. In this chapter, I provide a more detailed explanation of how queries are created and how they can then be used in your application. As you did in Chapter 5, you’ll use some simple user-interface macros to link your queries to views. In Chapter 7, “Programming a web app by using macros,” I will provide a more detailed discussion of macro design.
If you want to follow along with the design steps in this chapter, continue to use the NorthwindData web app used in Chapter 5.
Creating basic queries
Queries are created using the Query option on the Advanced menu, as shown in Figure 6-1.
FIGURE 6-1 Creating a query.
When the query designer opens, the Show Table popup is displayed. It has three tabs you can click on to display tables, queries, or both. If you double-click a table or query in the Show Table popup (or click the Add button when the table or query is highlighted), it will be added to the query, as shown in Figure 6-2. If you close the Show Table popup, you can return later to add other tables or queries by clicking the Show Table icon on the ribbon. If tables are related, lines will be added between the linking fields as the tables are added to the query.
FIGURE 6-2 Adding tables or other queries to a query.
After you select the tables and queries to be used in creating a new query, the next step is to select the fields to work with. Fields can be added using several different techniques:
- Double-click or drag and drop the * at the top of the list of fields to add all fields to the query grid. This would be shown, for example, as Orders.*
- Double-click or drag and drop a single field onto the query grid.
- Press the Shift key to select multiple fields, and then drag and drop the multiple selections onto the query grid.
- From the query grid, in a blank column, use the Field drop-down list to select a field.
Select all the fields from the Orders table by dragging and dropping the * at the top of the field list for the Orders table onto the query grid, as shown in Figure 6-3. Then select multiple fields—such as ProductID, UnitPrice, Quantity, and Discount—holding down the shift key to select the fields from the Order Details table, which are then dragged onto the query grid.
FIGURE 6-3 Adding fields to the query grid.
Fields on the query grid can be selected by clicking just above the field name, as shown in Figure 6-4. After a field is selected, the column will appear darkened to indicate it is selected. After the fields are selected, they can be re-ordered using drag and drop, or deleted by pressing the Delete key; the Shift key can be used to select multiple fields.
FIGURE 6-4 Hovering the cursor to select a field.
A field name can be changed by prefixing the field using an alternative name ending with a colon, as shown in Figure 6-5. I displayed the Zoom box by pressing Shift+F2 and adjusted the display font to display the expression.
FIGURE 6-5 Changing the name of a field.
You can display the results of your query by clicking on the View icon, which is located at the top left on the Design ribbon. You will be prompted to save the query before viewing the results. Save the query with the name qryOrdersWithCriteria.