Database Queries: Pets (Access 2003) - Teach-ICT

Database Queries: Pets ( Access 2003)

? Open Microsof t Access. ? Creat e a new dat abase called ` Pet s' . ? Creat e a new t able, wit h t he f ollowing f ields:

o Pet ID o Pet Name o Type of Animal o Colour o Age o Out st anding Feat ures ? Select appropriat e dat a t ypes f or each f ield. ? Select appropriat e f ield sizes f or each f ield. ? Choose or creat e an appropriat e primary key f or t he t able. ? Save t he t able as ` Pet s' .

Now add 5 records t o your Pet t able. Copy t he records below int o your t able:

Figure 1: Pet records Close t he Pet s t able. We now want t o creat e a query t hat will only display a list of all t he pet names and t he t ype of animal t hat t hey are. Follow t hese st eps:

? From t he main menu, select ` Queries' ? Select ` Creat e query in design view' ? Eit her double-click on t he select ion, or

select ` Open'

1

? teach- All Rights Reserved

From t he ` Show Table' window t hat

is displayed:

? Select ` Pet s' ? Click ` Add'

This allows you t o select f ields f rom t he pet s t able. If you had ot her t ables in your dat abase t hey would be list ed here f or you t o choose f rom.

The query design window will be displayed.

You can see t hat t he Pet s t able has been added t o t he t op of t he window.

We now need t o select t he f ields t hat we t o include in our query.

Do t he f ollowing:

? Double-click t he pet name f ield f rom t he list of f ields in t he pet s t able. ? The pet name f ield should be displayed in t he query design in t he f irst row of

t he f irst column. ? Anot her way of adding f ields is by clicking on t he f ield and dragging it ? click

on t ype of animal in t he list of f ields, hold your lef t mouse but t on down and drag t he f ield t o t he f irst row in t he second column.

Your query design should now look like t his, wit h t he pet name list ed f irst f ollowed by t he t ype of animal t hat t hey ar e:

Select ` File' > ` Save' t o save your query. Call it ` pet names and t ypes of animal' .

Not e: It is very import ant t o save your queries wit h descript ive names. You can end up wit h many queries and will not know what each does as t hey are of t en very si m i l ar .

2

? teach- All Rights Reserved

You now need t o t hink about what inf ormat ion will be displayed bef ore you act ually run t he query. Go back t o f igure 1: Pet records in t his workbook (page 2) and t hink about t he inf ormat ion t hat you t hink will be displayed. Wit h your query open in design view, click on t he run icon This will run t he query and select t he dat a t hat you have specif ied, as below: As you can see, only t he pet names and t ype of animal are list ed. The query has select ed only t he f ields t hat you select ed. At t he bot t om of t he window you can move bet ween records and it shows you how many records t here are in t he select ion. Try clicking on t he arrows t o move bet ween records. Close t his query, saving any changes t hat have been made.

TASK 1 Creat e a new query t hat list s only t he t ypes of animal and t he colour f ield. Save t he query as t ypes of animal and colour. Run t he query t o observe t he result s. Close t he query, saving any changes.

3

? teach- All Rights Reserved

SELECTING CRITERIA IN A QUERY

Wit hin a query, it is possible t o narrow down t he select ion even more.

In t he previous t asks, you have narrowed down result s, by select ing f ields. It is also possible t o narrow down t he result s by select ing specif ied records.

To do t his we will specif y crit eria in t he query.

The main crit eria t ypes are:

<

>

=

= OR AND wildcard

We will work t hrough each of t hese:

? Creat e a new query, select ing only pet name and age f rom t he list .

? Save t he query as ` select ing ages' .

LESS THAN ( ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download