Microsoft Access Create queries for a new database Lecture 4 Dr. Huynh Tan Quoc
Business Computing Skills - BA120IU Topics 1: Database Essentials 2: Creating Database Tables
3: Working with Table and Database Records 4: Modifying Tables and Fields 5: Creating Forms 6: Creating Reports 7: Creating Queries
8: Using Controls in Reports and Forms 1-2 Cou C rse se goals
• Use the Query Designer to create a select query that returns data from a single table.
• Create a select query that asks you to enter parameters and
then returns results that match those parameters.
• Use a formula in a query to calculate a sum.
• Use the Query Wizard to create a select query that returns data from two tables.
• Use an expression to concatenate fields. 1-3 CR C EATE QUERIES ERI ES FOR F A OR A NE W NE W D ATABASE Ge G t e ta n a swe sw r e s r from m you o r r d a d t a a t
Your new asset-tracking database is
coming along. You have tables,
relationships, and data, so the next
step is to build some queries. They’ll
help you answer important questions,
and they’ll make it easier to create
The building blocks of a select query. forms and reports. 1-4 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Ge G t e ta n a swe sw r e s r from m you o r r d a d t a a t
Specifically, you’ll create select
queries. They’re components that
retrieve and process your data and
display the results in a datasheet.
For example, if you want to know
who to call when a computer needs
The building blocks of a select query.
repair, you create a query that
extracts the relevant names and phone numbers. 1-5 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Ge G t e ta n a swe sw r e s r from m you o r r d a d t a a t
That’s what the picture shows. The
query takes asset data from the first
table, the names of technicians from
the second table, and displays the
result in a way that you can consume.
The building blocks of a select query.
Let’s start by looking at the parts of a query. 1-6 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Th T e h e b a b sics c of o f c r c ea e t a itn i g n g se l se ec e t c tqu q e u ri r e i s e
Access provides two primary ways to
create select queries — the Query
Designer and the Query Wizard.
Regardless of the tool you use, you follow some common steps when you create a select query.
The basic process for creating a query. 1-7 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Th T e h e b a b sics c of o f c r c ea e t a itn i g n g se l se ec e t c tqu q e u ri r e i s e
Start by choosing a record source
for the query. A record source can
be one or more tables, one or more
queries, or a combination of the
two. The picture shows a table open in the Query Designer.
The basic process for creating a query.
From the record source, select the
fields that you want to see in the
query. The picture shows fields in
the Query Designer, but you do the
same thing in the Query Wizard. 1-8 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE
Add any sorting, filtering, or other Th T e h e b a b sics c of o f c r c ea e t a itn i g n g se l se ec e t c tqu q e u ri r e i s e
selection criteria to your queries.
For example, if you use the criteria
shown in the picture, the query will
only return data for assets purchased
after May First of 2010. You can also
use criteria that make a query ask
you for input before it runs; you’ll see that later in this course.
The basic process for creating a query.
After you finish adding fields and
any selection criteria, run your
query to see if it gives you the correct results. 1-9 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Way a s y t s o t o u se u se q u q e u ry y r es e ul u tls t
When you run a select query, Access
displays the results in a datasheet. The
result is called a record set, and you can
work with it in the same way that you work with a datasheet.
For example, you can add or change
A query as the data source for a report.
data, and Access will write your
changes to the tables that serve as the record sources for your query. 1-10 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Way a s y t s o t o u se u se q u q e u ry y r es e ul u tls t
You can also use your record sets to
provide data for forms, reports, and
even other queries. For example, if you
want a report on the computers that you
need to replace, you can create a query
that returns that data, and then quickly build your report.
A query as the data source for a report.
The picture shows this. The query
returns only those records where an
asset has been marked for retirement. 1-11 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Way a s y t s o t o u se u se q u q e u ry y r es e ul u tls t
The final two courses in this series show
you how to create forms and reports that
use queries as data sources. For now,
remember that unlike tables, record sets
don’t physically exist in your database.
Instead, Access stores the query, and it only
displays a record set when you run the query.
A query as the data source for a report.
So let’s build one. We’ll start with the Query Designer. 1-12 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Qu Q e u ry y a a sing n l g e l e r eco c r o d d sou o rc r e
The Query Designer gives you the most
control over a select query. It also makes
it easy to create a query that uses a single table as a record source.
For example, this type of query makes it
easy to list your assets and their purchase Using the Query Designer. dates. 1-13 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Qu Q e u ry y a a sing n l g e l e r eco c r o d d sou o rc r e Here’s the process.
On the Create tab, in the Queries
group, click Query Design. The
designer starts and displays the Show
Table dialog box. On the ribbon, the Design tab also appears.
In the dialog box, select your record Using the Query Designer.
source. You can use a combination of
tables and queries, and the ones you
select appear in the upper section of the designer. 1-14 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Qu Q e u ry y a a singl e e re r c e o c r o d d sou so rc r e
In your record source, double-click the
fields that you want to see in your
record set, or results. Your choices
appear in the bottom section of the designer.
When you finish adding fields, go to
the Design tab, and in the Results group, click Run. Using the Query Designer. Your record set appears as a datasheet. 1-15 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Ma M ke k e a a q u q e u r e y y ask k f o f r o in i p n ut
The type of query that you created in
the previous section can return a lot
of data. A common way to limit what the query returns is to use a parameter.
Put simply, parameters make the Using criteria in a query.
query ask for input before it runs;
they are a type of filter that you build into your query. 1-16 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Ma M ke k e a a q u q e u r e y y ask k f o f r o in i p n ut
Here’s how to use parameters in your queries:
Open the query in Design view, and
in the Criteria row of the field you
want to filter, enter your parameter.
For example, you can use the criteria shown in the picture.
When you run the query, that criteria Using criteria in a query.
asks you for a start date, then an end
date, and it returns only the records
that fall within the dates you specify. 1-17 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Us U e e a a f o f rm r u m l u a l a in n a a q u q e u r e y
Another common way to alter the
selection criteria in a query is to add a function.
For example, if you want to know how
much your company spent on office
furniture last year, you can add an Using a function in a query.
aggregate function to the query and the
sum will appear in your results. 1-18 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Us U e e a a f o f rm r u m l u a l a in n a a q u q e u r e y
Open your query in Design view. On the Design tab, in the
Show/Hide group, click Totals. The
Total row appears in the designer.
In the field where you want to use
the function, click the Total row and
select a function from the list.
When you run your query, the results include your calculation. Using a function in a query. 1-19 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Qu Q e u ry y m u m l u tlitp i l p e l e r ec e or o d d so u so r u c r e c s e
You can use multiple tables, or other
queries, as the record source for a new
query. In fact, you’ll query multiple record
sources far more than you will single
sources. Multi-source queries are how
you answer questions such as which
assets came from which suppliers.
Using the Query Wizard to build a multi-source query.
The Query Wizard is the easiest way to
get started building multi-source queries,
especially if you’re new to Access. 1-20 CR C EATE QUERIES ERI ES F OR R A A NEW NEW D ATABASE Qu Q e u ry y m u m l u tlitp i l p e l e r ec e or o d d so u so r u c r e c s e
On the Create tab, in the Queries group, click Query Wizard.
Complete the wizard. As part of that
process, select the tables and fields
you want to use as your record source, and…
Give your new query a descriptive
Using the Query Wizard to build a multi-source query.
name, and remember to avoid using spaces in the name. 1-21 Sugg S est e i st ons for ons f or p r p a r c a titc i e c 1.
Create a select query from a single table. 2.
Make the query ask for your input (add a parameter). 3.
Add a calculated field to a query. 4. Format the calculated field. 5.
Create a query from multiple tables. 6.
Concatenate fields with an expression.
Online practice (requires Access 2010) 1-22 Test e quest ion i 1 on
When you first create a query, you select which of the following? (Pick one answer.) 1. A record source. 2. A system table. 3. A record set. 1-23 Test e quest ion i 1 on
When you first create a query, you select which of the following? Answer: 1. A record source.
The record source can be a combination of tables and queries. 1-24 Test e quest ion i 2 on
Use criteria in a query when you need to do which of the following? (Pick one answer.) 1.
Ensure your data is properly formatted. 2. Add formulas to a table. 3.
Sort, filter, or otherwise limit what the query returns. 1-25 Test e quest ion i 2 on
Use criteria in a query when you need to do which of the following? Answer:
3. Sort, filter, or otherwise limit what the query returns.
Criteria can be quite powerful, too. For example, you can
use criteria to find records for people who live in a given
and have birthdays that fall between dates that you specify. 1-26 Test e quest ion i 3 on
Before you can use multiple tables as a record source,
those tables must: (Pick one answer.) 1. Be open in Datasheet view. 2.
Participate in a relationship. 3.
Not serve as the record source for another query. 1-27 Test e quest ion i 3 on
Before you can use multiple tables as a record source, those tables must: Answer:
2. Participate in a relationship.
If the tables don’t participate in a relationship, the query
returns all the data in each table, and lts are almost always meaningless. 1-28