Lab 9 Queries - Design And Exploration

1 - Introduction:

A query can benefit from your creativity. Just like a table and/or a form, a query is a matter of planning and good design.

2 - Creating A Query In Design View:

To create a query, the first decision you make concerns the source of data.

1. Start Microsoft Access and open the Videos4 database.
2. In the Database Window, click the Queries button, then double-click Create Query In Design View.
3. You should receive the Show Table property sheet that allows you to specify where data for the query will come from.
   
   
   
   In the Show Table property sheet, click tblVideos, click the Add button and close the Show Table property sheet.
4. Once you have the originating table or query as a list, you can choose which fields are necessary for the query. If you want to use all the fields, double-click on the list's title bar, that will select all fields. Then drag the whole group in the lower part of the query. To select different fields from the list you can click each one and drag it to the query. To get a group of different fields, click VideoTitle, hold the Ctrl key down, then click Director and  click Rating. Once you have that list, drag one of the selected fields, for example Rating, to the left column of the query.
5. Save the query as qryVideos.
6. Run the query. Then switch back to Design View.
7. To expand your query, you can add fields as you like, just drag the needed field from the list and drop it on top of the field that will succeed to it. For example, drag the YearReleased and drop it on top of Director. Then run the query. 
8. When you have finished viewing the query, switch back to Design View.
9. You can move fields in Design View the same way you do for a table. For example, click the small bar on top of YearReleased and release the mouse, that will select the whole column. Then click again on the column header and drag it between Director and Rating.
   
   
     
10. Then run the query. When you have finished viewing the query, switch back to Design View.
11. You can build a query using data from different tables or queries as long as these objects are related. You have to select fields from the table or query that you add using the Show Table property sheet.
    Click the Show Table button on the toolbar. From the Show Table property sheet, double-click tblVideoCategories then close the Show Table property sheet.
12. To change the starting field in a query, drag the appropriate field to the first field in the query. For example, drag VideoCategory from tblVideoCategories and drop it on top of VideoTitle.
13. Then run the query. When you have finished viewing the query, switch back to Design View.
14. Once you have selected certain tables or queries to participate in your query, their fields are listed on different parts of the query. This allows you to choose easily which ones to add to your query. For example, in the empty field next to Rating (on its right), click in the Field box. A combo box appears. Click on its arrow to drop it down.
    
    
        
15. From the list, select tblVideos.Length.
16. Then run the query. When you have finished viewing the query, switch back to Design View.
17. Now you can run different instances of the query using the techniques we have learned to sort and filter tables and queries.
18. Save and close the query. Then close the database.

3 - Hiding Fields From The Query:

Instead of showing or not showing some fields on a query, you can decide to hide a field (or some fields) for some particular purpose.

1. Open the Music Collection4 database.
2. From the Database Window, click the Queries button, then double-click Create Query In Design View.
3. From the Show Table dialog, double-click tblTracks, and click the Close button.
4. From the tblAlbums list, double-click the * button, that will select/include all fields.
5. Switch to Datasheet View. On the main menu, click Format -> Unhide Column...
6. In the Unhide Columns dialog, uncheck ID, Album and Notes.
   
   
     
7. Click Close and view the query.
8. Save the query as qryTracks before closing it.

2 -Text Combinations In Queries:

When combining calculated fields, you are allowed to name the new field; otherwise, Microsoft Access will give a default name.

1. Open the Employees2 database.
2. On the Database Window, click the Queries button. Then double-click Create Query In Design View.
3. From the Show Table dialog, double-click tblEmployees and click Close.
4. From the tblEmployees list, double-click EmployeeID.
5. Press Tab to move to the next field box on the right. Press Shift + F2.
6. Type [FirstName] & " " & [LastName] and click OK.
7. Run the query. Since you didn't give a name to the new field, Microsoft Access called it Exp1. Switch back to Design View.
8. Right-click in Exp1 and choose Zoom... Replace Exp1 with FullName and click OK.
9. Run the query again. Now you have a specified column name. The only thing left to change is the caption a user sees on the column header. Switch back to Design View.
10. Right-click somewhere in the FullName column and choose Properties. In the General tab of the Field Properties dialog, in the Description field, type This is the employee's full name. This description will be seen on the Status Bar when a user clicks a name. In the Caption field, type Employee's Name. Then close the Field Properties dialog.
11. Run the query.
12. Save the query as qryEmployeesAddresses.
13. Close the Employees2 database.
14. Open the Georgetown Cleaning Services3 database.
15. Open the tblCleaningOrders. Drop the Clerk combo box down to see the names of the employees. Switch the table to Design View.
16. In Design View, click the EmployeeID field. In the lower part of the view, click the Lookup tab. Right-click the Row Source field and choose Build... Delete or Replace LastName in the LastName field with Clerk: [LastName] & ", " & [FirstName]. Sort it Ascending. Close the query and accept to save it.
17. Switch the table to Datasheet View and drop the Clerk combo box. It now displays the last name followed by the first name.

3 - Subform From A Query: 

The primary purpose of a subform is to display children data of a parent form. It allows you to show data related to the main source. We have already created subform in the past. This time, we would like the form a little friendlier and better looking. To get that, we will create a subform from a query that is itself created from a table.

1.      Open the Music Collection5 database.

2.      From the main menu, click Insert -> Query.

3.      From the New Query dialog, double-click Design View.

4.      From the Show Table dialog, double-click tblTracks and click Close.

5.      Click AlbumID, hold the Shift key, and click TrackLength. That will select four fields.

6.      Drag the selected group to the first column of the lower portion of the design grid.

7.      Save the query as qryTracks. Then close it.

8.      On the Standard toolbar, click the New Object arrow and choose Form.

9.      In the New Form dialog, choose Form Wizard and choose qryTracks in the lower combo box. Then click OK.

10.  Make sure the qryTracks is selected as the source of data. Select all fields and click Next. Choose the Tabular layout, the Standard style and click Next. Name the form sbfTracks, click the Modify the Form's Design radio button, and click Finish.

11.  Right-click the Album Title's label and choose Properties. Click the All tab and set the Visible field to No.

12.  Click the AlbumID text box and set its Visible field to No.

13.  Decrease the widths of both the Album label and the AlbumID text box to 1/16 (or less, they are not going to be seen).

14.  Click the button at the intersection of both rulers to access the form's properties. In the All tab, set the Navigation Buttons field to No.

15.  Save the subform.

16.  Open the frmAlbums form in Design View.

17.  On the Toolbox, make sure that the Control Wizard is pressed. Then click the Subform/Subreport button. Click somewhere in the lower portion of the form.

18.  On the Subform Wizard page, choose sbfTracks1 and click Next. Click Next again, and click Finish.

19.  Resize the subform to fit proportionately in the form.

20.  Save and close the frmAlbums form.

Getting Help In Microsoft Access:

1. Click the Office Assistant and type Query.
2. Click Queries: What they are and how they work.
3. Click the graphics link.