Home

Advanced Queries
 

Parameterized Queries
 

Introduction

In the previous lessons, every time we created a query, we selected the columns we wanted because we knew what fields would be considered in the result. This also meant that we decided (imposed) what would be displayed to the user. In some cases, you may want to let the user specify one category (or more than one category) of values that would show in the result instead of displaying all of the items. Consider the following table:

 

To show the list of videos based on ratings, you could create a query and specify one of either the R, the PG, or the PG-13 values. For example, if you want to create a query that shows only R-rated videos, you could create it as follows:

This query would show only the videos that are rated R:

Instead of imposing the rating value to the user, you can create a query that would prompt the user for a rating and it would show only the videos that are based on the user's choice of the rating. This is the basis of a parameter query.

A parameter query is one that requests a value from the user and displays its result based on the user's choice. As its name implies, this query expects a parameter, like the arguments we reviewed for procedures. This means that, when creating such a query, you must prepare to display a request to the user.

Practical LearningPractical Learning: Introducing Parameterized Queries
  1. Start Microsoft Access and create a Bland database named I Rent Stuff
  2. To create a new table, on the main menu, click Insert -> Table and, in the New Table dialog box, double-click Design View
  3. Create the columns as follows:
     
    Field Name Data Type Caption Field Size Other Properties
    EmployeeID AutoNumber Employee ID   Primary Key
    EmployeeNumber   Employee # 10  
    FirstName   First Name 20  
    LastName   Last Name 20 Required: Yes
    FullName   Full Name    
    Title   Title    
    HourlySalary Number Hourly Salary Double Format: Fixed
    Notes Memo      
  4. Save the table as Employees and close it
  5. Use AutoForm to generate a form for the Employees table
  6. Save it as Employees and change its design as follows: 
     
    Bethesda Car Rental - Employee
  7. Double-click the FullName text box to access its Properties window and click the Event tab
  8. Double-click On Lost Focus and click its ellipsis button
  9. Implement it as follows:
      
    Private Sub LastName_LostFocus()
    [FullName] = IIf(IsNull([FirstName]), _
                     [LastName], _
                     [LastName] & ", " & [FirstName])
