CONTENTS PAGE
CREATING A NEW DATABASE .................................................................................. 2
CREATING THE STRUCTURE OF A TABLE ........................................................... 2
SAVING THE STRUCTURE ........................................................................................ 3
ENTERING THE DATA ................................................................................................. 4
WIDENING A COLUMN .............................................................................................. 4
SAVING DATA ............................................................................................................. 5
CLOSING AND EXITING PROCEDURES.................................................................. 5
CLOSING THE DATABASE WINDOW...................................................................... 5
EXITING FROM ACCESS ............................................................................................ 5
UPDATING THE TABLE ............................................................................................... 5
OPENING AN EXISTING DATABASE ...................................................................... 5
ADDING NEW RECORDS ........................................................................................... 5
AMEND EXISTING RECORDS ................................................................................... 6
DELETE A RECORD .................................................................................................... 6
REPLACE EXISTING TEXT ........................................................................................ 6
CHANGING THE STRUCTURE/DESIGN .................................................................. 6
ADDING A FIELD......................................................................................................... 6
CHANGING A FIELD NAME ...................................................................................... 7
QUERYING THE DATABASE ...................................................................................... 7
QUERY OPERATORS .................................................................................................. 9
SORTING A QUERY................................................................................................... 10
SAVING A QUERY ..................................................................................................... 11
EXERCISE 1 ................................................................................................................... 11
AMENDING A QUERY .............................................................................................. 11
EXERCISE 2 ................................................................................................................... 11
INTRODUCTION TO ACCESS PART 2 .................................................................... 13
REPORTS ..................................................................................................................... 13
Open up Microsoft Access by clicking Start/All Programs/Microsoft Office/Access.
CREATING A NEW DATABASE
To create a new blank database:
1. Click New on the toolbar.
2. In the New File task pane, under New, click Blank Database.
3. In the File New Database dialog box, specify a name (in this case name your
database sales and location for the database, and then click Create.
CREATING THE STRUCTURE OF A TABLE
2. You will now view the database window – this is the main screen for managing
different areas of your database.
Introduction to Access 2
3. By default the database window will appear showing the tables window – you are
going to design a table using Design view so double click that option or make
sure Create table in Design view is highlighted and click on the New icon above.
4. Look at the window that appears. Each item of data in a database is inserted in a
field which has a name and is of a specific type.
5. In the box under the heading Field Name type in First Name
6. Press the Tab key to move to the Data type box. The word Text should be
displayed immediately.
7. Look further down the design window and you will see other information appears
under a General Tab – we are going to change the Field Size to 20 (this is the
number of characters or letters we wish to restrict the size of this field to). The
larger your field sizes the bigger your database file.
8. Now click in the second row of the Field Name column and enter the details of
the other fields:
Field Name Data Type Field Properties
First Name Text Size 20
Surname Text Size 20
Street Text Size 40
Town Text Size 20
Postcode Text Size 10
Date Registered Date/Time Format – Short Date
Ref Num Number Size – Long Integer
Amount Owed Currency Format – Currency
SAVING THE STRUCTURE
1. Click on File on the menu bar and then click on Save – when the save as window
appears rename from table1 to customer and click OK
2. When you click OK you will be presented with the next window – at this stage in
your introduction to Access we are not going to need a Primary Key so click on
No
Introduction to Access 3
3. You have now created a table customer inside your database sales
ENTERING THE DATA
So far we have been working in design view – creating the structure of the table inside
the database.
Now you need to add data – these are the records that contain the information we want to
save in the database.
You should be back at your database window which will have changed to show the newly
saved table – customer
1. Highlight customer and click on open
2. Enter the following information using the tab key to moving between the fields.
Proofread the data carefully – any errors can be changed by clicking where the error
has been made and using delete and rekeying the correct details
WIDENING A COLUMN
If you need to widen a column to see all the details –
Introduction to Access 4
1. Position the cursor on the line between the 2 column headings (eg between street
and town)
2. Wait until the cursor becomes a double headed arrow and then click and drag to
the right.
SAVING DATA
Unlike the structure the data is saved as soon as you press tab or enter key at the end of a
row – all you need to do now is close the table. Select File drop down menu and close. (If
you have made any changes to the layout be widening columns etc you will be prompted
to save these changes – say yes).
CLOSING AND EXITING PROCEDURES
CLOSING THE DATABASE WINDOW
Select File/Close
EXITING FROM ACCESS
Select File/Exit
UPDATING THE TABLE
OPENING AN EXISTING DATABASE
1. Open Microsoft Access
2. Select File/Open and locate the file you saved from the location you saved it in.
3. Select Open from the dialog box
ADDING NEW RECORDS
1. From the database window, highlight customer and click on the open icon
2. Click in the first cell of the blank row at the bottom and type in the following 3
records, one row at a time.
Introduction to Access 5
AMEND EXISTING RECORDS
You may have to use the scrolling bars at the right hand side to move up and down your
screen or the scroll bars along the bottom to move along from left to right in order to
view all your data or access the record you want to change.
DELETE A RECORD
1. Click in the grey cell to the left of the record to highlight the whole row
2. Select Edit/Delete/Yes
3. Try this out by deleting the record for Ken Jones
(note that once you have deleted a record you cannot undo this action in Access)
REPLACE EXISTING TEXT
1. Just like other Office packages you can highlight a block of text by clicking and
dragging over it to select it.
2. Type in the new data – this replaces existing text.
Change John Green’s town to Port Glasgow and the postcode to PA13 6YK
CHANGING THE STRUCTURE/DESIGN
Sometimes it may be required to change the actual structure of the database eg to add a
field – this is referred to as changing the design of the database. To try this out, open the
sales database and with the database window showing and highlight customer table click
on design.
ADDING A FIELD
To add a field to the end of a list of fieldnames simply click in the next blank row and
key in the new field name and type.
If the field is to be inserted between existing field names then click anywhere in the row
below where you want to add the new field and
Select Insert drop down menu/rows
Introduction to Access 6
Key in the new Field name and type
Try this out click on the grey cell to the left of the Date Registered field and create a new
field Telephone. Note that although you might assume this should be a number field this
is not the case. The spaces between the numbers mean it must be a text field – size 20.
Save the changes with File/Save then select View/Datasheet to add the following
telephone numbers to the database:
customer
Surname Telephone
Green 01475 783388
Small 01475 684839
Gault 01475 338383
Hill 01475 739291
Thomson 01475 737389
Murphy 01475 393900
Miller 07745 889392
Thompson 07785 143508
Laing 01475 939300
(note you can flick back and forth between design view and datasheet view by using the
first icon on the toolbar) When you are in datasheet view it looks like this
When you are in design view it changes to look like this
Change back to design view to carry out the design change which follows:
CHANGING A FIELD NAME
1. Click in the appropriate field and edit the text using arrow and delete keys.
2. Change amount owed to outstanding
QUERYING THE DATABASE
The whole point of creating a database is so that specific data can be searched and
produced quickly and accurately, or sorted into a particular order. This is known as
querying the database.
Introduction to Access 7
Let’s try it out:
In the sales database window select Queries tab. Make sure you are highlighting Create a
query in Design view. Click on New
Click on OK
The following screen will appear – in the Show Table dialog box click Add to add the
table customer to the query window.
Now you can close the show table dialog box.
Indicate which fields you want to be part of the query – highlight the field in the list and
double click it to put it in the first available column in the grid or click and drag it down
to the grid.
Try this out – put the following fields in the grid
First Name
Surname
Town
Ref Num (you will have to use the scroll bar on the list to find this field)
Introduction to Access 8
(the sequence in which they are placed on the grid is the sequence in which the field
names appear when the query is run). Make sure the tick boxes are all in place to show all
the fields in the result.
Now we will consider the clever part of querying – using criteria to return the specific set
of information we want.
In the grid below Ref Num, click in the cell in the row opposite Criteria and type the
following:
>=10 (this will find all customers who have a reference number of 10 or more)
Select query/run or run icon to display the answer
QUERY OPERATORS
In the previous example we used >= to tell the programme we are looking for “greater
than or equal to”. There are several more query operators used with Access – these are:
Introduction to Access 9
> Greater than eg > 100 A higher number than 100
(can only be used with numerical
values such as integers/dates/currency)
= Greater than or equal to eg >=100 100 or more
(can only be used with numerical
values such as integers/dates/currency)
= 01/01/2005 and inclusive
> button to move all the available fields across to Selected fields and click
next. (Before you do this make sure that “outstanding” field is the last on the list.
In the Group Levels, select Town to Group by
In the next dialogue box select sort by Surname and click on the Summary Options
button
Introduction to Access 13
Now click on Sum for the outstanding field ( in this dialogue box you are asked which of
the number fields you want to calculate – we are going to sum the amount outstanding)
Make sure Detail and Summary are selected.
Click next.
Select landscape
Move through the rest of the wizard clicking next until you are asked for a Report Title –
insert the following title: Report of Outstanding Accounts
Now click finish and you should produce a report something like this:
Introduction to Access 14
You will notice a few elements that you might like to change or delete – eg the
“Summary for Town…..” information on the left side and the word Sum.
You can edit a report by clicking on the Design icon to access the design of the report.
Click on the entry for “Summary……” under Town footer – you should see the sizing
handles appear. Now just delete the entry altogether.
Click on Sum and edit it to say subtotal
Now save your changes.
Try creating a report from query 5.
Introduction to Access 15