ReviseICT.co.uk homepage
Microsoft Access
Database software

Tasks

 
"Day Trip Database"

To move on, you need to have completed your research about potential trips out.  Have this with you as you complete the next stage - creating your database.  If you haven't found everything - such as the travelling time for each place - this doesn't matter.  The great thing about databases is that you can edit and improve your data later on.

Directions

1. Data types.  If you think back to the information we need about each destination, there are lots of different "types" of data.  Databases can handle all the different types of data, but you need to set the data type for each field.  If you think back to your Excel lesson, you will find some of these very familiar.

2. There are many different data types - as shown below:

Text Basic text that can be letters, symbols or numbers.  This will just show information and will not be used for any calculations.
Hyperlink A text field that is a weblink.
Number This must just include numbers and can be used for calculations.
Currency Just for money.
Memo Used for notes and comments.
Date / Time Just for dates and times.
Boolean Also known as 'Yes / No'.  This is used whenever there are only two possible values - e.g. yes or no, true or false.
AutoNumber Created by automatically Access - this is the individual number for each record. 

3. So why have we bothered with all this?  Why can't we just enter our data?  As has been mentioned before, careful planning and preparation makes a much better database.  If you set the data types correctly you can use them for powerful calculations.  If you have just entered the data as text, it cannot be used for calculations.

4.  Naming conventions.  This is another area that people find tricky, but once understood is really simple.  It is important to use the correct conventions for your field names.  This is so Access can perform powerful calculations and produce useful results.  Each field name must not include any spaces.  Access adds the letters "tbl" to the start of each field name. As you can't use spaces, is is a good idea to use capital letters to make field names easier to read.

Using your previous planning, we are going to use the following names.  See how the field names use capital letters (but no spaces!)

Field Name Data Type Description
TripNo AutoNumber  
Name Text Name of place
Location Text Location
ContactNo Text Phone number
ContactWeb Hyperlink Web address
Activities Memo Potential activities
Requirements Text Special requirements
EntranceCost Currency Entrance cost
Distance Number Distance
TravelTime Time Travelling time
TravelCost Currency Travelling cost

5. Think about what we have done so far.  It all looks terribly complicated doesn't it?  However, if you think carefully, it does all make sense - you have researched the different places and now taken time to properly plan your database.

6. Load Access and select "Blank Access Database".  You will need to create a new folder and a name for the database.  Create the new folder, type "DayTripDatabase" in the file name box and press "Create".

7. This is where your careful planning and preparation will pay off.  Every Access database has a Database window - this is where you can select the different objects - Tables, Queries, Forms and Reports.  From the screen that appears, make sure that the "Tables" tab is selected, and press "New":

8. Select "Design View" (the second option) and press "OK". The Table Design window appears - this is where we are going to set up all our fields.  If you look at the screen you can see some of the terms you are becoming used to.   We are going to enter the "Field Name", define the "Data Type" and add a "Description".

9. Use the information in the table above.  The first field name you need to type in is "TripNo".  Do this, and then select "AutoNumber" from the dropdown menu:

10. In Access we need to set up a Primary Key.  This is the field that Access uses to identify each record.  To set "TripNo" as the Primary Key make sure TripNo is select and then press the Primary Key button from the toolbar at the top.  See if you can find this - it isn't tricky!

11. With the Primary Key selected you now need to enter all the other fields.  Type in the other field names, select their data type and add your own description.  Use the information from the yellow table in point 4 above.  You should end up with 11 completed rows.

12. When complete, click on Save.  Call your table "Table1".
 

All done? Make sure you have saved!
Move onto the next task.
 

Index page