End Sub
  10. Save the form and switch it to Form View
  11. Create a few employees as follows:
     
    Empl # First Name Last Name Title Hourly Salary
      Patricia Katts Office Manager 24.55
      Henry Larson Sales Representative 12.75
      Gertrude Palau Sales Representative 10.85
      Helene Sandt Intern 8.95
      Melanie Karron Sales Representative 12.05
      Ernest Chisen Sales Manager 22.60
      Melissa Roberts Supervisor 18.25
     
  12. Close the form
  13. To create a new table, in the Tables section of the Database window, double-click Create Table In Design View
  14. Create the following columns:
     
    Field Name Data Type Caption Other Properties
    CustomerID AutoNumber Customer ID Primary Key
    DrvLicNbr   Driver's Lic. #  
    FullName   Full Name  
    Address      
    City      
    State      
    PostalCode   Postal/ZIP Code  
    Country      
    Notes Memo    
  15. Save the table as Customers and close it
  16. Use AutoForm to generate a form for the Customers table
  17. Save it as Customers and change its design as follows: 
     
  18. Save the form and switch it to Form View
  19. Create a few employees as follows:
     
    Driver's Lic. # Full Name Address City State Postal Code
    M-505-862-575 Lynda Melman 4277 Jamison Ave Silver Spring   20904
    379-82-7397 John Villard 108 Hacken Rd NE Washington DC 20012
    J-938-928-274 Chris Youno 8522 Aulage Street Rockville   20852
    K-497-220-614 Pamela Ulmreck 12075 Famina Rd Hyattsville MD 20707
    922-71-8395 Helene Kapsco 806 Hyena Drive Alexandria VA 22231
    C-374-830-422 Hermine Crasson 6255 Old Georgia Ave Silver Spring   20910
    836-55-2279 Alan Pastore 4228 16th Street NW Washington DC 20004
    B-397-597-487 Phillis Buster 724 Cranston Circle College Park   20747
    K-115-802-957 Elmus Krazucki 808 Rasters Ave Chevy Chase MD 20852
    294-90-7744 Helena Weniack 10448 Great Pollard Hwy Arlington VA 22232
  20. Close the form
  21. To create a new table, in the Tables section of the Database window, double-click Create Table In Design View
  22. Create the following columns:
     
    Field Name Data Type Caption Other Properties
    ProductID AutoNumber Product ID Primary Key
    ProductNumber   Product # Field Size: 10
    DateAcquired Date/Time DateAcquired Format: dd-mmm-yyyy
    Input Mask: 00\->L<LL\-0000;0;_
    PriceAcquired Number Price Acquired Field Size: Double
    Format: Fixed
    ProductName   Name/Description  
    Make      
    Model      
    SerialNumber   Serial #  
    ProductCondition   Product Condition  
    RentalRate Number Rental Rate/Day Field Size: Double
    Format: Fixed
    ProeductPicture OLE Object Picture  
    Available Yes/No Is Available? Default Value: Yes
    Notes Memo    
  23. Save the table as Products
  24. Set the ItemCondition's Data Type to Lookup Wizard
  25. In the first page of the wizard, click the second radio button and click Next
  26. Click under Col1 and type Excellent
  27. Press the down arrow key and type Good
  28. Press the down arrow key and type Needs Repair
  29. Press the down arrow key and type Time to Retire
  30. Click Next and click Finish
  31. Close the table
  32. Use AutoForm to generate a form for the Products table
  33. Save it as Products and change its design as follows: 
     
  34. Save the form and switch it to Form View 
  35. Create a few records
     
  36. Close the form
  37. In the Database window, click Forms.
    Right-click Employees and click Design View
  38. Access its Properties window and click Event
  39. In the Event tab, double-click On Load and click its ellipsis button
  40. To create a new table, implement the event as follows:
      
    Private Sub Form_Load()
    DoCmd.RunSQL "CREATE TABLE RentalOrders(" & _
                 "ReceiptNumber COUNTER(1001, 1) NOT NULL PRIMARY KEY, " & _
                 "OrderPreparedBy TEXT(10), " & _
                 "OrderFinalizedBy TEXT(10));"
