Contents Contents......................................................................................................................... 1 How to set up a database in Microsoft Access.............................................................. 1 Creating a new database................................................................................................ 3 Enter field names and select data types......................................................................... 4 Format date fields: how do you want fields with date data type displayed?................. 5 Format currency fields: How do you want fields with currency data type displayed? . 6 To set up a primary key................................................................................................. 7 Changing the length of a text field in Microsoft Access............................................... 8 Validation Rules: Pull-down lists (file lookup)............................................................. 9 Validation Rules: Picture (Format) Checks ................................................................ 11 Validation Rules: Presence Checks............................................................................. 12 Validation Rules: Range Checks................................................................................. 13 Validation Rules: Uniqueness Checks ........................................................................ 14 Entering data into a database....................................................................................... 15 How to create a form in Microsoft Access.................................................................. 17 How to set up a database in Microsoft Access Create a New Database ↓ Enter field names and select the data types ↓ Format fields with date/time and currency data types ↓ Set up a primary key ↓ Chose field lengths for all the text fields ↓ Set up validation rules ↓ Enter the data into your database 1 2 Creating a new database Open up Microsoft Access. Go to the File Menu and select New. Then click on Blank Database. Give the database a sensible name, when asked, and then click on Create The database manager window will now appear (shown in the picture on the left). Click on “Create table in Design View” and you will be able to start setting up your database. 3 Enter field names and select data types Put the name of the field in this column and then select the desired data type from the pull down list. The data type text lets you enter any mixture of characters (letters, numbers and special characters like ?!@:;,-) The data type number only allows numeric data. If you go to format you can select integer (whole numbers only) or decimal (sometimes called real numbers – numbers with a decimal point in them) The data type yes/no is what Access calls Boolean fields – there are only two possible values Yes and No. You can also select data types like dates, times and currency (money) if these are needed in your database. 4 Format date fields: how do you want fields with date data type displayed? You can chose how you would like a date to be shown e.g. 1st June 2006, or 01/06/2006 or 01-Jun-06. To do this select the field which has a date/time data type and then go to Format and select your preferred option from the pulldown list. In the example shown above the Date of Birth is being set up to have the Short Date Format. 5 Format currency fields: How do you want fields with currency data type displayed? You can chose how you would like a currency field to be shown e.g. £1000, or $1750 or £1000.00. To do this select the field which has a currency data type and then go to Format and select your preferred option from the pull-down list. In the example shown left the Hourly Wage is being set up to be in £s. You can then chose how many decimal places you want shown (select 2 if you want the pennies shown, 0 if you want to have the value in pounds only) 6 To set up a primary key A primary key is a field that is unique for every record. Select the field that you want to make the primary key and then click on the key icon. If you want to set up a composite primary key (make several fields together be the primary key because there is not a field that is unique on its own) select all the fields that will be part of the composite key and then click on the key icon. Note: to select several fields hold down the ctrl button on the keyboard while you use the mouse to select all the fields. If you do not set up a primary key then Access sometimes creates one for you and calls it ID. 7 Changing the length of a text field in Microsoft Access Select the field and then in field size type in the length (maximum number of characters you want to allow the user to type in to the field). In this example the surname field has had its length (field size) set to 30: 8 Validation Rules: Pull-down lists (file lookup) In this example we are going to look at how to set up a pull-down list. In a database about students we want to make sure that only male and female can be typed into the gender field. Select the gender field and make the data type text. Then change the data type to lookup wizard. Select “I will type in the values I want” and then click on Next. If you make a mistake at any time click on Back to go back to the last screen. 9 Type in the items that will be in the list, as shown in this picture and then click on finish. Then click on the Lookup tab and select Limit to List. Type in Yes. If you do not do this last bit then there will be a pull-down list that the user can select from but they could also type in whatever they like. 10 Validation Rules: Picture (Format) Checks Sometimes the data in a field must have a certain pattern e.g. National Insurance Numbers and Postcodes. In this example we are going to set up a format check on National Insurance. All NI Numbers start with 2 letters then have 6 numbers and finally a letter. Select the NI Number field and then in input mask type: LL 00 00 00 L An L means that something must be a letter, a 0 (zero) that something must be a number. The next screenshot shows a slightly more complex input mask that has been set up for a postcode, where the data entered must match one of four possible formats. It is also possible to use input masks to make sure that the data entered has upper or lower case letters, or characters other than letters & numbers in certain positions (e.g. you could use an input mask to ensure that an e-mail address has an @ symbol in it). 11 Validation Rules: Presence Checks In this example we are going to look at how to set up a presence check. A presence check makes sure that data is entered in a field (it cannot be left blank). In a database about students we want to make sure that every student has a name. Select the name field. Then where it says Required type in: Yes Where is says Allow Zero Length type in: No 12 Validation Rules: Range Checks In this example we are going to look at how to set up a range check. For a field called Mark on Test we want to make sure that only numbers between 0 and 100 are entered. First of all make the data type Number Then where it says Validation Rule type in: >=0 and <=100 (This means must be greater than or equal to 0 and less than or equal to 100). Note: To make a field be greater than 0, you would have typed in >0 where it says Validation Rule. 13 Validation Rules: Uniqueness Checks MS Access automatically makes a field you set-up as a primary key have a uniqueness check. If there are any other fields that you want to set up a uniqueness check for then select the field and then: Change the indexed property to say “Yes (No Duplicates)”. 14 Entering data into a database When you have finished setting up the database click on this icon: You will be asked to save the table, give it a sensible name. Your screen will now look like this: You can now type in the data. When you have finished typing in the data click on the cross to close the table. 15 The database manager will now look like the picture above. Double click on the table name if you want to add more data to your database. If you want to change the setup of the database then select the table name and then click on design. Instead of typing the data into the table directly you could create a form and use the form to enter the data. A form is a more userfriendly way of entering data into a database. 16 How to create a form in Microsoft Access Click on Forms tab on the database window. Select Create Form by Using Wizard. Click on the double arrow to move all the fields from available fields to selected fields. Then click on Next. Select the layout you would like – here justified has been selected – then click next. 17 Then select the style you would like your form to be in – here standard has been selected – and then click on next. Finally, give your form a name and click on finish. The finished form is shown below – you can now use this form to type in more information to your database. The arrows at the bottom of the form can be used to move between records and to add a new record. All the data that you enter using your form will automatically be added to your table. Clicking on this icon (in the top-left corner) will take you to the design view of the form where you can move fields around, change colours, insert pictures etc... Example: to change font colour right click on the field and select properties. 18