LTC - Task 4 - Helpers Database Pt.2

• Be able to understand how data is stored in a database
• Be able to create your own table in the 'Helpers Database'
• To fully understand fields, records and tables
• Know how the data can be manipulated in a database and sort and filter a database to produce the required information
• To understand the concept of 'Mail merge'
• To be able to produce mail merged documents from your database

For this task you will:

• learn all about data types
• plan the table you are going to produce using given fields
• fill the table with data
• print out various pieces of information by using sort and filter techniques

Task 4a - Data types

As you know data is stored in records in a database. Each individual piece of data contained in the record is stored in a field. When storing data in a field you need to know what 'data type' it should be stored as.

In the table below you can see the data types available in Microsoft Access. This can also be seen in a Word document. Click here to view the Word document.

TEXT	Text or combinations of text and numbers, as well as numbers that don’t require calculations, such as telephone numbers. Up to 255 characters in length.
MEMO	Lengthy text or combinations of text and numbers. Up to 65,535 characters in length.
NUMBER	Used for storing numbers that can be used in calculations.
DATE/TIME	Used for storing dates and times
CURRENCY	Used for storing monetary values and numeric data used in calculations, using from 1 to 4 decimal places
AUTO NUMBER	These are numbers allocated by the computer.  The numbers are usually allocated in sequence. When using AutoNumber, the number given will always be unique (it will never be repeated)
YES/NO	Used for storing data such as Yes/No, True/False
OLE OBJECT	An object (such as Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds or other binary data) linked to or embedded in a MS Access table
HYPERLINK	Text or combinations of text and numbers stored as text and used as a hyperlink address.
LOOKUP WIZARD	Creates a field that allows you to choose a value from another table, or from a list of values by using a list box or combo box.

Task 4b (Task 4 - Part1) - Choosing the right data type

For task 4 part 1 you have to create a database for all the cast members. You must include the fields below. For each field you need to decide the correct data type.

• Surname - Data Type?
  
  
• Forename - Data Type?
  
  
• Gender - Data Type?
  
  
• Year - Data Type?
  
  
• Address - Data Type?
  
  
• Postcode - Data Type?
  
  
• Tel_no - Data Type?
  
  
• Part - Data Type?
  
  
• Costume (Hired or Made) - Data type?

Parts should include Joseph, his 11 brothers, his mother and father, Pharaoh, Potiphar and his wife, jailer, baker and butler. (Use the names of pupils in your class and/or any others you wish. Decide whether or not a costume needs to be hired or made). You play the part of Joseph.

Task 4c - More on data types

There are some important facts to note about data types:

1. A text field will store any combination of text and numbers. This is called 'Alphanumeric'.
   
   
2. When storing a telephone number you must store it as TEXT. This is because all telephone numbers begin with '0' (zero) .If you store a number with a zero at the front as the data type 'number' the zero will be dropped.
   
   Therefore if you want to store the telephone number '02840645677' in a number field you will get '2840645677'. If stored in a field with data type of 'text' then it will remain intact.
   
   
3. As you can see from the field lists above there are a number of fields were a number of 'choices' need to be stored in each field.
   
   e.g. Gender (M/F), Part (See lists above) and Costume (Hired or Made)
   
   For these fields we use the data type of 'Look up Wizard'. A look up wizard will allow you to store choices in a field so that when you are entering data, the value can be chosen from a list.
   
   What are the advantages of this?

The Look up Wizard

In task 2 you will be shown how to set up a 'Look up wizard'. Below is a screen shot of how they work once they have been set up. In this example a look up has been set up for the 'Gender' field, given the value 'M or F'.

The ID field

It is important that each record in the database is unique so that data can be searched for. Which field can be used from the fields given above for a 'unique' field. Surname? Forename? Year?

None are suitable as many people have the same surname and forename, and sometimes people who have the same surname and forename live at the same address. Therefore we need to create a unique identifier for every record. One way of doing this is by including an 'ID' field.

'ID' fields can be given the data type 'Autonumber'. This will means that every record in the database is given a unique number when created. This helps prevent duplication of data. An example of this can be seen below.

