| 1. |
Please explain the basics of the query grid?
|
| 2. |
How can I sort my queries in order of last name then first name?
|
| 3. |
How do I limit my query data set by date?
|
| 4. |
How do I convert mixed case names and addresses to upper case?
|
| 5. |
How do I query a memo field?
|
| 6. |
Can I update 100s of records in a query?
|
|
| 1. |
Please explain the basics of the query grid? |
| |
Please review diagram 1 for the following:
- Field row: This row displays the table fields to be queried.
- Table row: This row displays the table from which you will select a field.
- Sort row: This row allows the user to sort a corresponding field as ascending or descending.
- Show row: This row allows the user to designate whether a corresponding field will appear in the final query dataset.
- Criteria row: This row allows the user to limit a final query data set by setting criteria. Examples are seen in diagram 1 of date criteria in under the LastEmail field. Other types of criteria can include:
- Limiting text fields by specific values. For instance - limiting the last name field by "Meyer"
- Limiting True/False fields by either "True" or "False"
- Limiting Numeric fields - example: Between 1 and 100
|
| |
Return to Top
|
| 2. |
How can I sort my queries in order of last name then first name? |
| |
- Open your query in design view.
- Move the Last Name column by selecting the entire column and dragging it to the left.
- Move your cursor to the top of the column you want to move. Find the downward arrow, click and select the column. Then click the gray bar at the top and drag to the far left.
- Do the same with the First Name column. Drop it just to the right of your Last Name column.
- Queries always sort in priority from left to right. This is why both the Last Name and First Name columns need to be moved to the far left of the query grid.
- In the query grid, each column has several rows. The first three rows are titled; "Field"; "Table"; "Sort".
- We will be using the "Sort" row.
- Click in the "Sort" row of the Last Name column. A small box with an arrow will appear to the right.
- Click on this box, a list will drop down.
- Choose "ascending" from the list.
- Follow the same sorting procedure with your First Name column.
- Now run your query. Your record set will now sort by Last Name, then by First Name.
|
| |
Return to Top
|
| 3. |
How do I limit my query data set by date? |
| |
There are multiple ways to limit a query data set by date. Enter all SQL statements in the CRITERIA row of your query grid. Following are some examples of SQL criteria syntax.
- To Query Between two date values: Between #01/01/2007# and #01/31/2007#
- To Query One Date: #01/01/2007#
- To Query Today's Date: Date()
- To Query Year-to-Date: Between #01/01/2007# and Date()
- To Query Every Date in the Data Base LESS than a specific Date: < #01/01/2007#
- To Query Every Date in the Date Base GREATER than a specific Date: >#01/01/2007#
|
| |
Return to Top
|
| 4. |
How do I convert mixed case names and addresses to upper case? |
| |
Changing mixed case text to upper case requires using the UCase function. Below is an example of using the UCase Function:
-
The query grid below takes three fields and converts them from mixed case to upper case. Fields converted are:
-
OrganizationName
-
Address1
-
Address2
-
To convert the field name is encased in square brackets and this string is enclosed within the UCase function itself. For example: UCase([OrganizationName]) .
-
Also, notice that each field converted is preceded by a label. For example: Organization: is the label for the UCase([Organization]) string. If these labels are not assigned to the converted field then Access will assign its own label upon running the query. The label Access assigns is usually a variation of Exp (for expression). In order for your query to show fields in a comprehensible way it is important to label all converted fields yourself.
|
| |
Return to Top
|
| 5. |
How do I query a memo field? |
| |
Memo fields (and any other text field) may be queried by using a wild card character. If you are looking for a particular word or phrase within a text field, the wild card character should be used. In Access the wild card character is an *. Always insert your wild card criteria statements in the criteria row of your query grid. Examples follow:
- To search a text field for values beginning with: "My text string*"
- To search a text field for values ending with: "*my text string."
- To search a text field for values surrounding: "*my text string*"
|
| |
Return to Top
|
| 6. |
Can I update 100s of records in a query? |
| |
Yes - it is possible to use queries to update the same field across as many records as you'd like. In order to do this you will need to use the following steps. See Diagram 2 as an example.
- In your query grid bring in the the field you want to update. For an example we will use a field called IDStatus. We will change the field value to "Inactive".
- Once you've added your field to the query grid then select the Query menu.
- Within in the Query menu choose Update Query.
- After selecting Update Query from the Query menu you will notice a new row in your query grid called, Update To. See Diagram 2 for an example.
- In the Update To row of the field you want to update (in this example we'll be using the Update To row of the IDStatus field) enter the value you want to entered into your field. In this example the value is "Inactive".
- Please notice that "Inactive" is between quotations marks.
- Once you have typed the new value in the update row of your query grid, go to the Query menu and choose Run Query.
- The query will now run. During this process you will be asked to confirm the updating of your records. Once you've given consent the query will update all selected records to the new value you entered in the Update To row.
- Note: While updating text fields make sure your values are always within quotation marks. While updating numerical fields it is not necessary to surround a value with quotation marks.
|
| |
Return to Top
|
