Relationship

Relationship is an association established between two tables or multiple tables using a common field. In Ms-Access,
you can store data in multiple tables and then use relationships to join the tables.

Types of Relationships
There are three types of relationships

 One to One
 One to Many
 Many to Many

In Ms- Access 2007, there are two valid types of relationships.

 One to One
 One to Many

One to One Relationships: -

In a One To One relationships, each record in table ‘A’ can have only one matching record in table ‘B’ and each
record in table ‘B’ can have only one matching record in table ‘A’. Remember that the field of table ‘A’ & ‘B’ must be
defined Primary Key.

Or

In this type of relationships, one record of table ‘A’ must have in table ‘B’ and one record of table ‘B’ must have
in table ‘A’. Remember that the field of table ‘A’ & ‘B’ must be defined Primary Key.

One to Many Relationships: -

A One to Many relationships is the most common type of relationship. Relationship between Primary Key field
to Foreign Key field.

In a One to Many Relationship, a Record in table ‘A’ can have many matching records in table ‘B’, but table ‘B’
has only one matching record in table ‘A’.

Primary Key:
A primary key is a field or combination of fields that uniquely identify each record in a table. A Primary key can not allow
null value.

How to include or Insert a Primary Key in a Table?

At first open your table → than click on view → click on design view → then right click over the field → click on Primary
Key option.

Foreign Key:
A foreign key is a value in one table that must match the Primary Key. You use primary keys and foreign keys to join
tables together. In other words, you use Primary keys and foreign keys to create relationships.

How to Create Relationship?

At first click on ‘Database Tools’ menu → Click on ‘Add’ option → then drag the primary Key field to Foreign key field →
click on Create Option → Then click on Save and Restart Your Application.
Page No
 Query
You can use a Query to select records from one or more tables in a database so they can be easily View, analyse and
store data in your common datasheet. If you want to view a list of student name and Email address, but you do not
want to see address and other data, so you can create a query that displays the student name and Email address only.
Remember that, a query can be based on Tables.

Types of Query
There are four types of query:-

 Select Query
 Crosstab Query
 Action Query
 Union Query
 Data – Definition Query

How to create query in design view?

At first click on create menu → then click on Query Design option → click on Add option and add one by one tables →
then click on Close option → Double click over the selected fields → click on Save option and type your Query name
then click on Ok Option → then click on Design menu → at last click on Run option.

How to create query by query Wizard?

At first click on create menu → then click on Query wizard option → click on Simple query wizard option → then click on
Ok option → select your Table in ‘Table/Query’ drop down list → then select your filed → click on grater then sign →
repeat the process to add other fields → then click on Next option → click on Next option → type your query name → at
last click on Finish option.

How to sorting any field?

At first open your query → then click on Home Menu → click on View option → then select Design View option → select
sort box then click on Down arrow → select sorting type (ascending / Descending) → than click on Design Menu → click
on Run Option.

How to Hide a Field (Column)?

At first open your query → then click on Home Menu → click on View option → then select Design View option → select
show box in your selected fields → then uncheck it → than click on Design Menu → click on Run Option.

How to Unhide a Field (Column)?

At first open your query → then click on Home Menu → click on View option → then select Design View option → select
show box in your selected fields → then check it → than click on Design Menu → click on Run Option.

Page No
 Criteria
How to Set Criteria?

At first open your query → then click on Home Menu → click on View option → then select Design View option → type
=[message] in criteria box → than click on Design Menu → click on Run Option → Enter parameter Value Then click on
Ok Option.

How to use single or multiple criteria in a Query? Or Query Using single or

multiple criteria:-
You have learnt about how to specify criteria for a query. To specify several criteria for just one field, you have to use
AND & OR operators.

For example, to select company names that start “THE” and include the word “Group of companies”, enter the following
criteria in the (Company Name) field: Like “The*” And Like “*Group of companies*”.

For example, to see either of two groups of employees, you could enter “Accts” Or “Admin” in the Department field.

How to use Text, Number, and Date as Criteria? Or Using Text, Number and Date as criteria:
USING TEXT AS CRITERIA
Expression Ms – Access finds all the records where the query field contains.

Like “Dis??world” “Dis” as the first two characters and “World” as the last four characters. Third and
fourth character can be any valid character.
Like “C*ter” The first character “c” and “ter” are the last three characters.

Like “*ne*” “ne” at any portion of the entry (Text).

