4.2: Simple Query

Last updated
Save as PDF

Page ID: 15511

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

The simplest query is one that displays a complete table – all rows and columns. Suppose we want to list all books in the library. The process of creating the query is as follows:

Click on the Create tab if necessary and then click on the Query Design icon. Now you can right-click in the Relationships area and choose the Show Table option
A window pops up, and from the list of tables you must double-click Book:

Figure \(\PageIndex{1}\): The QBE List of tables.
Choose Close from the Show Table pop up window

MS Access displays the Book table and its fields in the Relationships area. The first in this list is an * which stands for all attributes – double-click the *. This results in the following:

Figure \(\PageIndex{2}\): Choosing all fields of the table.

We can run the query to test it and confirm it does what we expect: list all rows in Book. To run a query, click the Run icon:

There are other views of a query. If you click the drop down just below the View icon:

Figure \(\PageIndex{4}\): Several views for a query.

You can see all the ways of viewing a query, including:

Datasheet View
Design View
SQL View

You can also run a query by choosing Datasheet View. When developing a query one often alternates between Datasheet View and Design View in order to get the query working as required. When you run a query, MS Access will retrieve the information requested. In this case the results of running the query are:

Table \(\PageIndex{1}\): Query results.
callNo	title	author	paperback
CB 351 M293 1983	Atlas of medieval Europe	Donald Matthew	True
HQ 1143 P68 1975	Medieval women	Eileen Power	False
PC 14 V48 1965	Medieval miscellany	Frederick Whitehead	True
QA 76.73 S67C435 2004	Joe Celko's Trees and hierarchies in SQL for smarties	Joe Celko	False
QA 76.73 S67C46 1997	Joe Celko's SQL puzzles & answers	Joe Celko	True
QA 76.76 A65P76 2011	Programming Android	Zigurd R Mednieks	True
QA 76.9 D26H355 2008	Information modeling and relational databases	T A Halpin	True
QA 76.9 D26H39 1996	Data model patterns : conventions of thought	David Hay	True
QA 76.9 D35C45 1999	Joe Celko's data & databases : concepts in practice	Joe Celko	False
R 141 E45 2006	Medieval medicine and the plague	Lynne Elliott	False
R 487 T35 1967	Medicine in medieval England.	Charles H Talbot	False

You can save the query:

Figure \(\PageIndex{5}\): Pop-up for saving a query.

Now you can see the query listed as a database object. The query can be run any time by an end user. The results of the query are not stored or saved – only the definition of the query. Whenever a user runs the query the current contents of the Book table are accessed.