Transcript 3APPT11ACT03e

Activity 3
Creating queries with more user-defined
criteria in the Design View
In this activity, we are going to create several queries with more
user-defined criteria.
1. Start Microsoft Access and open the database file ch11_01.mdb in
the hard disk.
2. Click ‘Queries’ in the ‘Objects’ column and then click New .
Activity 3
Creating queries with more user-defined
criteria in the Design View
3.
Double-click ‘Design View’ in the ‘New Query’ dialogue box.
4.
Double-click ‘CPU’ in the ‘Show Table’ dialogue box to add the
table into the Design View window. Then, click Close to
continue.
Activity 3
Creating queries with more user-defined
criteria in the Design View
5.
Add the fields to the Design View window as shown below.
6.
Enter ‘<1100’ into the criteria box of the ‘Price’ field.
Activity 3
Creating queries with more user-defined
criteria in the Design View
7.
Select ‘Ascending’ as the sorting criterion for the ‘Price’ field.
8.
Select ‘Queries’  ‘Run’ to execute the query. Alternatively,
click the ‘Run’ button
on the toolbar.
(a) How many records are shown in the query window?
_________________________________________________
14 records.
(b) Which is the most expensive product? How much is it?
_________________________________________________
Intel P4D 925 (3,000 MHz). It costs $1,058.
Activity 3
Creating queries with more user-defined
criteria in the Design View
9.
Close the query window. Try to create a query for each of the
questions listed below.
(a) Display all fields in Motherboard and those motherboards
with Price less than or equal to 1100. Sorting is not required.
(b) Display RAM Brands, Product Names (field ‘Product’), Sizes
and Prices that those Spec is ‘DDRRAM’ and Size is ‘1024MB’.
Arrange the records in descending order of Prices.
(c) Display CPU Product Names that start with ‘CELERON’.
Arrange the records in ascending order of Product Names.
Activity 3
Creating queries with more user-defined
criteria in the Design View
9.
Complete the following table to show the results of your queries.
Selected
field(s)
From
table(s)
Criteria
Sort by
field(s)
(a)
Brand, Socket,
Price, Product
Motherboard
Price: <= 1100 None
(b)
Brand, Product,
Size, Price
RAM
Spec: Like
‘DDR-RAM’
Size: =1024
(c)
Product
CPU
Product Name: Product
Like ‘Celeron*’ (ascending)
Price
(descending)
Activity 3
Creating queries with more user-defined
criteria in the Design View
10. Close the database file and exit Microsoft Access.
Activity 3
Creating queries with more user-defined
criteria in the Design View
10. Close the database file and exit Microsoft Access.