Return to the Query Design view, and this time lets consider the situation where you want a list of customers with credit limits between $2,000 and $5,000. Remove any existing criteria and add the CreditLimit field to the table. In the Criteria area for the CreditLimit field, type:
>=2000 And <=5000
This query creates and displays a field dynamically by performing a calculation using data from two other fields.
(Click for larger image).
In this case where the two criteria: >=2000 and <=5000 both relate to the same field, you write the query by placing both criteria in the same cell with the word AND appearing between them.
Another example of this is a query that displays all customers who became customers in the period between January 1, 2000 and December 31, 2005. In this case, add the CustomerSince field to the query, remove any other criteria, and then type the following into the Criteria cell for the CustomerSince field:
When you use dates, surround the date text with hash symbols so Access understands this data represents a date.
Sort Query Data
You can sort the query results by selecting the sort order from the Sort dropdown list below a field name in the query grid. For example, to sort the previous CustomerSince query by the date in the CustomerSince field, set the sort cell under CustomerSince to read Ascending and test the result by running the query again.
This query is very flexible as it prompts you for the query data at run time.
(Click for larger image).
There may be times when you'll need data that doesn't actually exist in the data table field, but by making calculations in your queries, you can mine that data. For example, to determine the difference between a customers CreditLimit and their OutstandingBalance, add a field to your query table by typing this into an empty Field cell:AvailableCredit: [CreditLimit]-[OutstandingBalance]
Run the query and notice that a new AvailableCredit field appears displaying the difference between a customer's credit limit and what they currently owe. This field isn't added to the table, and it only appears in the query. You don't need to have it in the table because you can create it dynamically whenever you need it.
Format Query Results
To format the results of a query, right-click the field name in the query grid and choose Properties > Format. Here, for example, you can configure a numeric field to display as currency with 0 decimal places. Run the query to see the results.
Want to See Even More
Software Application Tips?
Check out our SmallBusinessComputing series, How-To With Helen Bradley
Reuse a Query
You can save any of these queries and reuse them later on. However, you'll probably want to make them more useful by having them prompt you for the criteria when the query is run rather than hardwiring the criteria into the query.
For example, to create a query to extract customers who have credit limits between certain values, add the CreditLimit field to your query table and type the following into the Criteria cell:Between [Lower CreditLimit Value] And [Upper CreditLimit Value]
Run the query, and you'll be prompted to enter the credit limit values marking the lower and upper range of credit limits you want to view.
As you can see there are plenty of options for extracting meaningful information from your database. Being familiar with the methods that you can use to extract data will let you make better use of the time and effort that you have put into entering the data in the first place.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com
|Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!|