This is a database management application program.
Opening Ms Access
Alternatively, run the program by typing ms access in the dialog box or open an existing Ms Access document.
Terminologies
Data – facts, concepts, represented in a formal/standardized manner e.g. can be graphical, digital, analog
Database – any organized collection of related information e.g. students record in an institution, an office fitting system
Normalization – the process of structuring a database to minimize waste/ avoid repetition e.g. by using Ms Access
DBMS – (Database Management System) is a computer based software that manages a database e.g. Ms Access
Database administrator (your name) – a person who manages a database
Structure – the composition of an object in a relational database e.g. rows & columns
Object – has the attribute and properties of data entries, e.g. table, forms, queries, pages, macros, modules and reports
Composites of a relational database
Tables – a file in a database used to store data, also referred to as a relation
Forms – used for entering data into tables
Reports – contains data from one or more tables designed to be printed
Queries – used for selecting records and fields from one or more tables, using a given criteria or condition
Indices – used to speed up access to records in a table
Relational terminologies
Files – refers to table
Fields – columns in a table
Records – rows in a table
Features of a relationship
*Each record in a file contains data about the same object
*Each field contains data about an attribute (property) of the object
*Each field in the file has a unique name
*The order of the fields in the files is irrelevant
*All entries in a field must be of the same type
*No two records in a field can be identical
Tables
Characteristics of a table
Field properties
o Field size – the maximum number of characters you can enter in the field. The largest maximum you can set is 255.
o Format – the display layout for the field select a pre-defined format or enter a custom format
o Input mask – a pattern for all data to be entered in the field
o Caption – the label for the field when used on a form. If you don’t enter a caption, the field name is used as the label
o Default value – a value that is automatically entered in this field for new records
o Validation rule – an expression that limits the value that can be entered in the field
o Validation text – the error message that appears when you enter a value prohibited by the validation rule
o Required – require data entry in this field
o Allow zero length – allow zero length strings in this field
o Indexed – an index speeds up searches and sorting on the field but may show updates. Selecting “Yes” – No duplicates prohibits duplicate values in the field
o Unicode compression – allow Unicode compression for this field
o IMEI mode – which IME mode do you want to set when the focus is moved to the field?
o IMEI sentence mode – which IME sentence mode do you want to set when the focus is moved to the field?
o Smart tags – smart tags to be applied to this field
Data types
a) Text – includes a mixture of letters and numbers
b) Memo – this is for long text entries
c) Number – are value entries e.g. 10, 12, 30
d) Date/time – chronological entries
e) Currency – it is used to present currency values
f) Auto number – for automatic listing ordering
g) Yes/ No – indicates whether the condition is true or false against
h) OLE object (object linking & embedding) – for inserting pictures & graphics in database
i) Hyperlink – connects the current object to other data sources
j) Look up wizard – it picks its data from other objects, fields or database
Query Operates
< > ? Like And Or Between Not = * + -
Wild Cards
? – Represents one missing character B??T,
- Represents any number of missing or unknown characters * S, A * (0, 1) – specifying letters mon (0,1) – Momo, moni – mon (! ), 1) – Mona, mony
Compound Operators
AND – Both conditions
MUST be TRUE
Male and Orphaned
OR – at least ONE condition
Must be TRUE
Making a Query
-Click on Queries tab on the Database window
-Click new command, design view, then click Ok
-On show table window, Add the tables you choose to use and click close
-Run your Query
Running a Query
Way One. On the Menu bar, click on Query then Run
Way Two. On the Query Design view toolbar, click on the Red Exclamation Mark (Run Command)
Parts of a Report
Report header – contains the title of the report and only appears on the first page of your report
Page header – contains the file names (titles) and appears on each and every page of your report
Detail section – contains the attribute of your field names and appear on each and very page as long as there is a field name
Sample File
No.-Name-Gender-Age
1 Andrew-M-36
2 Grace-F-43
3 Hillary-M-26
4 Maurice-M-24
5 Allan-M-36
6 Ann-F-29
7 Beatrice-F-31
Planning the Database
Planning a database will involve answering the following questions using interviews;
Relationships
After you have set up different tables for each subject in your database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships between again. The first step in his process is to define relationships between your tables. After you’ve done that, you can create queries, forms and reports to display information from several tables at once
The kind of relationship that Microsoft access creates depends on how the related fields are defined
Types of relationship
-A One-to-Many relationship is created if only one of the relationship is a primary key or has a unique index
-A One-to-One relationship is created if both of the related fields are primary keys or have unique indexes
-A Many-to-Many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields – the foreign keys from the two other tables
-An Intermediate relationship is created when none of the related fields is a primary key or has a unique index
Queries
A query is a question posed to Access and it is represented in a way that access can understand. Queries are used to view, change and analyze data in different ways. You can also use them as the source of records for forms and reports. The most common type of query is a select query. A select query retrieves data from one or more tables using criteria you specify and then displays it in the order you want. Note that making changes to a query record or deleting a record in a query will affect the same record in he related table.
Query design view
There are two sections in design view
The top section is where the table or query will be displayed from which you are basing your new query. Inside each table or query window, will be listed the field names.
The bottom section is where the field names are moved to set up the criteria or other options to base the query on
Report part continuous
Page footer – contains the date, time, page number and copying information. It appears on each and every page of your report
Report footer – contains notes and comments that appear only on the last page of your report
Macros
Shortcuts
Tables
a) Create table in Design View
b) Create table using Wizard
c) Create table by entering data
Queries
a) Create query in Design View
b) Crate query by using Wizard
Forms
a) Create form in Design View
b) Create form by using Wizard
Report
a) Create report in Design View
b) Create report by using Wizard
Pages
a) Create data access page in Design View
b) Crate data access page by using Wizard
c) Edit web page that already exists
Macros are in built codes tailored to execute access commands e.g. minimizing and maximizing. Macros and Modules can also be created through the same procedure.
In Ms Access, always remember that a primary key defines a relationship between one table to another in the database.