
























































Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
Ms access manual with detail reading.
Typology: Lecture notes
1 / 64
This page cannot be seen from the preview
Don't miss anything!
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.
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.
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.
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:
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.
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)
[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.
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:
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.
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
From the Menu Bar Choose “Relationships” from the ‘Tools’ menu
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.
10.The relationship is indicated by the join line between the two tables
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:
Editing Relationships between tables
Deleting Relationships
Modify Existing Relationships
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’ ()
Access allows you to EDIT or even DELETE the contents of a record. To delete or edit
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
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.
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: