How many fields can I sort by in a query?
You can sort by up to ten fields in a query by using the Sort row. Access sorts form the leftmost field first and works it's way to the right.

What if I do not want the Sort by field at the left of the Query Dynaset?
It is possible to use a field to sort by and then add the field again in a different column of the query's dynaset. Use the Show check box to hide the sort by column and show the field elsewhere.

Use Parameter Queries to get more life out of a query
Often you may want to run a query using specific criteria to return specific records. You can type the criteria into the design window, but if you want to change the criteria every time you run the query then going into the design window and updating the criteria would get very time consuming. So you can use a parameter.

If you wanted to return sales transactions that concerned only a certain type of product, then in the query design window you could type "[Enter product type]" in the "Product Type" criteria box. When the query runs, you will be prompted ("Enter product type") to enter a product type which will then act as the criteria for the query.

How to add calculated fields to queries
Some times you may want to display the results of a calculation in a query. For example you have a query that displays invoice details, including the number of items of each type bought and the unit price of the items. To display the total spent on each item you can add a calculated field to the query. You create a calculated field by entering an expression into an empty field cell in the query design grid.

For example: Items_Total: [Quantity]*[Unit_Cost]

In a calcuated field you enter the name you wish to give the field first followed by a colon ':'. Then enter the expression for the calculation, in this case the quantity field multiplied by the unit cost field. You can perform all sorts of calculations in a query using calculated fields. You can even add criteria to calculated fields.

For example, in the above scenerio you could limit the query output to records whose Items_Total was greater than 500 by adding '>500' to the criteria grid under the [Items Total] field.

Why does my query have less records than my table, even though I have not set any criteria ?
If you have based your query on more than one table you should check that the join properties are correctly set. To do this, go into design view and double click each join line. Select the appropriate join type from the three options.

Finding all records entered this month
Your table needs to have a field that holds the data that records have been entered. You then need to create a query that selects the records entered this month. Once you have genereated your query as normal, enter the following code into the Field row:

Month([Entry Date])

Where Entry Date is the name of the field that contains the date of entry, and Month is a function that extracts the month from the Entry Date.

Then in the criteria row of this column enter:

Month(Date())

This uses the Month functions to obtain the month of the current date.

Using Like criteria in a Parameter Query
It is possible to create a parameter query in Access that will search for records that match the parameter entered by the user AND contain all other records that have that value plus other text.

For instance you may want Access to return all records that begin with the letter Q.

To do this you need to enter the following expression into the criteria row:

Like [Find records beginning with:] & "*" Or Is Null

The user can enter a Q and the criteria concatenates the wildcard character * to this parameter value, to find all text strings that begin with a Q.

By also using Or Is Null in the criteria the user can enter a blank value into the parameter query to return all records, even if this field is blank.

Displaying only the highest or lowest values in a query's result
Open the query in Design View. In the sort cell of the relevant field click either DESCENDING or ASCENDING. Then click the TOP VALUES box that you will find in the toolbar. Enter either a percentage or the number of highest or lowest values.

For example, by selecting DESCENDING and then entering 10 in the TOP VALUES box you will get the top ten values in your query. Or if you enter ASCENDING and 25% you will get the bottom 25% of values in your query.

You can also input Top Values by setting the TopValues property in the query's property sheet.

Changing column titles displayed in queries
If you didn't enter a caption value in the table design for a particular field you can still change the column title that is displayed for that field in queries/datasheet view.

In the query design grid, if you have a field named '2nd Name', but you'd like it displayed in datasheet view as 'Middle Name', in the field row add "Middle Name:" before the '2nd Name' field, so it looks like this:

Middle Name: 2nd Name

When you display the datasheet the column title for the '2nd Name' field will be 'Middle Name'.

Note that if you entered a caption value in the table design for a particular field then you cannot override that caption.

Action Queries - What are they?

Action queries do something to tables, they can change or move data. There are four types of action query:

1. Append queries add data to the end of a table
2. Update queries update entries in an existing table
3. Delete queries use criteria to identify data to be removed from exisiting tables
4. Make-table queries use data taken from other tables to create new tables

How to combine two similar tables
It is sometimes neccessary to combine two similar tables if for instance you need to combine two seperate customer lists from different departments in your company.

Often you can simply create a query that selects the data you need from each table and represents it in one dynaset of the data. Because the dynaset is 'synamically' connected to the data you can make changes to the data in the dynaset and it will update the record in the original table.

An Append Query will select the records from one table that you specify in your criteria and add them on to the end of the other table. Make sure that the fields of both tables match and have the same data types. By going to Datasheet view before you run your query you can ensure that the query selects the right records.

There is another type of query called a Union Query which can combine data from several tables. The result shows all of the data from all of the tables.

You need to understand SQL statements to create this sort of query. If you want to try this select QUERY, SQL SPECIFIC, and UNION. Then type in the SQL statement into the union query window.

How to use a query to extract just the day, month, or year from a Date field
You will need to create a custom format. Move to the field in Design View, select VIEW, then PROPERTIES. Enter the format in the Format property. Here is a list of the formats you can use and the effect they will have on the date 25th December 1999:

DD - 25
DDD - Sat
DDDD - Saturday
MM - 12
MMM - Dec
MMMM - December
YY - 99
YYYY - 1999

There are also built in functions that will return part of a date. You could use these to create a calculated field to equal one part of a date. For example, the following expression will return the year of birth:

Year([Field containing DOB])

Other built in functions you can use are as follows:

Day(date) - day of the month as a number
Month(date) - month of the year as a number
Year(date) - the year
Weekday(date) - day of the week as a number
Hour(time) - the hour
Minute(time) - the number of minutes
Second(time) - the number of seconds

How to use Blanks or Empty Strings in your queries
If you want a query to search your table for blank fields in your tables you need to set the Allow Zero Length property to Is Null.

How to turn a Crosstab Query into a Table
First create your Crosstab query. Then from the QUERY menu select MAKE TABLE and enter the name of the table. Select OK. Now Select QUERY and RUN to create the new table.

The resultant table will have the usual datasheet orientation and will not include any summarising detail that may have been in the original Crosstab query.

How to limit the number of decimal places shown in a Query's calculated fields
Open the Query in Design View and go to the calculated field whose decimal places you need to Format. Choose VIEW, PROPERTIES and go to the Format property.

There is a drop-down list of formats. Choose the Decimal Places property and enter the number of decimal places you need.

Return to Datasheet View to see the results.

Shorten the results of a calculation to two decimal places
Rather than changing the Decimal Places property, this example shows you how to control the actual number that is calculated rather than what is shown.

Use the following code:

Val(Format(expression, "#.00"))

The Format function will extract the number to two decimal places, but converts it to text to do so. The Val function reconverts the text back into a number.

Why you cannot use a * in an IIf function
The IIf function assumes that the wildcard * is a literal character and will return a * when the expression is true. To return the complete contents of a field you need to enter the field name as the argument in the expression. The following code can be entered in the Field row entry. It will display an invoice date when it is before today's date and nothing otherwise:

Date: IIf([Invoice Date]<Date(),[Invoice Date])

Click the link if you need an Access database repair. Fast service, no obligation, free quote!

Forum Archive | About us | Privacy Policy

Microsoft Access Query Tips