Get to learn ms Access step by step for free. Both theory and practical is covered. Good explanation and on point instructions. Try this tutorial today.
Ms ACCESS THEORY NOTES
This is an application software classified under Database.
It is highly specialized in database management. It also contains programming.
Other examples of databases:
- Fox pro
- Fox base
- Qbase
- Oracle
Microsoft Access Window
It can be divided into two parts.
1. Database Window
2. Application Window
Also Read: Ms PowerPoint Tutorials
a) The application window comprising of the following:-
- Application title
- Menu bar
- Status bar
- Control buttons.
b) Database window containing:-
- Objects, i.e. tables
- Selection tabs
- Command buttons
- Control buttons
- Scroll bars (open objects)
Definitions.
a) RECORD
A record is a complete detail about an entry typically entered in a row. Its made up of
related fields, e.g. name, date of birth, country of origin and color about a particular
person.
E.g.
- A collection of data about
- A person, a place,
- An event,
- Other items.
A record is represented as a row in Database view of a table, query, or form.
b) FIELD
Its an item of data within a record. Its made up of several characters. It contains
related data type. E.g. An element of a table that includes a specific item of
information, such as the last name.
A field is represented by a column or cell in a datasheet on a form, and you can use the control, such as a text box, to display data from a field.
c) OBJECTS
These are programs that control the layout presentation of data, e.g. filters (Query).
e.g. (A table, query, form, report, macro at module: objects that are defined by
the system, or information from another application, such as a chart (graph), a
drawing, or a table, that can be linked or embedded (inscribed in a file).
DESIGNING A DATABASE
DATABASE: – This is all the records and objects put together as a file
Def: A collection of data and artifacts related to a particular topic or purpose. A database can contain Tables, Queries, Forms, Reports.
During the database design, one should consider the following factors including:
- Data Integrity
- Relationship – between fields
- Data/field duplication.
- Object Sizes, e.g. table size.
Steps in designing a database
- List and group your fields
- Decide on the data type
- Give relevant names to the various groups of field.
The following are available fields (data types)
- Text – These are label entries, e.g. “Alan.”
- Number – Are value entries e.g. 10,12,30
- Date/time – Chronological entries
- Memo – This is for long text entries
- Hyperlink – Connects the current object to other data sources.
- Yes/No – it indicates whether the condition is true or false, against.
- OLE object – (Object linking and embedding) this is for inserting object and
other graphics in the database. - Lookup Wizard – it picks its data from other objects, field or databases
- Currency – It is used to present currency values.
- Auto number – for automatic listing/ordering.
CREATING A NEW DATABASE
- Open Microsoft Access
- In the dialog box choose the blank database
- Click OK
- Indicate the name and the place where your new database is to be started
- Click on create
- Click on tables’ selection tab
c) Click on records then paint to sort then click on sort ascending or type descending.
NB: This action should be done on the datasheet view.
Primary keys
This is a unique field, which gives records their identity.
Def: One or more fields whose value or values uniquely identify each record in a table in a relationship, a primary key is used to refer to specific records in one table from another table. A primary key is called a foreign key when it is referred to from another table.
We can insert a primary key using the following procedure: –
- When the table is open, change the design view.
- Position your cursor in the field which will be used as a primary key.
- Click on the primary key in the standard toolbar.
NB: There should be no similar entries in the particular field, i.e. (ID NO)
Alternatively:
You can let the computer insert the Primary Key for you by clicking Yes when the
the program asks you whether you what to insert the primary key now.
Renaming Objects
· Click on the objects name while the database window is open
· Type the new name.
Inserting other fields.
While the table is open, change to design view. Insert the row where you want your
new field to be located.
Type the field name, data type and any other details required
Adjusting Row height/Column width
While the table is open, point to the row of the column head junction until you get a resize pointer.
- Click holds down and drag.
Alternatively: - Use the format menu by· Click on the format
- Click on column width or row heights
- Adjust accordingly
- Click, OK.
Copy/Cut/Paste and Paste Append
The Copy / Cut and paste command work similarly as in Excel and Words.
Procedure.
- Select the record or records
- Click on Edit
- Click on Edit and Paste Append.
Go To Command
This command can be used to move to the First, Last, Next, Previous or new records.
- Click on Edit
- Point to go to & Select the destination then click, e.g. First, Last.
Alternatively:
Use the record scrolling tab at the bottom of the table.
Finding and Replacing
We can use the find command to trace particular entries, e.g. a person name.
- Position the cursor to the field which you want to search
- Click on edit
- Click on find
- Enter the data to search for
- Click on find first
- Click on find next to find any other entries of the same type.
MAKING SELECTION
- Selecting the whole table
- Click on edit
- Click on select all records
SELECTING COLUMNS
Point to the bottom left corner until you get arrow right then click
SELECTING A CELL
Point to the bottom left corner until you get cell pointer then click
QUERIES
These are electronic questionnaires (objects) and programs that can be used to
- Update records
- Select and display particular records (Filtering).
ARRANGE OR SORT RECORDS
Queries are named according to their uses
- Update queries – change data to the required information
- Delete queries- delete selected data
- Append queries- transfer the selected data
The query can be made or designed in various layout e.g. cross tab, design view.
DESIGNING A QUERY (CREATING)
Ensure that your tables have Primary key
- Click on queries tab
- Click on new
- Click on design view
- Add tables by clicking on the desired tables and clicking on add
- Click on close
- Select the fields using the list boxes provided, state criteria
- To view the result, change to datasheet view
SETTING CRITERIA
Decide on which record you want to show which field you want to use as a determining
factor.
Type in the function, which will filter only those records of interest, e.g. you, may need to display the list of people who earn more than Kshs, 20,000.00. In this case, the field to filter by is salary and the function >20,000.00. Other functions criteria are:-
= Equal
=> Equals/ greater than
< Less than
RELATIONSHIPS
This is a way of linking up tables to enable data to be easily shared among tables.
Relationships usually are created to curb the duplication of records, especially when a query is created using more than one table.
A relationship can either be created in:-
- Tools menu
- Relationship command
OR - Query design screen.
Before a relationship is created to ensure that field names are having the same data
type else, no connection can be created, eg. In the sample, the KTDA database following
tables has field names with a similar data type.
PERSONAL POSITION SALARY GRADE
SNO. SNO SNO GCODE
GCODE
The personal table can be linked either position or salary because of the common field
name with the same data type, i.e. S/NO.
There are three types of relationships
i One – One
ii One – Many
iii Many – Many
Steps in setting up relationships.
- Click on the tools menu
- Select relationship command
- In the show table dialog box, click to select table click on ADD button select all records to be linked and close.
- Click, drag, and drop a field from one table to another. Ensure that the link is abided to, i.e. field name with common data types. A join line appears to show that a relationship has been created.
- Close and save the link layout.
Alternatively.
Links/relationship can be created in the query design screen using 3 to 4 above.
To delete a join line.
- Click on join line until it becomes thick line then press delete key on the
keyboard.
FORMS
These are Ms- Access objects used for the following:-
- Displaying records.
- Edit records
- Add new records
We use a form to perform the above operation on a table or query.
When we use a form on a query then it gives the user the possibility of working on more than one table at a time.
Creating a form
- Click on the form selection tab
- Click on new
- Select the view (design, auto form columnar, auto form tabular, etc.
- Select the table or query to use.
- Click, OK.
In case you are using the form wizard, ensure that you,
- Select the field to include
- Select the layout/format, i.e. clouds, dust, etc.
- Give the form name
- Finish.
Some important symbols in the form wizard (Step by step process)
- Picks the highlighted field
- Picks the entire field in the table.
- Removes the selected fields from those, which will be, included in the form.
- Removes the fields.
REPORTS
These are Ms-Access objects.
- Print previewing
- Reorganizing and grouping of data and records
- Calculation (analysis)
CREATING A REPORT
- Click on the report selection tab
- Click on view
- Select the table or query to use
- Click, OK.
SUCCESS IN ACCESS