End Sub
  41. Return to Microsoft Access and switch the form to Form View
  42. Close the Employees form
  43. When asked whether you want to save the changes, click No
  44. In the Database window, click Tables
  45. In the Tables section of the Database window, right-click RentalOrders and click Design View
  46. Complete the table with the following columns:
     
    Field Name Data Type Caption Other Properties
    RentalOrderID   Rental Order ID  
    OrderPreparedBy   Prepared By  
    OrderFinalizedBy   Prepared By  
    ProductSelected   Product Selected  
    ProductName   Name/Description  
    Make      
    Model      
    SerialNumber   Serial #  
    ProductCondition   Product Condition  
    Customer   Customer (Drv Lic #)  
    CustName   Name  
    CustAddress   Address  
    CustCity      
    CustState      
    CustZIPCode      
    CustCountry      
    RentStartDate Date/Time Start Date Format: Long Date
    Input Mask: 00\->L<LL\-0000;0;_
    RentEndDate Date/Time End Date Format: Long Date
    Input Mask: 00\->L<LL\-0000;0;_
    TotalDays Number Total Days Field Size: Integer
    RentalRate Number Rental Rate/Day Field Size: Double
    Format: Fixed
    SubTotal Number Sub Total Field Size: Double
    Format: Fixed
    TaxRate Number Tax Rate Field Size: Double
    Format: Percent
    Default Value: 0.0775
    TaxAmount Number Tax Amount Field Size: Double
    Format: Fixed
    RentTotal Number Rent Total Field Size: Double
    Format: Fixed
    Notes Memo    
  47. Save the table
  48. Set the ProductCondition's Data Type to Lookup Wizard
  49. In the first page of the wizard, click the second radio button and click Next
  50. Click under Col1 and type Excellent 
  51. Press the down arrow key and type Good
  52. Click Next and click Finish
  53. Save and close the table
  54. In the Database window, click Forms and double-click Create Form in Design View
  55. Set its properties as follows:
    Caption: I Rent Stuff - Rental Order Preparation
    Navigation Buttons: No
    Min Max Buttons: Min Enabled
  56. Save the form as RentalOrderPreparation
  57. On the Toolbox, click the Combo Box and click the form
  58. In the first page of the wizard, accept the first radio button and click Next
  59. In the second page of the wizard, click Table: Employees and click Next
  60. In the third page, double-click FullName and Title
  61. Click Next four times and click Finish
  62. On the Toolbox, click the Combo Box and click the form
  63. In the first page of the wizard, click the second radio button and click Next
  64. Click under Col1 and type Excellent 
  65. Press the down arrow key and type Good
  66. Click Next and click Finish
  67. Design the form as follows:
     
    Control Caption Name Other Properties
    Text Box Prepared By txtOrderPreparedBy  
    Text Box   txtPreparedByName  
    Rectangle      
    Text Box Product Selected txtProductSelected  
    Text Box Make txtMake  
    Text Box Model txtModel  
    Text Box Serial Number txtSerialNumber  
    Combo Box Car Condition cboProductCondition  
    Rectangle      
    Text Box Customer (Drv Lic #) txtCustomer  
    Text Box Name txtCustName  
    Text Box Address txtCustAddress  
    Text Box   txtCustCity  
    Text Box   txtCustState  
    Text Box   txtCustZIPCode  
    Text Box   txtCustCountry  
    Option Group Order Evaluation    
    Text Box Rent Start Date txtRentDate Format: Long Date
    Input Mask: 00\->L<LL\-0000;0;_
    Text Box Rate Applied/Day txtRentalRate  
    Text Box Notes txtNotes  
  68. Save and preview it
     
  69. Save and close the form

Creating a Parameter Query

You start a parameter query like any other query, by selecting the necessary columns. In the Criteria box corresponding to the column on which the choice would be based, you can enter a phrase between an opening square bracket and a closing square bracket. Here is an example:

When the user executes the query, a message box would display the specify question. The user must enter a value in the dialog box:

After entering the value, the user can click OK. The Datasheet View would then display the records that abide by that rule:

In the same way, you can use any of the other operators we reviewed in the previous lesson, including BETWEEN, LIKE, NOT, or IN. For example, to let the user enter part of a name of a director, you could set the Criteria of the Director to: 

LIKE "*" & [A director name that includes] & "*"

When the query runs, if the user enters a name such as Phillip, the list would include the 6th and the 12th videos. Instead of requesting just one value as a parameter, you can request more than one. To do this, you can use the BETWEEN operator that requests an additional AND. For example, to ask the user to specify a range of years whose videos you want to see, you would set the Criteria of a CopyrightYear to

BETWEEN [Enter a starting year] AND [Enter an ending year]

Practical LearningPractical Learning: Creating a Parameterized Query
  1. To start a new form, in the Forms section of the Database window, double-click Create Form In Design View
  2. Access its Properties window.
    Click Control Source and click its ellipsis button
  3. In the Show Table dialog box, double-click RentalOrders and click Close
  4. In the RentalOrders list, double-click * and ReceiptNumber
  5. In the Criteria box of ReceiptNumber, type [Enter Receipt Number]
     
  6. Close the Query Builder
  7. When asked whether you want to save the change, click Yes
  8. Save the form as ReturningProduct and set its properties as follows:
    Caption: I Rent Stuff - Returning Product
    Navigation Buttons: No
    Auto Center: Yes
    Min Max Buttons: Min Enabled
  9. Design the form as follows:
     
  10. Save the form and close it

Data Views
 

Introduction

In the previous lesson, we saw that, after sorting or filtering records on a table, we could save the results of the table and the table would remember its previous state the next time it's opened. The most formal way to create and save sorting or filtering as an object is through a query. A query is saved as a regular object like a table or a form. The concept of queries is mostly used by Microsoft Access. Other database environments or libraries don't necessary use queries as objects. For example, ADO allows you to create a list that is either sorted, filtered or both and save it as a formal object. This is referred to as a view.

A view is a technique of creating a list of records based on data from an existing table, a query, or another view.

View Creation

Microsoft Access doesn't have its own means of creating a view. Instead, you can create it as part of an ADO database. To create a view, you can start with the following SQL syntax:

CREATE VIEW ViewName
AS
SELECT Statement

The creation of a view starts with the CREATE VIEW expression followed by a name. The name of a view follows the rules and suggestions we have applied to other objects so far. After the name of the view, use the AS keyword to indicate that you are ready to define the view.

Because a view is like a query, it can be defined using a SELECT statement, following the same rules we applied for data sorting or filtering. Here is an example that creates a view:

Private Sub cmdCreateRegistration_Click()
	Dim conDatabase As ADODB.Connection
	Dim SQL As String

    	Set conDatabase = Application.CurrentProject.Connection
    
    	SQL = "CREATE VIEW StudentsIdentification " & _
              "AS SELECT FirstName, LastName FROM Students"

	conDatabase.Execute SQL
    
	conDatabase.Close
    	Set conDatabase = Nothing
End Sub

After creating the view, it is internally available to all objects of your database like a query but, because Microsoft Access doesn't have a Views section, you cannot see the view in the Database window.

Using a View

After creating a view, you can use it as you would a query. For example, you can use it as the Record Source of a form. Here is an example:

Private Sub cmdApplyRegistration_Click()
    Me.RecordSource = "StudentsIdentification"
    Me.txtFirstName.ControlSource = "FirstName"
    Me.txtLastName.ControlSource = "LastName"
End Sub

You can also extract the values of a view using a SELECT statement. You can also display the value of a query in Datasheet View like a table or a query. To do this, call the OpenQuery() method of the DoCmd object.

 

A View With Arguments

A view is a list of values from a table, an existing query, or another view. A view is not a procedure but it can be used as if it were one. As imitating a procedure, a view can include a type of argument. The word argument here only means that some values can be passed to a view but these values can be specified only when creating the view. They are not real arguments.

When creating a view, you can create placeholders for columns and pass them in the parentheses of the view. This would be done as follows:

CREATE VIEW StudentsIdentification([First Name], [Family Name])
. . .

If you use this technique, the names passed in the parentheses of the view are the captions that would be displayed in place of the columns of the view. This technique allows you to specify the strings of your choice for the columns. If you want a column header to display the actual name of the column, write it the same. Otherwise, you can use any string you want for the column. If the name is in one word, you can just type it. If the name includes various words, include them between an opening square bracket "[" and a closing square bracket "]".

After listing the necessary strings as the captions of columns, in your SELECT statement of the view, you must use the exact same number of columns as the number of arguments of the view. In fact, each column of your SELECT statement should correspond to an argument of the same order.

Here is an example:

CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
AS
SELECT TagNumber, Make, Model, Available FROM Cars

Because, as we stated already, a view is not a procedure and the values passed to the view are not real arguments, when executing the view, don't specify the names of arguments. Simply create a SELECT statement and specify the name of the view as the source.

 

Deleting a View

As mentioned earlier, after creating a view, it becomes a named object like a table, a query or a form. As such, it can be removed from a database. To delete a view, use the DROP VIEW expression in the following syntax:

DROP VIEW ViewName

On the right side of the DROP VIEW expression, enter the name of the undesired view and execute the statement. Here is an example: 

Private Sub cmdAlterView_Click()
    Dim conDatabase As ADODB.Connection
    Dim SQL As String

    Set conDatabase = Application.CurrentProject.Connection
    
    SQL = "DROP VIEW StudentsIdentification"

    conDatabase.Execute SQL
    
    MsgBox "The RegistIdentification view has been deleted.", vbInformation
    
    conDatabase.Close
    Set conDatabase = Nothing
End Sub

You will not be warned before the interpreter deletes the view.

 

 
Previous Copyright © Yevol, 2007 Next