In (“Debraj”,”Punam”) “Debraj” or “Punam”

USING NUMBER AS CRITERIA

Expression Ms – Access finds all the records where the query field contains.
>= 5000 And <= 10000 The value between 5000 and 10000.
Between 100 and 150 The value between 100 and 150.
<>2500 The value is not equal to 2500

USING DATE AS CRITERIA

Expression Ms – Access finds all the records where the query field contains.
>=#10/05/2011# The date value on or after 10th may 2011.

Between #10/05/2011# and The date value between 10th may 2011 and 10th may 2012.
10/05/2012#

Page No
 Form
Forms give you the ability to choose the format and arrangement of fields. Forms are used as an alternative way to
enter data into a database Table.

 How to create a form by Form Wizard?

At first click on Create menu → than click on More Forms Option → select ‘From Wizard’ option → select your Table or
Query in ‘Table/Query’ drop down list → then select your filed → click on grater then sign → repeat the process to add
other fields → then click on Next Button → select Form Layout like Columnar, Tabular, Datasheet, Justified → than select
form Style → click on Next Button → type your Form Title then Click on Finish Button.

 How to create a form in Design View?

At first click on Create menu → click on Form Design Option → a Form Design window will be appear then click on
Design Menu → select Label (form Controls) then drag over the Form(and drop method)→ type your Form Heading
than click on Outside → Select Add Existing Fields (form Tools) → select Fields one by one from ‘Fields List’ Task pane →
then choose your fields Position and click on Outside → click on Arrange Menu → Click on ‘Auto Format’ option →
Choose Auto Format Style then click over it.

 How to insert Form Background (colour) in Design View?

At first open your form → then click on view arrow drop down list → select Design View → then right click over the
blank space → then select ‘Fill/back Color’ option → choose your form background colour → click on view arrow drop
down list → select ‘Form View’ Option.

 How to Change Form Background (colour) in Design View?

At first open your form → then click on view arrow drop down list → select Design View → then right click over the
blank space → then select ‘Fill/back Color’ option → choose your form background colour → click on view arrow drop
down list → select ‘Form View’ Option.

 How to create a Sub Form (Create a form & Sub form at Once)?
At first click on Create menu → than click on More Forms Option → select ‘From Wizard’ option → select your Table
‘Table/Query’ drop down list → then click on Next Button → select “Form with Subform(s)” option → then click on Next
Button →select Form Layout (Tabular, Datasheet) → than select form Style → click on Next Button → type your ‘Form
Title’ And ‘Form Sub Title’ then Click on Finish Button.

 How to create Linked Forms?

At first click on Create menu → than click on More Forms Option → select ‘Form Wizard’ option → select your Table
‘Table/Query’ drop down list → then click on Next Button → select “Linked Forms” Option → then click on Next Button
→select Form Layout (Tabular, Datasheet) → than select form Style → click on Next Button → type your ‘Form Title’ And
‘Second Form Title’ then Click on Finish Button.

 How to insert a Picture Background?

At first open your form → then click on view arrow drop down list → select Design View → then Click on Property Sheet
→ Select form from selection type drop down list → select picture Option then click over it → choose your Picture →at
last click on Ok.

Page No
 How to Make a Form Read Only Mode?
At first open your form → then click on view arrow drop down list → select Design View → then Click on Property Sheet
→Select ‘Data’ Tab → Change the Recordset Type Property to Snapshot → then Change the Record Locks Property to
All Record and setting Different Allow Option to Yes/No according to your Requirement → At last Click on Save Button.

Report
Report will organize or summarize the information in a table or query and provide a way to print the data in data
base.

 How to create a Report by Report Wizard?

At first click on Create menu → than click on Report Wizard →→ select your Table or Query in ‘Table/Query’ drop down
list → then select your filed → click on grater then sign → repeat the process to add other fields → then click on Next
Button → Click on Next Button → select the fields from the list that the record should be Grouped by and click the grater
then ‘>’ button to add those fields to the diagram→ then use the Priority button to change the of the grouped fields →
Click on Next to move to the next screen → if the Record should be Sorted ,a sort order here . Select the field → than
click on Next button → select a Layout and Page Orientation for the report → than click on Next Button→ then Select a
Colour and Graphics Style for the report → Click on Next Button → type your Report Title Name for the Report → at last
click on Finish Button.

 How to create a Report in Design View?

