Tips and Tricks for working with Microsoft Access Queries

What does QBE mean (QBE grid)
Query By Example – a method of obtaining data pioneered by IBM in the 1970s. Access converts the query design within the grid to Structured Query Language (SQL).

Change the Column Name in a Query
In the field row of the QBE grid, write the new name to the left of the original field name followed by a colon:

NewName: OrginalName

Show Unique Values Only in a Query
In design view right-click and choose Properties. Set the Unique Values property to ‘Yes’.

Show Records relating to the current Year, Month or Day
Year([Order Date])=Year(Now()) And Month([Order Date])=Month(Now()) And Day([Order Date])=Day(Now())

Add Records from one Table to Another
Append query

Delete Specified Records in a Table
Delete query

‘Key Violations’ error message
Occurs when an append query is attempting to add records where the values in the key field are the same as those that already exist. Duplicate values are not allowed in a key field.

Use an update query:
• to change values with a calculation eg [Price]+1.50
• or to update with values from another table [New Table Name].[New Field Name]

Converting Text Case
Type Ucase or Lcase function in field name row of QBE grid to the left of the existing field name which should be enclosed in brackets.

Ucase([FieldName])

You may need to rename the field:

NewName: Ucase([FieldName])

Show all Fields without adding them to the QBE Grid
In the properties of the query, set the OutPut All Fields property to Yes.
To show all fields for all future queries – Tools | Options | [Tables/Queries] – Output All Fields.

Create a Parameter Value that allows you to enter Partial Criteria
Concatenate the parameter value and the wildcard

Like [Parameter:] & “*”

This will also show any empty field records when a parameter value is given.

Like [Parameter:] & “*” Or Is Null

To pick up records with blank field entries only, type a space in the parameter dialogue box.

Create a Parameter Value that allows you to enter Partial Criteria (ends with)
Concatenate the parameter value and the wildcard

Like “*” & [Parameter:]

Create a Parameter Value that allows you to enter Partial Criteria (contains)
Concatenate the parameter value and the wildcard.

Like “*” & [Parameter:] & “*”

Incorporating a LookUp Table
Lookup table may show discounts available on products dependent on price range – fields would be StartPrice, EndPrice, Discount. Create a select query using the Product table (that includes the price of the product) and the lookup table. In the criteria row under the product price field enter the following Between operator.

Between [LookupTable].[StartPrice] And [LookupTable].[EndPrice]

Add the Discount field to the QBE grid to retrieve the discount appropriate to the price of each product.

Refreshing a Crosstab Query
Close and reopen.

Printing the SQL for Queries
Tools | Analyse | Documentor [Queries]

The Options button allows you to select what information will appear in the document

IF Statements
IIF([Field] condition, true, false)

Order Discount:IIf([SumOfQuantity]>2,([Book Price]-([Book Price]*0.1)),[Book Price])

Using Calculations that Involve Expression Fields when Totalling in Queries
In the calculating field that uses expression fields, set Group By to Expression.

Extracting Elements of a Date
Create a custom date in the field properties, using the following syntax:
DD - 05
DDD - Sun
DDDD - Sunday
MM - 12
MMM -Dec
MMMM - December
YY - 99
YYYY - 1999

When a Query Returns an Impossible Number of Records
There is no relationship between the tables included in the query, so every record in one table is linked with every record in another table.

Ways to Tune a Query so it runs Quickly
• Run the performance analyser
• Index the fields that join the tables in the query
• Index fields that contain criteria
• Compact the database
• Avoid the Not In operator