Transcription
Access 2007Creating Databases Fundamentals
ContentsDatabase DesignObjectives of database designProcess of database design11Creating a New Database. 3Tables . 4Creating a table in design view4Defining fields4Creating new fields5Modifying table design6The primary key7Indexes8Saving your table9Field properties9Calculated Field Properties (Access 2010 only)13Importing Data . 14Importing data from Excel14Lookup fields . 16Modifying the Data Design of a Table .20Relationships .22Creating relationships23Viewing or editing existing relationships24Referential integrity24Viewing Sub Datasheets26.Page 2 of 29
Database DesignTime spent in designing a database is time very well spent. A well-designed database is the key toefficient management of data. You need to think about what information is needed and how thatinformation is to be used.Objectives of database designThe strategy of database design is to accomplish the following objectives:To organise stored information in a timely, consistent, and economical manner.To eliminate, or minimise, the duplication of database content across the organisation.To provide rapid access to the specific elements of information in the database required by eachuser.To accommodate the possible expansion of the database to adapt to the needs of a growingorganisation, such as the addition of new products and processes.To maintain the integrity of the database so that it contains only validated, auditable information.To prevent access to the database by unauthorised persons.Process of database designPlanning your databaseThis should be done on paper.Determine the purpose of your database. This will include deciding what information needs to bestored and what will need to be retrieved. What ‘questions’ will you need to ask your data?TablesDecide what tables you need in your database:A table should not contain duplicate information and information should not be duplicatedbetween Tables. If information needs to be updated, it should only need updating in one place.This is more efficient and also eliminates the possibility of duplicate entries that contain differentinformation. Do not include any calculated data (data that is the result of an 'expression') as this iseffectively duplicated information.Each table should contain information about one ‘entity’ or subject. This enables you to keepinformation about each subject independently from other subjects.FieldsDetermine the fields you need in the tables:Each field should relate to the subject of the table.Each field should contain a particular type of information about the table's subject.Create fields so that you can store information in its smallest logical parts (e.g., First Name, Initial,last name, rather than just name). How small this part is will depend on how you will want to sort,filter or query your records.Primary keysIdentify which fields have unique values and decide which field(s) will be your primary key(s)Page 1 of 29
Data typesDetermine the appropriate data types for each of your fields (e.g. Text, Currency, Date, etc). Unless youwant to use the default data type (Text), you will need to assign a data type to each of your fields. Alldata in a single field must consist of the same data type.RelationshipsIdentify associations between the tables (when you have more than one Table). You will need to definerelationships between your tables so that Access can bring related information from different Tables backtogether in meaningful ways.Implementing your designThe list below is a suggested step-by-step plan:Create a small database based on your design.Specify relationships between the tablesEnter a few records in each table.Create rough drafts of your forms and reports to see if they contain the data you need.Try running a few queries to see if you get the answers you expect.Check your database for any unnecessary duplication of data.Check your design with anyone else who will be using your database.Decide who will be able to access the database, the tables, and the fields within the tables.Finally, enter your data into your tables. Create any queries, forms, reports, etc. that you need.Ensure all the data in your database is relevant and kept up-to-date.Remember the Data Protection Act.Page 2 of 29
Creating a New DatabaseBefore you can create objects such as tables and forms, you must first create the database file in whichthey will be stored.1.2.On the File tab select New.Click on Blank database.3.In the File Name box, type a name for your database.4.Click on the browse button to the right of the File name box to browse for a location for yourdatabase.5.Click on Create.A new database will be created with a new default Table.6.Click on Design View to start working with this Table.Page 3 of 29
TablesTables are the fundamental objects in a database. Without any tables, no data can be stored. To createa table you need to follow these steps:Create the table objectDefine the fields in the table including their name, data type and descriptionSet the properties for each fieldCreate appropriate indexesSet the primary keySave the tableCreating a table in design view1. On the Create tab, click on Table Design.A new table will appear in design view:The table design viewThere are three main parts tothe design view window.Upper paneUpper paneFor defining fields by name, datatype and description (optional).Field properties (bottomleft)For defining specific propertiesfor a field.Help (bottom right)As you move around thedesign view window, contextspecific help is provided here.FieldpropertiesHelpDefining fieldsBefore creating the fields in a table, the following information about naming, data types and descriptionsshould be considered:Field naming rules and conventionsThe following conventions should be observed when naming fields:Field names are mandatory. They may contain up to 64 characters.Names may include embedded (but not leading) spaces and punctuation except full stops,exclamation marks and square brackets.You cannot assign the same field name to more than one field in the same table and it is goodpractice to use a unique field name for each field in the entire database.It is not good practice to include spaces in field names. Instead, use an underscore ( ) for spacesto improve the readability of field names.Page 4 of 29
Minimizing the length of field names conserves resources and saves typing when you refer to thefield name in macros, etc.Avoid specifying a name for a field that could cause a conflict with a built-in Access function orproperty names (e.g. ‘name’, ‘date’, etc.).Data typesYou must assign a field data type to each field in a table unless you want to use the text data type thatAccess assigns by default. All data in a single field must consist of the same data type.Data TypeDescriptionTextAllows text and numbers to be stored. The default length of this field is 50 and islimited to a maximum of 255 characters.MemoMemo fields are usually used for notes, descriptions, etc and can store up to 2gigabytes of which you can display 65,535 characters in Forms and Reports. Fontformatting can also be applied to text in Memo fields.NumberAllows only numbers to be stored. Used for fields which contain numbers you maywish to use for calculations.Date/TimeStores date and time formats.CurrencyInserts the currency sign and decimal point.AutoNumberAutomatically inserts a sequential or random number. You cannot enter data intothis field – it is automatically filled in as you add records to your table. This datatype can be used to create unique numbers to identify records.Yes/NoIs a logical field used when you only have either a Yes or No or a True or Falsevalue.OLE ObjectStores pictures, charts, etc. Can be up to 1 gigabyte.HyperlinkStores a hyperlink address which is a path to an object, document, Web page, orother destination.AttachmentAllows you to attach specific files to an individual record in a table.CalculatedAllows you to add a calculation based on values from other fields and/or othervalues. It is also possible to use functions.LookupwizardThis is not really a data type as such but starts up a wizard which allows you tocreate a field that looks up data either from a list of values you type in or fromanother table. See page 16 for details on using the Lookup Wizard.DescriptionThis is optional but can be useful for other users of the database. If you enter a description it will bedisplayed in the status bar at the lower left of the Access window when you select the field for data entryor editing in the datasheet view of a table or the form view of a form.Creating new fields1. In the first row of the Field Name column, type the name of your first field, following the namingconventions listed above.2. Press the Tab key to take you to the Data Type field.3. Click on the drop-down arrow that appears in the box to display a list of data types (see below) andselect the appropriate one.4. Press Tab to take you to the Description field. Type in a short message describing the current field.Page 5 of 29
5. Press the Tab key to take you to the next row and back to the Field Name column.6. Continue as above until you have entered all your fields as shown below.Primary keysymbolRow selectorModifying table designIt is possible to modify the design of your table and fields at any time by going to design view and makingthe required changes. However, any changes made after data has been added to the table may causeloss of data. Changes to field names, data types and properties may also impact on other objects which arelinked to or based on the table.Adding and removing fieldsYou can modify the structure of your table by adding and removing fields in design view. Note, however,that using the cut, paste or delete functions will remove the field and any data in that field.Selecting fieldsTo select a field, click on the row selector to the left of the field name. To select multiple fields, click onthe row selector and drag the mouse down the selector symbols to select additional fields. The screencapture below shows a table with two fields selected.Deleting fields1. Select the field or fields to be deleted using the row selector.2. Press the Delete key on the keyboard, select Delete Rows from the Design tab or right-click onthe row selector and choose Delete Rows.Inserting fields1. Click in the field above which you wish to insert a new row.To insert multiple rows, select the required number of rows using the row selector.2. Select Insert Rows from the Design tab or right click and choose Insert Rows.Moving fields1. Select the field or fields to be moved.Page 6 of 29
2. Click on the selected field(s) and drag the mouse to the new location for the field(s). As you drag, abold black line will appear indicating where the fields will be moved to if you release the mousebutton.3. Release the mouse button in the appropriate location.Helpful hint: Don’t use cut and paste to move fields if you have data in the table as this will delete the data.Changing data type1. If your table contains data, make a backup copy of the table before you change data types or fieldsizes.2. Open the table in design view.3. Click the data type column of the field you want to change, click the drop down arrow, and select thenew data type.4. Click the Save button on the Quick Access toolbar.Helpful hint:If the data type conversion would result in lost values, Access displays a message telling you that errorsoccurred during conversion before it actually saves the changes. Click Cancel to cancel the changes. ClickOK to continue and save the changes anyway.In large tables, changing a Data Types might take a long time. If you want to cancel the conversion processat any point while it is running, press Ctrl Break, and then click OK.Changing field sizeIf you convert a large field size setting to a smaller one in a field that already contains data, you mightlose data in the field. For example, if you change the field size setting for a text data type field from 255to 50, data beyond the 50 characters will be discarded.If the data in a number data type field doesn't fit in a new field size setting, fractional numbers may berounded or you might get a null value. For example, if you change from single to integer data type,fractional values will be rounded to the nearest whole number and values greater than 32,767 or lessthan -32,768 will result in null fields.The primary keyA primary key is a single field or combination of fields in a table that uniquely identifies each record inthat table. No two records can have the same value in the primary key field or combination of fields.It is advisable to create the primary key before entering data in a table because Access will preventduplicate values being created in the designated field.It is particularly important to set a primary key when creating relationships between tables as Access usesit to link tables together.Helpful hint: Records are displayed in primary key order by default.Setting a primary key1. Click on the appropriate field name and click the Primary key button on the Design tab.2. The primary key symbol will appear on the button in the row selector box on theappropriate row (see the screen capture on previous page).Setting a multiple field primary keyWith a multiple-field primary Key, the combination of the contents of all fields included in the keymust be unique. For example, for a primary key based on the CourseID and CourseDate fields, the sameCourseID and CourseDate combination cannot appear in more than one record.Page 7 of 29
1. Click and drag across the row selector of the appropriate fields.2. Click on the Primary Key button on the Design tab.IndexesIndexing a field speeds up data access when running queries, sorting, and grouping. If you regularlysort or search on certain fields it is a good idea to create an index for this field. However, indexes canslow down some processing activities such as adding or deleting records as the Indexes for many fieldsneed to be updated while performing these operations. For this reason, indexing should be limited to afew important fields.Indexing can also be used to prevent duplicate entries in a field.The options in the Indexed text box are:No(Default) The field is not indexed.Yes (Duplicates OK) The field is indexed with duplicates allowed.Yes (No Duplicates) The field is indexed with no duplicates allowed.If the primary key for a table is a single field it is automatically set to Yes (No Duplicates).Helpful hint: You can't index Memo and OLE Object fields.Creating a multiple-field indexA multiple-field index can be created when you wish to be able to sort or filter records by more than onefield and where one or more fields may contain the same value and you need an additional field orfields to enable you to distinguish between records.A good example of this might be a two-field index using the first name and surname fields – recordscould then be sorted quickly by last name and then by surname in a multiple sort.1. Click on Indexes on the Design tab.A list of all current indexes (single and multiple field) will be displayed.2. Type a name for the index in the Index Name column of the next blank row. (It isadvisable to choose a name that includes the names of the fields to be indexed or asimilarly descriptive name.3. In the Field Name column of the same row,choose the name of one of the fields youwish to include in the index.4. In the next row, leave the Index Name columnblank and in the Field Name column chooseanother field to include in the index from thedrop-down list.5. Repeat this last step (above) until you haveincluded all the fields you want.The screen capture above shows indexes for a table with the following indexes:Last NameA single-field index on the Dept fieldPrimaryKeyA single-field index on the ID field that was automatically created by setting theprimary key to be the ID field.Full NameA two-field index on the Last Name and First Name fields.Helpful hint:Page 8 of 29
You can use the Indexes window shown above to view and edit all Indexes in a table, both single andmultiple field.Saving your table1. Click the Save buttonon the Quick Access toolbar.2. Type in a name for your table. This can be up to 25 characters long and can include spaces,although these are not advisable. It is a good idea to use the naming convention tbl Tablename,replacing Tablename with the name of the table.Entering data in your tableTo enter data into your table, switch to Datasheet View and enter data as normal.Switching to datasheet viewFrom the Design or Home tab click on the View button.Field propertiesEach field has property settings thatyou can change to alter the way thefield looks and behaves. Fieldproperties are optional and can beentered after a field has beencreated. There are differentproperties available for differentdata types.The properties for a particular fieldare displayed at the bottom left ofthe design view window when youclick in the field and the General tabis selected as shown on the right.Page 9 of 29
Field sizeYou can use the Field Size property to set the maximum size of data that can be stored in a field set totext or number data type.Number field sizeThe default setting for a number field is Long Integer. The Field Size property settings and their valuesare related in the following yteStores numbers from 0 to 255 (no fractions).None1 byteIntegerStores numbers from -32,768 to 32,767 (no fractions).None2 bytesLongInteger(Default) Stores numbers from-2,147,483,648 to 2,147,483,647 (no fractions).None4 bytesSingleStores numbers from -3.402823E38 to 3.402823E38.74 bytesDoubleStores numbers from -1.79769313486232E308 to1.79769313486232E308.158 bytesText field sizeThe default setting for a text field is 50. You can reset the Field Size for anything between 0 and 255.Helpful hint:Use the smallest possible Field Size properties setting for number fields because smaller data sizes can beprocessed faster and require less memory.You can't use Undo to undo changes to a table's design after saving it in Table design view.FormatThe Format options also vary depending on the Data Type set. The format of a field only affects the waythe data is displayed: Access will still store the data in the same way.Formatting dates and numbersClick in the Format box and select an appropriate format from the drop-down list:Page 10 of 29
Formatting textThere is no drop-down list for fields with a data type of Text. However, you can type in formattingoptions.For example:Less than [ ]:converts all text in the field to lowercaseGreater than [ ]:converts all text in the field to uppercaseDecimal placesFor Number or Currency fields, you can specify the number of decimal places allowed for a field between0 and 15.Input masksAn input mask is used to provide some control over what values can be entered. They are primarilyused in Text and Date/Time fields, but can also be used in Number or Currency fields. Input Mask areused when data is entered in a fixed pattern of numbers, letters, spaces etc., such as a NationalInsurance number but is unsuitable where the pattern varies (e.g. postcode or telephone number).When you click in the input mask area, a build buttonappears on the right. Clicking this button willenable the Input Mask wizard, which will help you select an appropriate input mask for your field.Build buttonYou can use the following characters to control data entry:CharacterAllowsRequired/Optional00-9 onlyRequired9Digit or spaceNot required# /-, digit or spaceNot requiredLLetters A-ZRequired?Letters A-ZOptionalALetter or digitRequiredaLetter or digitOptional&Any characer or spaceRequiredCAny characer or spaceOptional All following characters converted to uppercase All following characters converted to uppercase\Displays the following characers as typeFor example: an input mask for an NI Number would be: LL000000 LEntry will be restricted to two letters, followed by six numbers, followed by one letter. The symbolconverts the letters to uppercase.To find out more about how to create input masks, click in the Input Mask area and press F1.Page 11 of 29
CaptionThis is an optional label that can be used to replace the field name in forms and reports. The captionwill also appear as the column heading in datasheet view in place of the field name.Default valueIf you type in a default value, this is automatically entered in the field for each new record. When newrecords are added, the default value can be kept or changed.Helpful hint:You can enter Date() in the Default Value box of a date field to enter the current date automatically whendata is entered.Validation rulesValidation rules will automatically check for any particular words or phrases that are being entered intoa field. If the match is not exact to that specified in the validation rule, then the information will not beaccepted into the field and an error message will appear explaining why the data could not be accepted.When you add words in the validation rule area that you want accepted, separate them with OR. Forexample: Mr OR Mrs OR Ms OR Miss OR Dr.Another example, to force entry of Exeter, Exmouth or ”)Helpful hint:You can use symbols and words such as , , , OR and AND or refer to other field names when creatingvalidation rules. Validation rules can also be set for tables and records, as well as fields.Example to restrict values to lower than 500: 500Example to restrict values to between 1 and 10 inclusive:Between 1 and 10Or the following would also work: 1 and 10Validation textThis property is only relevant if you have set a validation rule. In the Validation Text area, type in themessage you want to appear for the user if the validation rule is broken.RequiredThis can be set to Yes or No to specify whether or not the field is compulsory (i.e. whether a value mustbe entered in the field).Text Align (For Memo fields - only in Access 2007)This determines how data is aligned horizontally in a cell. By default this is set to General which willalign text on the left and dates and numbers on the right. Changing the Text Align value will affect thealignment of text in all Objects based on the Table (Forms, Reports etc.).Text FormatThis is only available in Memo fields and allows you to choose whether you store data as Plain Textor Rich Text. The Rich Text option allows you to format text using bold, underline etc. Text isstored as HTML.Page 12 of 29
Calculated Field Properties (Access 2010 only)ExpressionThis is where you enter the expression which determines the value in this field. This can include valuesfrom one or more fields, operators, functions etc. For example you could create an expression thatworks out the length of time someone has worked for a company by calculating the difference betweenthe date they were employed and today’s date.Click on the Build buttonto create the expression using the Expression Builder.Result TypeThis is only available for Calculated fields and this is where you choose the data type for the value inthe calculation field. Additional field properties will be available for the Calculated field depending onwhich data type is chosen.Page 13 of 29
Importing DataIf you have existing data in an acceptable form, you can import it into Access using the Import wizard.This might be a text file, a spreadsheet or another database. For example, data can be imported froman Excel spreadsheet to create a new table in your database or to append data to an existing table – thecolumns become the fields and the rows become records in the new table.Importing data from ExcelPreparing to import from ExcelThe spreadsheet data should be organised in the following way:The first row of the worksheet should contain the column headings that will become your field names.You should therefore ensure that these observe Access field naming conventions. See page 4 for moreinformation.There should be no blank rows in the dataset, particularly between the column headings and thefirst row of data.There should be no other data on the worksheet apart from the dataset.To append records to an existing table, the field names and data types must match the fields in thetable to which you wish to append the record. Appending records is not covered in this workbook.Importing data to create a new table1. Go to the External Data tab.2. Click on the Import Excel spreadsheet button3. Use the Browse button to locate the folder that contains the file with the data you want to import.The option to Import the source data into a new table in the current database is selected bydefault.4. Click OK.5. The wizard will automatically select the first worksheet to import. If necessary, select a differentworksheet:6. Click Next.Page 14 of 29
7. If not already ticked, tick the First Row Contains Column Headings to enable Access to set upthe field names for your table and click Next.8. You now have the option to add indexes to fields, change data types or to choose not to import aspecific field by selecting each field in turn and changing the Field Options above. However, thesecan be changed once the table has been created.9. Click Next.10. You can now allow Access to add a primary key field with an AutoNumber data type or to use anexisting field. Whether you have a suitable field will depend on the specific data you are using.11. Click Next.12. The final step is to name your table. Access will offer the name of the Excel worksheet by default,but this can be changed if you wish.13. Click Finish. The table will now be created.Helpful hint:On the last screen there is an option to Save Import Steps. This allows you to save the process so that itcan be repeated in future. So for example, you could repeat the above process when new wards are created.Click on Saved Imports on the External Data tab to access any previously saved processes.Page 15 of 29
Lookup fieldsA lookup field is a field which ‘looks up’ its values from a particular source. This might be a set of valuesfrom a field in another table or query (a record source) or a set of values which you type in (a value list).When entering data in datasheet view, the values appear in a drop-down field known as a combo box.Creating a lookup field from a record source in another table automatically creates a relationshipbetween the two tables in a similar way to creating relationships between tables in the RelationshipWindow. However, no referential integrity rules will be applied.Advantages of using a lookupLookups make data-entry easier and reduce the chances of errors caused by mistyping. They can alsobe used to restrict the data that can be entered in a field by limiting data-entry to the values on thedrop-down list. Using a table or query as a lookup is particularly useful, because when you change thevalues in the table upon which the Lookup is based, the values in the drop-down list in the lookup fieldare also updated.Lookup tablesA lookup table is a table which is created with the specific purpose of being used to create values for adrop-down list in another table. A lookup table will have a One-to-Many relationship with the relatedtable. For example, a lookup table might contain a list of wards which will appear as a drop-down listin the patients table.A lookup table may also contain additional fields with further information about the records. Forexample, the ward lookup table mentioned above might also contain information about number ofbeds, location etc. of each ward.More than one field can be added to the drop-down list that displays for the user in the Datasheet viewof the table. However, only the value in the linked field from the lookup table will actually be stored inthe related table (e.g. the field containing the ward name). This field should be unique and will usuallybe the primary key field in the lookup table.Using the lookup wizardThe easiest way to create a lookup field is to use the Lookup wizard. This appears as a Data Type optionin Table Design view but isn’t a data type as such. Choosing this option invokes the lookup wizardwhich guides you through the process ofcreating your Lookup field.In the Data Type column for the relevantfield, select Lookup Wizard. The first pageof the Lookup wizard will appear as shown.Creating your own value list1. Select I will type in the values that I wantand click Next.2. Enter the number of columns you want inthe list in the Number of columns box.3. Click in the first row of the Col 1 columnand type in your first value.4. Press the Tab key to move down to the nextrow and type in your next value.5. Continue until all your values have been entered and click the Next button.Page 16 of 29
6. Ensure the correct field name is displayed for your lookup column and then click Finish.The lookup field will be created as shown in Datasheet view on the right.Helpful hint: The Data Type for the field is set to Text by default.Creating a lookup based on another table1. Invoke the Lookup Wizard as describedpreviously.2. Select I want the lookup column tolook up the values in a table or queryand click Next.3. Select the table you
Calculated Field Properties (Access 2010 only) 13 . The list below is a suggested step-by-step plan: Create a small database based on your design. Specify relationships between the tables Enter a few records in each table. Create rough drafts of your fo