At first click on Create menu → click on ‘Report Design’ Option → a Report Design window will be appear then click on
Design Menu → select ‘Label’ (from Controls) then drag over the Report (and drop method)→ type your From Heading
or Title than click on Outside → Select ‘Add Existing Fields’ (from Tools) → select Fields one by one from ‘Fields List’ Task
pane → then choose your fields Position and click on Outside → click on Arrange Menu → Click on ‘Auto Format’ option
→ Choose Auto Format Style then click over it.

 How to Change Report Background (colour) in Design View?

At first open your Report → then click on view arrow drop down list → select Design View → then right click over the
blank space → then select ‘Fill/back Color’ option → choose your Report background colour → click on view arrow drop
down list → select ‘Report View’ Option.

 How to insert a Picture Background?

At first open your Report → then click on ‘view’ arrow drop down list → select ‘Design View ‘→ then Click on Property
Sheet → Select ‘Report’ from selection type drop down list → select ‘Picture’ Option then click over it → choose your
Picture →at last click on Ok.

 How to change Picture Alignment?

At first open your Report → then click on ‘view’ arrow drop down list → select ‘Design View ‘→ then Click on ‘Property
Sheet’ Option → Select ‘Report’ from selection type drop down list → select ‘Picture Alignment’ Option → than choose
Alignment Type like Top Left, Top Right, Center, Bottom Left, Bottom Right → click over it → at last click on ‘Save’
Button.

 How to Edit Picture Size Mode?

At first open your Report → then click on ‘view’ arrow drop down list → select ‘Design View ‘→ then Click on ‘Property
Sheet’ Option → Select ‘Report’ from selection type drop down list → select ‘Picture Size Mode’ option → than choose
Picture Size Mode like Clip, Stretch, Zoom, Stretch horizontal, Stretch vertical → click over it → at last click on ‘Save’
Button.
Page No
 How to Set Auto Format?
At first open your Report → then click on ‘view’ arrow drop down list → select ‘Design View ‘→ than click on ‘Arrange’
Menu → select ‘Auto Format’ option → than choose Auto Format Style → Click over it → than click on ‘Home Menu’ →
select ‘view’ arrow drop down list → select ‘Design View’ → At last Click on Save Button.

Switchboard Manager
Switchboard is a special type of Form where you can integrate different option for accessing forms and Reports as a
collection of Buttons.

 How to create a switchboard?

At first click on ‘DataBase Tools’ Menu → then select ‘Switchboard Manager’ Option → (You will notice that a Message
box will appear, asking for the confirmation of creating a New Switchboard.)→Click on ‘Yes ‘→ now, a switchboard
Manager window will appear on your screen (Here you will Notice that Main Switchboard is already declared as
Default.) → click on ‘New’ Button to create a new Switchboard Page → than type your Switchboard Page Name → Click
on ‘Ok’ Button → click on ‘Make Default’ Button (to declared as default) → than click on ‘Edit’ Button → than click on
‘New’ Button → Edit Switchboard Item dialog box will appear, Type your Text [ where you have to define Text (Text that
is to be Displayed on the Switchboard as Item)]→Select ‘Command’ from ‘Command Drop Down List’ [Command
(Action to be taken on to the Subject)] →than select your Object from ‘Form’/’Switchboard’ Drop Down List (Object on
which the Action is to be Taken) → Click on ‘Ok’ Button → Repeat the above Process to add other Switchboard Items
→Click on ‘Close’ Button → than click on ‘Close’ Button.

Protecting Database Using Password

To protect a database from unauthorized access you have to assign Password for the Database.

 How to assign Password for the database?

At first open the database in Exclusive mode → than click on Database Tools Menu/Tab → click on ‘Encrypt with
Password’ Option. The Set Database Password dialog box appears. →Type your Password in the ‘Password box’ → Re-
enter your Password in the Verify box → then click OK.

 How to Open the Data Base in Exclusive Mode?

Click the Microsoft Office Button , and then click Open → In the Open dialog box, browse to the file that you
want to open, and then select (single-click) the file. → Click the arrow next to the Open button, and then click
Open Exclusive. The following figure depicts the menu.

Page No
 How to Print Relationship Report?
At first click on Database Tools/Menu → then click on Relationship →click on ‘Relationship Report’ →Than click on
‘Print’ Option → at last click on ‘Ok’ button.

Page No