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.
Update Values
in a Table
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
|
