ReviseICT.co.uk homepage
Microsoft Access
Database software

Tasks

 
"Day Trip Database"

In Task 2, you began creating your database.  You have done a great deal of hard work to research, plan and setup the database.  If you closed your work before, load Access again and load your word using "Open an existing file".  We are now going to enter the data.

Directions

1. Your database window will appear.  So far, we have only defined our fields - setting up our database.  We have used the Design View for this.  When we enter actual data, as we are about to, you will use the Datasheet View.

2. Click on "Table1" (or the name you saved your table as).  Your table will now appear in a different format, now ready to enter your data.  You should be able to see all the different field names that you entered in Task 2:

3. Access will automatically create the TripNo field, but you need to fill in the other fields.  If you want to make a column wider, just drag the column headers (field names) as you have already done in Excel.  You might also like to maximise your table so you can see as much as possible.

4. Type in the rest of your data.  Remember to save your work regularly.  To move quickly between each field you can press the Tab key.  This will obviously take you some time, but your preparation work has made the task much easier.

5. Once you have entered your data, you can see how useful the database is.  Close Table 1 so you can just see your database window.  As you learned in task 2, this is where you can select all the different objects.  This is where you can edit, manipulate and sort your data.

6. When entering data, mistakes can occur - you are only human!  To check data you can set up validation rules.  This is basically where you set boundaries for your data - such as the cost has to be between 0 and 25 or the travel time must be under four hours (remember it is a day trip!).

7. From the database window, make sure "Table1" is selected.  Press "Design".  This will open our database in Design View again.  Click on the "Name" field name.  Underneath the data, you will see a General tab.  There are two rows named "Validation Rule" and "Validation Text".  These are to be used for our validation.

8. Think about which fields we need to set rules for.  There is no point setting rules for fields which just contain text as Access cannot check them.  Click on your EntranceCost fieldname.  Enter the text as shown below:

Try to create your own rules for Distance, TravelTime and TravelCost.  If you need help setting up the rules you can click on the button to the right of the rules (circled above).  Save your work when you have finished.

6. To find out information, you need to query the database.  This is where you ask the database a question such as "Which trips cost less than 10 to go in?" or "Which trips take less that two hours to get to".  With a database, you can ask long and complex questions which would be far too difficult for a human being to answer quickly.  Also remember we have just created a small database.  A travel company might have a similar database with many thousands of records.

7. Making queries is the whole basis of using a database - this is why we have carefully planned and added data, even setting up checks to see if the data has been correctly entered.  From the database window, select the "Queries" tab and then click on "Create query using wizard".

8. You now need to work through the next screen on your own!  This is where you set up the questions that you would like to ask and set the data you want to display.  What would be the problem if you just selected the options shown below? [Clue: What information is going to be displayed?]

Work through the rest of the options and then give your query a name - such as "FirstTry" and Click on Finish.

9. This query is now saved.  If you entered a thousand more places you could visit (don't worry, you aren't going to!), the same query could be used.  This was a simple query as we have just asked a simple question - show the entrance costs, the distances and so on.

10. Multiple Criteria queries are the most helpful.  This is where you can set up complex questions.  Imagine how tricky a question could get - "I would like a trip that has an entrance fee of less than 10, takes between 0 and 2 hours, but is at least 26 miles away and won't cost any more than 10 per person for the coach".

11. From your database window, select "Query" again, but this time select "New".  Select "Design View" and press "OK". In the next window, add "Table1" and click on "Close".  Work through the Query grid and enter the data as below.  You can select fieldnames from the drop down box that appears in the cells. 

12. We have now set up our complex question.  We can ask the database to answer the question from point 10! We need to "Run" the query.  To do this, you just have to press the "Run" button.  See if you can find this near the top of your screen.

13. It doesn't have to stop there.  You can make your queries more complicated using comparison operators.  Under the criteria row there is a row labelled "Or".  This is where you can add further criteria.  Imagine we wanted to show trips that were either under 10 or over 50.  In the EntranceCost column, you could add ">50" underneath "<10".  This is adding a comparison operator.

14. If you have reached this far, you have done extremely well.  Congratulations.  You have covered the basics of Access and worked through some complex material.  By experimenting further you can see how powerful Databases are.  Try the assessment to see how much you understand.


All complete?
Test your understanding with the assessment
 

Index page