Understanding Databases – Create a named single table query

Queries are used to extract and analyse data from tables. Click on the Queries icon, then select Create Query in Design View.

4-4 Create Query

The single table query will use the ‘Company employees’ table created in a previous tutorial.

4-5 Add Query

In the ‘Add Table or Query’ window, click the table(s) you want to extract data from, click ‘Add’, and then ‘Close’ to start designing a query

4-6 Add Field

To add a field to the query, double click the field name in the table window and it will appear in the table below. Alternatively, click in the field cell in the table and select the name from the drop-down menu.

To delete a field, right click in the grey box above the column and click ‘delete’.

4-6 Add Field

To select all fields from a table double click the asterisk. This will create a query to extract data from every field in the table – i.e. all of the records.

To run the query, select ‘Run Query’ from the Edit menu or press F5

This will open a preview window displaying the data from all fields. To close the preview select ‘Preview’ from the View menu or press F4

4-7 Delete Field

To add specific search criteria, select the field then type criteria into ‘Criterion’ row, e.g. <3 will return all data less than 3 in the ‘Group’ field You can add more criteria in the ‘Or’ rows, e.g. >5 will return all data greater than 5 in the ‘Group’ field

In the example, as Last Name, First Name and Group are set to visible, if we run the query (F5), these fields will display for Group records less than 3 and more than 5

Other search criteria that can be used are = (Equal), <> (Not equal to), <= (Less than or equal to), >= (Greater than or equal to)

To name the query, select ‘Save As’ from the File menu, name the query, then click ‘OK’

Example

Another example of using a query to extract data from a single table could be a bank that wants to find out customers that have savings higher than a certain threshold in their account.

A table could contain Account number, Name, Amount.

The query could then set criteria in the Amount field of >10000 for example to list all the customers that have savings of more than 10000.

This query could then be used to produce a report, displaying these customers in a user-friendly format. More on this in a later tutorial.