Download Ms access manual book and more Lecture notes Computer Science in PDF only on Docsity!
Microsoft Access Tutorial
- Lesson 1: General Introduction
- Introduction
- Database Terminology
- Starting Ms Access
- Designing a Database
- Creating a New Database
- Exiting Ms Access
- Lesson 2: Databases and Tables
- Opening an Existing Database
- Creating Tables
- Creating relationships between tables
- Adding Records to a Table
- Deleting Data in a Record
- Changing the width of a column/Moving a column
- Finding Records
- Filtering and Sorting Records
- Sorting
- Exercise
- Lesson 3: Queries
- Introduction to Queries
- Creating and Saving Queries
- Complex Queries
- Creating Complex Queries
- Print the Resulting Data in a Query
- Query Types
- Creating Total Queries
- Exercise
- Lesson 4: Forms
- Features of a Form
- Creating Forms Using the Form Wizard
- Creating Forms Using Auto form
- Creating a Form Using Design View
- Opening a Data Entry Form
- Customising a Form
- Object Linking and Embedding (Ole)
- Creating Unbound Controls
- Calculated Unbound Controls
- Data Protection
- Editing Data in a Form
- Exercise
- Lesson 5: Reports
- Creating Reports Using a Wizard
- Creating Reports Using Design View
- Sections of the Report
- Previewing and Printing Reports
- Reports Based In Multiple Tables
- Report Printing
- Exercise
- Ms Access Project
Index
An index speeds up queries on the indexed fields as well as sorting and grouping operations. For example, if you search for specific employee names in a LastName field, you can create an index for this field to speed up the search for a specific name.
Internally, an index is implemented as a look up list much like the Index you use to find information about a particular item in a book.
Primary Key
A field created in a table to UNIQUELY identify records. For example the NationalID number field has a unique value for every employee and therefore can be used as a primary key.
Primary keys are needed to aid in searching for records. This is because a field such as the Employees Name is likely to contain similar names for different people. This means that it is more prudent to invent a series of codes (Usually Numerical) to identify the employees uniquely.
1.01 Starting MS Access
Start windows and then click Microsoft Access icon to run the application or Select the program from the Program listing in the Start menu as shown below
Designing a database
A database is a collection of related data.
- Determine the purpose of the database. This helps in deciding the facts to be stored. Determine the tables. Divide the information into separate subjects, such as employees or orders. Each subject will be a table in the database.
- Determine the fields. Decide what information to store in each table. Each field is displayed as a column in the table.
NOTE
When you create a Microsoft Access database, you create one file that contains the data and table structures as well as the queries, forms, reports, macros and modules.
Lesson 2: Databases and Tables
2.01 Opening an Existing Database
The part marked ‘A’ (See Picture Above) shows a list of existing databases that you opened in the recent past. This list will be different for different system users (If you are using Windows XP or 2000). By clicking on either of the names in the list, you will be able to open the chosen database.
To open a database that is not listed, you may choose ‘Open’ from the ‘File’ menu i.e.
You may also use the ‘Open’ Icon ()
Either way you will get the ‘Open’ dialog box from where you must choose the source of the
Numeric data to be used for mathematical calculations, except calculations involving money (use Currency type). Set the Field Size property to define the specific Number type.
Date/Time Stores Dates and times
Currency Currency values. Use the Currency data type to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right.
AutoNumber Unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added.
Yes/No Fields that will contain only one of two values, such as Yes/No, True/False, On/Off.
OLE Object Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data), created in other programs using the OLE protocol, that can be linked to or embedded in a Microsoft Access table. You must use a bound object frame in a form or report to display the OLE object.
Field properties
Depending on the nature of the contents of your field, a certain level of control can be achieved such that a field will accept what it has been programmed to have. The following options are offered:
FIELD SIZE
Allows entry of field size for Text data type For example, if the standard length of names is 25 characters, you may use this property to limit the number of characters entered into such a field to 25. In this case, if there are existing names longer than 25 characters then they will be truncated to 25.
For numeric data types, you choose the field size by selecting from a drop-down list. Field size does not apply to the Date/Time, Yes/No, Currency, Memo or OLE object data type.
FORMAT
Allows selection of a predefined format in which to display the values in the field from the drop-down combo list applicable to the data type that you chose (Except Text). One can also customise a form of presentation of data. E.g. to have a code appearing before every telephone number you wish to enter, select the relevant field and set the format: “02-”000000 (This gives the code for Nairobi)
INDEXED
[Yes (Duplicates OK)] - Gives sorted indexed field and can allow data duplicates. [Yes (No duplicates)] - Gives sorted, indexed but cannot allow data duplicates. It’s not available for Memo or OLE data types.
NEW VALUES
Applies only to AutoNumber fields. Access can increment the Autonumber field by one for each new record, or fill in the field with a randomly generated number, depending on the new values property setting that you choose.
NB For more details on fields and Field properties, press F1 to access help.
To switch between ‘design’ and ‘datasheet’ views:
- Move to the ‘View’ option
- Choose the appropriate view i.e. Design or Datasheet
Example of Table (Design View)
In this view you can make changes to the database design e.g. adding new fields, changing field types and or/ attributes Once you finish designing the table, click on the save icon (), the following dialog comes up
Type an appropriate name e.g. ‘Customers’ then click on the ‘Ok’ Button ensure that the new name is not given to an existing table. You will be prompted to define a primary key IF you have not already defined one i.e
.1 If you answer ‘Yes’ the table will be saved but Access will add a new field named ‘ID’,‘ID1’…. Of type ‘AutoNumber’ and make it to be the primary key. .2 If you answer ‘No’ the table will be saved with no primary key defined i.e. ‘As it is’ .3 If you answer ‘cancel’ the new table will not be saved.
Example of Table (Datasheet View)
In this view you can add new records, delete edit or existing records
)a Moves to the FIRST record.
)b Moves to the PREVIOUS record. )c Moves to the NEXT record. )d Moves to the LAST record. )e Creates a NEW record.
2.03 Creating relationships between tables
- (^) A relationship determines how to link information from many tables so that Microsoft Access can determine knows how to relate the information. A join compares the values of a field in one table with the values in the joined field of the other table. The result is a link of the records in both tables wherever the values of the joined fields equal.
- When it finds matches it combines those two records and displays them as one record in the query’s results. If one table does not have a matching record in the other table, neither record appears in the query’s results.
NB
- The fields used to join two tables must be of the same data.
- The joins created during the query apply only for that query.
- In some instances, Access automatically creates joins e.g. If you add two tables to a query and the tables each have a field with the same name and data type and a relationship has already been created between the tables;
- If one of the join fields is a primary key. If Members No. in the Members table was a primary key, then a join would have automatically been created between the members and have automatically been created between the Members and Rental tables. Primary keys are usually shown in boldface.
After setting up tables to store related information, you need a way of linking the details in the different tables. Those tables should be already normalized.
Procedure
- You cannot create or modify relationships between open tables, Close any open tables.
- From the Toolbar click on the relationships icon
OR
From the Menu Bar Choose “Relationships” from the ‘Tools’ menu
- Many to Many One record in either Table A or Table B can relate to many records in the other table. For example, suppliers can supply many products, and the products can be supplied by many suppliers.
NB: If the relationship exists between two linked tables, the relationship is termed as External
Microsoft Access checks the data available in the tables and suggests the type of relationship that would be suitable.
- To change the relationship created without exiting, click Create New.
- Enter the details of the relationship as shown above then Click OK
- Otherwise click on Create.
10.The relationship is indicated by the join line between the two tables
- Save and close the relationships so that next time the database is opened the relationship will not have to be created afresh.
Referential Integrity Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid and that you don't accidentally delete or change related data. Once referential integrity is enforced then:
- (^) You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.
- You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete a category record from the categories table if there are products assigned to the category in the products table.
- You can't change a primary key value in the primary table, if that record has related records. For example, you can't change the CategoryID in the categories table if there are Products assigned to that Category in the Products table.
Editing Relationships between tables
Deleting Relationships
- Click on the Relationship line for the relationship you want to delete (The line will turn bold when it is selected).
- Press the Delete key on the keyboard.
- Confirm that you want to delete the relationship.
- Click Yes
Modify Existing Relationships
- Double-click the relationship line for the relationship you want to edit. Set the new relationship options.
2.04 Adding Records
You can add records in a table only when you are in the Datasheet View. It is not possible to add data in an ‘AutoNumber’ field. Move to the blank record showing on your table and enter your data, as shown below
You may find it easier to add a record after clicking the new ‘button’ ()
1.05 Deleting Data in a Record.
Access allows you to EDIT or even DELETE the contents of a record. To delete or edit
- Highlight the text (e.g Maithya as listed above)
- Press the Delete key OR Type the new text you would like to replace with NB. If you delete or Edit by mistake you may use the UNDO command from the Edit menu or the undo icon (), However a full RECORD deletion cannot be reversed.
2.07 Finding Records
You may sometimes need to find records in your table, for example you may require to change the salary of one of your employees say “Suyama” To Find a record: )f Open the table in ‘datasheet’ view )g Move to the field on which you would like to perform the search e.g. ‘Last Name’ )h From the ‘Edit’ menu choose ‘Find’ )i The following Dialog Comes up
)j Type the name that you want to search in the ‘Find What’ list box )k Click on the ‘Find Next’ button
)l If this is not the record you are looking for, click on ‘Find Next’
Find dialog box Options: a) Match Click on the drop-down arrow to display a list of search locations
b) Any Part Of The Field Searches for any occurrence of the text string. For Example, searching for ‘Smith’ finds ‘ SmithSonia’ and ‘ JohnSmith’ c) Whole Field Recognise a match only when the text string matches the complete contents of the field. d) Start of Field Searches for the text string at the beginning of a field for example searching for ‘Smith’ finds ‘ SmithSonia’ not ‘ JohnSmith’
e) Search Click the drop-down arrow to display a list of search directions. f) All Searches through all records in the database g) Up/Down Searches either downwards or upwards from the current position
2.08 Deleting records
Occasionally, you will need to delete unwanted records from your file eg those of customers who are no longer shopping with us.
a) Open the table in Datasheet View b) Highlight the record to be deleted as shown below
c) Press the ‘Delete’ key d) When you are prompted as shown below, choose ‘Yes’
e) The selected record will be deleted permanently.
2.09 Filtering and Sorting Records
Filtering
Filtering allows you to view a set of records depending on some criteria that you may set
Steps: .4 Select “Filter” From the “Records” menu then select “Advanced Filter/Sort”. The filter window appears as shown below:
