Database Queries – With Microsoft Access

Access Queries – Cheat Sheet

Creating Queries:

Selecting Tables: Double Click – or use mouse to select and click

ADD, Close window after adding all tables desired.

HINT: Only choose the

tables REQUIRED to

retrieve the necessary

data – DO NOT ADD

EXTRA TABLES!

HINT: IF you select multiple

tables – ensure that the tables

share a relationship… otherwise,

the output will be incorrect!

Selecting Columns for Query Output

SHOWING Columns – Click the "check-mark" to show or hide a

column

Sorting Output – Drop down the Sort value of ASC / DESC – multiple sorts can be used – the order of columns (left to right) determines Primary and

Secondary Sort Columns

Add columns to your query by clicking the

column in the table

OR

By clicking on Arrowhead to drop down

list and select the field in the design area

Selecting a subset of table Rows – by Adding Criteria

Multiple Criteria

The above only includes rows where the drug name = "Crestor" and the Date Start is greater than 1/1/2017. Notice that

Access needs double quotes " " around text data – and # # - around dates.

BETWEEN -- used to set a criteria for a range of values.. best

used on dates and numeric data.

Placing a value in the Criteria

row of the design area Place

the value you want the query

to include the rows that match

– in the Criteria line of the

column to match

You can separate criteria

under the same column

with the word OR

You can place criteria under

multiple columns – if they

are on the same row –

ACCESS treats them as an

AND – if they are on

different rows, ACCESS

treats them as an OR.

LIKE – allows to match part of a column - when used with the

wildcard *

Is Null – Is Not Null, returns columns that are / or are not empty

Displaying only Top Values – Option Located in Query Ribbon

"Sports*" – returns any rows with a Specialty that starts with Sports

"*Sports*" – returns any rows with a Specialty that has Sports anywhere in the Field

Top Values – Select or Enter

number to Return

Top Values Depends on Sorting

Formatting Query Output – Option Located on Query Ribbon

Grouping & Aggregating Data

Grouping & Aggregate Functions:

• Count – counts the number of rows within the group

• Sum – accumulates the values of a column within the group

• Min – inspects the rows within a group – and selects the Minimum value

• Max - inspects the rows within a group – and selects the Maximum value

• Avg – calculates the average of the values for a column within the group

Then – click on Property Sheet,

and choose the appropriate

format option

To display the aggregate values for the entire table

Without a Grouped Column --- Access aggregates the data in the entire table.

To display the aggregate values for a group

To display totals by DrugID /

DrugName: Use the GROUP BY

option under the columns – and then

select any columns to aggregate for

each DrugID / DrugName found in

the data.

If your output looks like this –

The ###### means that the

column isn't wide enough to

display the data… either widen

the column, or format it to Fixed/

and 1 Decimal.

Query Wizards to find Unmatched or Duplicates

Duplicate Query Wizard – finds

potential duplicate data within a

table..

UnMatched Query Wizard –

compares two tables and finds

rows of data in one table (the table

with the primary key) that doesn't

have any related rows of data in

another table (the table with the

foreign key).