Homework

For homework complete the 'Pupils Data Types Exercise...' sheet by adding the fields shown above and choosing the correct data type.

The 'ID' field has already been completed for you.

When completing the homework sheet you also need to give consideration to the fields 'Field size', 'Required?', 'Description'.

Field Size

Databases can contain a large amount of data. One way of reducing the amount of data is by making sure the 'Field size' is just big enough to hold the data. Otherwise the field size is automatically set to '50'. This means it can hold a maximum of 50 characters - either text or numbers.

Think about the field size when you plan out your database table. What is the minimum size required for the:

• Surname field
• Forename field
• Address Field
• Postcode field
• Telephone number field

Required?

The required field serves to make sure that important data must be entered into the database. If you make a field 'required' then data has to be filled in or the software will not allow you to go any further.

Which fields must be required for the cast table?

Description

The description field is needed when you decide to use abbreviations for your field names. For example you could shorten the Surname field name to just 'SN'. You would then include a 'Description' to explain what the abbreviation means.

By using abbreviations you are also making sure that your database does not get too large!

Note: You cannot begin Task 2 if you have not planned out your database first!

Before you start creating your new database table you must have your homework planning completed!

In this task you will:

• Create a 'Cast' table to store all the names of the pupils taking part in the play
• Fill the cast table with names, addresses and telephone numbers etc
• Print out data as required

Task 4d - Creating your table

Now you have a plan for your 'Cast' table you need to create it. At this stage you are setting out a new table in the database - you are not entering data into it! Follow the steps below.

1. Open up your 'Helpers' database
2. Make sure you have clicked on the 'Table' option
3. Click on the 'New' icon
4. Choose 'Design view' and press OK
5. Your new table will now open in design view
6. Using your homework planning, begin to create the table structure.

Using look ups

To create the look ups needed for Gender, Part and Costume follow the instructions below:

1. Enter your field name. This example is for 'Gender'
2. Choose from the 'Data Type' drop down - 'Look up Wizard'. The Look up wizard opens.
3. Choose 'I will type the values I want' and press Next

5. Enter the values under the 'Col1' heading - in this example it will be 'M' for Male and 'F' for Female.
6. Click 'Next' and then 'OK'

7. When you return to your table you will notice that the data type has changed to 'Text'. Don't worry this is normal!
8. If you click on the 'Look up' tab at the bottom of the design view you will now see the values you have entered. If you have entered a wrong value - amend it here rather than run the look up wizard again.

Note: When setting up the 'Parts' look up - you only need to include the part of Brother once as it can be applied to 11 people, all of which will play the part of brother.

Task 4e

Complete the table design making sure you set up the correct data types and look up values including field sizes.

To save the table you must click on the red 'X' for the table only (not Access).

Choose 'Save' and enter the table name as 'Cast'. (See below)

You will be asked if you want to set up a 'Primary key'. Choose 'Yes'.

 

Open up the new Cast table by double clicking it - Start to enter the data. Remember the following points:

• Do not type in a number into the ID field. This will be added automatically.
• Remember to use capitols for all data you enter.
• Remember that you must include your own name and that you must be given the part of Joseph.
• You will need at least 20 names and addresses, all of which have been given a part, to complete this part of the task
• Once a database table has been set up you do not need to save it. When you close down Access the table will be saved automatically.

Task 4f (Part 1(2))

Once you have completed adding all the data Print out the table as an alphabetical list by name.

Task 4g (Part 1(3))

Print a list, with the females first, of those who need to hire a costume.

Task 4h (Part 1(4))

Annotate both lists.

 

 

 

For this task you will use the mail merge facilities of Word to produce a letter of at least three paragraphs for each of the participants informing them of rehearsal times which are Tuesday and Thursday 3.45pm to 5.00pm and telling them of the parts they have been given.

You must print out the standard / skeleton letter

You must print out a personalised letter for the Pharaoh and Potiphar.

Detailed instructions for this task are available by clicking here.

By completing this task you can achieve:

• A level 5 in Information Handling
• A Level 6 in Communication