MS ACCESS
What is database?
A database is a collection of information that's related to a particular subject or
purpose, such as tracking customer orders or maintaining a music collection. If your
database isn't stored on a computer, or only parts of it are, you may be tracking
information from a variety of sources that you have to coordinate and organize
yourself.
What is MS Access?
MS-Access is a RDBMS (Relational Database Management System) under Windows.
It is used to store and manipulate large amounts of data. The data is stored in
multiple tables and Access allows us to set up relationships between the tables to
facilitate data retrieval. MS-Access is an Object Oriented Program i.e. everything in
MS-Access is an object. Each Object has properties that define how it appears and
what work it performs. MS-Access has a number of components out of which the
most important component is the Table object. Tables are objects that hold the
data and all related objects such as Queries, Forms, and Reports that are used to
manage this data in tables.
RDBMS : Relational database management system. Relational database A type of
database that stores information in tables. Uses matching values from two tables to
relate data in one table to data in the other table. In a relational database, you
typically store a specific type of data just once.
Objects OF A DATABASE
The important database objects are Data, field, records, Tables, Queries, Forms,
Reports, Macros and Modules.
Data: A data is a information which fill the blank area of data field.
Record: A collection of data about a person, a place, an event, or some other item.
Records are the logical equivalents of rows in a table.Field :An element of a table
that contains a specific item of information, such as a last name.
Tables
A table is a collection of data about a specific topic, such as products, students or
suppliers. Using a separate table for each topic means you store that data only
once, which makes your database more efficient and reduces data-entry errors.
Tables organize data into columns [called fields] and rows [called records]. MS-
Access has a table datasheet view, wherein you can add, edit, or view the data in a
table. You can also check the spelling and print your table’s data, filter or sort
records, change the datasheet’s appearance, or change the table’s structure by
adding or deleting columns. In the MS-Access, table Design view, you can create an
entire table from scratch, or add, delete, or customize an existing table's fields.
Queries
You use queries to ask questions about your data. Queries are also used to view,
change, and analyze this data in different ways. Queries can be used 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. You can create a query with a wizard or
from scratch in query design view. In design view, you specify the data you want to
work with by adding the tables or queries that contain the data, and then by filling
in the design grid. You can formulate simple queries that look for records in a single
table, or design complex queries that involve multiple tables and criteria.
1 City Corporation Computer Institute
MS ACCESS
Forms
When a table is opened, it gets presented in a datasheet view. In datasheet view.
In datasheet view data is presented in rows and columns. In most cases it is better
to use the form to enter or modify data. Developing a database form from scratch
is a tedious task. MS-Access has a form wizard with which a form can be created
after answering a number of a question about what you want to see. Columnar auto
form creates a form with one record per page and field arranged in columns.
Reports
Reports are another type of object used in MS-Access to view and to print data.
Reports can present data from a single table as well as more than one table.
Differences between Forms and Reports: Reports are similar to forms and in fact
the report design window shares many of the features of the form design window.
There is however some important differences between these two types of objects.
One difference is that forms are primarily used to edit or view data on your
computer's screen. When you move through a form, you usually navigate from one
record to another, Reports on the other hand can be previewed on screen as well
but their main purpose is to present information on a neatly printed page. Another
difference between forms and reports is that reports have special features to help
you summarize data. It is not possible to view this kind of summary information
using a form.
Macros
A macro executes one or more database commands automatically when it is
executed. Macros are meant for automating those tasks that you do over and over
again i.e;
Printing month-end reports.
Adding new record to a table.
Printing letters to customers periodically.
Modules
A module is another MS-Access database object. A module is a program you write
using VBA to automate and customize database functions. VBA is a Visual Basic for
Applications, the programming language included in office 97.
Create an Access database
Microsoft Access provides two methods to create an Access database. You can use a
Database Wizard to create in one operation the required tables, forms, and reports
for the type of database you choose — this is the easiest way to start creating your
database. Or you can create a blank database and then add the tables, forms,
reports, and other objects later — this is the most flexible method, but it requires
you to define each database element separately. Either way, you can modify and
extend your database at any time after it has been created.
Create a database by using a Database Wizard
Click New on the toolbar.
In the New File task pane, under New from template, click General
templates.
On the Databases tab, click the icon for the kind of database you want to
create, and then click OK.
In the File New Database dialog box, specify a name and location for the
database, and then click Create.
Follow the instructions in the Database Wizard.
Create a database without using a Database Wizard
Click New on the toolbar.
2 City Corporation Computer Institute
MS ACCESS
In the New File task pane, under New, click Blank Database.
In the File New Database dialog box, specify a name and location for the
database, and then click Create.
The Database window appears, and you can create the objects that you want
in your database.
About creating a table
To create a blank (empty) table for entering your own data, you can: Use the Table
Wizard to choose the fields for your table from a variety of predefined tables such
as business contacts, household inventory, or medical records.
Create a table in Design view, where you can add fields, define how each field
appears or handles data, and create a primary key.
Enter data directly into a blank datasheet. When you save the new datasheet,
Microsoft Access will analyze your data and automatically assign the appropriate
data type and format for each field.
To Create a table we can use Field we can use10 types of data type in
Access
Data Type Property
You can use the Data Type property to specify the type of data stored in a table
field. Each field can store data consisting of only a single data type.
Setting
The Data Type property uses the following settings.
Setting Type of data Size
Text (Default) Text or combinations of Up to 255 characters or the
text and numbers, as well as length set by the Field
numbers that don't require Size property, whichever is
calculations, such as phone less. Microsoft Access does
numbers. not reserve space for
unused portions of a text
field.
Memo Lengthy text or combinations of Up to 65,535 characters.
text and numbers. (If the Memo field is
manipulated through DAO
and only text and numbers
[not binary data] will be
stored in it, then the size of
the Memo field is limited by
the size of the database.)
Number Numeric data used in 1, 2, 4, or 8 bytes (16
mathematical calculations. For bytes if the FieldSize
more information on how to set property is set to
the specific Number type, see the Replication ID).
FieldSize property topic.
Date/Time Date and time values for the 8 bytes.
years 100 through 9999.
3 City Corporation Computer Institute
MS ACCESS
Currency Currency values and numeric data 8 bytes.
used in mathematical calculations
involving data with one to four
decimal places. Accurate to 15
digits on the left side of the
decimal separator and to 4 digits
on the right side.
AutoNumber A unique sequential (incremented 4 bytes (16 bytes if the
by 1) number or random number FieldSize property is set
assigned by Microsoft Access to Replication ID).
whenever a new record is added
to a table. AutoNumber fields
can't be updated. For more
information, see the New Values
property topic.
Yes/No Yes and No values and fields that 1 bit.
contain only one of two values
(Yes/No, True/False, or On/Off).
OLE Object An object (such as a Microsoft Up to 1 gigabyte (limited
Excel spreadsheet, a Microsoft by available disk space)
Word document, graphics,
sounds, or other binary data)
linked to or embedded in a
Microsoft Access table.
Hyperlink Text or combinations of text and Each part of the three parts
numbers stored as text and used of a Hyperlink data type
as a hyperlink address. A can contain up to 2048
hyperlink address can have up to characters.
three parts:
text to display — the text that
appears in a field or control.
address — the path to a file (UNC
path) or page (URL).
subaddress — a location within
the file or page.
screentip — the text displayed as
a tooltip.
The easiest way to insert a
hyperlink address in a field or
control is to click Hyperlink on
the Insert menu.
Lookup Wizard Creates a field that allows you to The same size as the
choose a value from another table primary key field used to
or from a list of values by using a perform the lookup,
list box or combo box. Clicking typically
this option starts the Lookup
4 City Corporation Computer Institute
MS ACCESS
Wizard, which creates a Lookup
field. After you complete the
wizard, Microsoft Access sets the
data type based on the values
selected in the wizard.
Field properties
Field properties can be set on the basis of the field type. The field properties are
different for different types of fields. The field properties panel is displayed below
the main panel where the field names are entered. To switch between the
properties panel and the main panel, Press F6
Properties of text
Field Size: Field size property applies to text and number fields only, for text field
size can vary from 1 to 255. Default is 50. For numbers the field size can be a Byte,
Integer, Long Integer, Single or Double. Default is double.
Format: Format specifies how the data entered in a field must be displayed in the
Datasheet View. The format property will be different for different fields. To specify
the format property for text and memo fields include a specific symbol in the format
box.
Following are the symbols available and their meanings:
Symbol Meaning
Display contents in upper case
@ ‖NULL‖ Displays "NULL" if empty
@"Not entered" Display "Not entered" if field is empty
$ Right aligns text in column
To specify the format property for Number, Currency, Date/Time, Yes/No fields,
select appropriate option from the field property list.
Caption: All types of fields have a caption property. The caption is used as the
heading of the field's column in Datasheet view. The size of the caption can be upto
255 characters. For example, the caption for the field ST ID can be student_id for
st_name, it can be Student Name etc.
Default Value: Default value specifies the value that must be displayed in a
particular field when making the data entry. For example, you can type 'Karnataka'
as the default value for the state field. This data will appear automatically during
data entry and can be modified if required.
Required: Required property specifies if data must be entered in a field or not.
Hence if required is set to Yes the user will not be allowed to leave the field blank
during data entry. By default, required property is set to NO for all fields.
Allow Zero length
You can use the AllowZeroLength property to specify whether a zero-length
string (zero-length string: A string that contains no characters. You can use a zero-
length string to indicate that you know there's no value for a field. You enter a
zero-length string by typing two double quotation marks with no space between
them (" ").) (" ") is a valid entry in a table field.
Note The AllowZeroLength property applies only to Text, Memo, and Hyperlink
table fields.
Setting
The AllowZeroLength property uses the following settings.
5 City Corporation Computer Institute
MS ACCESS
Setting Visual Basic Description
Yes True A zero-length string is a valid entry.
No False (Default) A zero-length string is an invalid entry.
Input Mask
Microsoft Access interprets characters in the Input Mask property definition as
shown in the following table. To define a literal character, enter any character other
than those shown in the table, including spaces and symbols. To define one of the
following characters as a literal character, precede that character with a backslash
(\).
Character Description
0 Digit (0 through 9, entry required; plus [+] and minus [-] signs not
allowed).
9 Digit or space (entry not required; plus and minus signs not allowed).
# Digit or space (entry not required; blank positions converted to spaces,
plus and minus signs allowed).
L Letter (A through Z, entry required).
? Letter (A through Z, entry optional).
A Letter or digit (entry required).
a Letter or digit (entry optional).
& Any character or a space (entry required).
C Any character or a space (entry optional).
.,:;-/ Decimal placeholder and thousands, date, and time separators. (The
actual character used depends on the regional settings specified in
Microsoft Windows Control panel.)
Causes all characters that follow to be converted to uppercase.
! Causes the input mask to display from right to left, rather than from left
to right. Characters typed into the mask always fill it from left to right.
You can include the exclamation point anywhere in the input mask.
\ Causes the character that follows to be displayed as a literal character.
Used to display any of the characters listed in this table as literal
characters (for example, \A is displayed as just A).
Password Setting the InputMask property to the word Password creates a
password entry text box. Any character typed in the text box is stored
as the character but is displayed as an asterisk (*).
6 City Corporation Computer Institute
MS ACCESS
validation rule: Validation Rule: Validation Rule property allows specifying what
validation must be made for the data entered in the field. If anything is specified in
the validation rule property, only the data that satisfies the condition specified can
be entered, all other data is rejected. For example, Total_fees must be greater than
zero and to specify this the validation rule property >0.
Validation Text: When the data entered in the field does not conform to the
validation rule that is specified, an error message is displayed on the screen that
informs the user what is wrong. Validation Text is used to display custom error
messages instead of default error messages. For example for Total_fees,
‖Total_fees must be greater than 0‖.
Properties Of Number
Field Size Property
You can use the Field Size property to set the maximum size for data stored in a
field set to the Text, Number, or AutoNumber data type.
Setting
If the Data Type property is set to Text, enter a number from 0 to 255. The
default setting is 50.
If the Data Type property is set to AutoNumber, the Field Size property can be set
to Long Integer or Replication ID.
If the Data Type property is set to Number, the Field Size property settings and
their values are related in the following way.
Setting Description Decimal precision Storage size
Byte Stores numbers from 0 to None 1 byte
255 (no fractions).
Decimal Stores numbers from – 28 12bytes
10^38–1 through 10^38–1
(.adp)
Stores numbers from –
10^28–1 through 10^28–1
(.mdb)
Integer Stores numbers from – None 2 bytes
32,768 to 32,767 (no
fractions).
Long Integer (Default) Stores numbers None 4 bytes
from –2,147,483,648 to
2,147,483,647 (no
fractions).
Single Stores numbers from 7 4 bytes
–3.402823E38 to –
1.401298E–45
for negative values and
from
1.401298E–45 to
3.402823E38 for positive
values.
7 City Corporation Computer Institute
MS ACCESS
Double Stores numbers from 15 8 bytes
–1.79769313486231E308
to
–4.94065645841247E–324
for negative values and
from
4.94065645841247E–324
to
1.79769313486231E308 for
positive values.
Replication ID Globally unique identifier N/A
(GUID)
Format Property - Date/Time Data Type
You can set the Format property to predefined date and time formats or use custom
formats for the Date/Time data type.
The following table shows the predefined Format property settings for the
Date/Time data type.
Setting Description
General Date (Default) If the value is a date only, no time is displayed; if
the value is a time only, no date is displayed. This setting is a
combination of the Short Date and Long Time settings.
Examples: 4/3/93, 05:34:00 PM, and 4/3/93 05:34:00 PM.
Long Date Same as the Long Date setting in the Regional Settings
Properties dialog box in Windows Control Panel.
Example: Saturday, April 3, 1993.
Medium Date Example: 3-Apr-93.
Short Date Same as the Short Date setting in the Regional Settings
Properties dialog box in Windows Control Panel.
Example: 4/3/93.
Warning The Short Date setting assumes that dates between
1/1/00 and 12/31/29 are twenty-first century dates (that is,
the years are assumed to be 2000 to 2029). Dates between
1/1/30 and 12/31/99 are assumed to be twentieth century
dates (that is, the years are assumed to be 1930 to 1999).
Long Time Same as the setting on the Time tab in the Regional Settings
Properties dialog box in Windows Control Panel.
Example: 5:34:23 PM.
Medium Time Example: 5:34 PM.
Short Time Example: 17:34
An Example of a table
A table is design for practice named employee.
Field name Data Type
8 City Corporation Computer Institute
MS ACCESS
Emp_id Text
Emp_name Text
Emp_address Text , lookup wizard
Emp_Position Text , lookup wizard
Salary Number
Joiningdate Date and time
Picture Ole object
Sex Yes/no
Remarks Memo
Creating a Table Using the Design View
Open the database in which the table is to be created - Emp. It may already be
open.
The database window is displayed with tabs to create different database objects.
Click on the table tab and click on new. The following dialog box is displayed.
Select design view and click on OK. The table structure is displayed as shown
below.
Filter:
We have seen earlier how to search for records with the Find command. You enter
the value you want MS-Access to look for and then view the retrieved records one
at a time. The Find ommand however will not work if you need to formulate a
search that checks for values in more than one field. For this type of job you need a
filter or a query.
Difference between Filters and Queries
Both filters and queries can be used to find records, but there are differences in
how you set them up and how they work.
Use a Filter: to quickly limit the records you are already viewing in a Datasheet or
a form to those that match the criteria that you specify.
Use a Query: to view selected fields and records from one or more tables, then
save the query to open I t later or use it as the basis of a form or a report.
There are 4 types of filter in access.
Filter By Form
Displays the Filter by Form window, which is a blank version of the active form or
datasheet, so you can filter data.
Filter By Selection
Filters records by selected data. To filter by selection, select a field or part of a field
in a datasheet or form, and then click Filter By Selection.
Filter Excluding Selection
Filters records by excluding selected data. To filter by excluding the selection, select
a field or part of a field in a datasheet or form, point to Filter on the Records menu,
and then click Filter Excluding Selection.
Filter Advanced Filter
Displays the Advanced Filter/Sort window so you can create a filter to apply to the
datasheet or form.
To Create a Filter by form
First open the table in which record you want to display. Ex : if you want to show
the salary of employee table
1) put the cursor in the salary field.
2) Press Alt+R or record menu
9 City Corporation Computer Institute
MS ACCESS
3) Press filter option by the mouse pointer
4) Click filter by form
5) The list of salary will be shown by the drop down list and you can the display
the record which you want to display the salary
To Create a Filter by Selection:
Filter by selection lets you select a value in a Datasheet or a form and click on a
button to find any matching records. For example, to search for records in the
Employee table where Address=" CTG ".
Open the Employee Table.
Click on CTG in the Address field in any record.
Click on Filter by Selection on the toolbar. You will see all CTG records.
Click on the Remove Filter icon in the tool bar to view all the records.
To Create Filter By Excluding Selection
1. Select the record which you want to not display.
2. Select by excluding selection.
3. Then click the apply filter
To create filter by using advanced filter:
Advanced Filter Using OR / AND
An advanced filter lets you combine conditions and fields freely for a filter. To
create an advanced filter or sort you have to open a. Filter editing window.
Open the Datasheet or Form View you want to filter.
From the menu bar, Choose Records >Filter > Advanced Filter/Sort.
A grid appears at the bottom half of the window. Drag a field name from the
field list into the grid or type the name of the field or choose the name from
the drop down list on the grid.
Now enter the value in the criteria row for the field.
For an Or filter you enter an additional condition in the OR row for whatever
field it applies to.
For an AND filter you enter an additional condition in the criteria row for
whatever field it applies to.
Click on Records> Apply filter to see the filtered records.
Figure shows the advanced filter for records with Address=CTG AND Position=
Officer OR Manager
Queries
About types of queries
You use queries to view, change, and analyze data in different ways. You can also
use them as a source of records for forms, reports, and data access pages. There
are several types of queries in Microsoft Access.
1. Select queries
A select query is the most common type of query. It retrieves data from one or more tables and
displays the results in a datasheet where you can update the records (with some restrictions). You
can also use a select query to group records and calculate sums, counts, averages, and other types
of totals.
10 City Corporation Computer Institute
MS ACCESS
2. Parameter queries
A parameter query is a query that when run displays its own dialog box prompting you for
information, such as criteria for retrieving records or a value you want to insert in a field. You
can design the query to prompt you for more than one piece of information; for example, you can
design it to prompt you for two dates. Access can then retrieve all records that fall between those
two dates.
Parameter queries are also handy when used as the basis for forms, reports, and
data access pages. For example, you can create a monthly earnings report based
on a parameter query. When you print the report, Access displays a dialog box
asking for the month that you want the report to cover. You enter a month and
Access prints the appropriate report.
3. Cross tab queries
You use cross tab queries to calculate and restructure data for easier analysis of
your data. Cross tab queries calculate a sum, average, count, or other type of total
for data that is grouped by two types of information — one down the left side of the
datasheet and another across the top.
4. Action queries
An action query is a query that makes changes to or moves many records in just
one operation. There are four types of action queries:
Delete Queries A delete query deletes a group of records from one or
more tables. For example, you could use a delete query to remove products
that are discontinued or for which there are no orders. With delete queries,
you always delete entire records, not just selected fields within records.
Update Queries An update query makes global changes to a group of
records in one or more tables. For example, you can raise prices by 10
percent for all dairy products, or you can raise salaries by 5 percent for the
people within a certain job category. With an update query, you can change
data in existing tables.
Append Queries An append query adds a group of records from one or
more tables to the end of one or more tables. For example, suppose that you
acquire some new customers and a database containing a table of
information on those customers. To avoid typing all this information into your
own database, you'd like to append it to your Customers table.
Make-Table Queries A make-table query creates a new table from all or
part of the data in one or more tables. Make-table queries are helpful for
creating a table to export to other Microsoft Access databases or a history
table that contains old records.
For this query The datasheet displays
Update The fields to be updated.
11 City Corporation Computer Institute
MS ACCESS
Delete The records to be deleted.
Make-table The fields to be included in the new table.
Append The records to be added to another table.
6. SQL queries
7. An SQL query is a query you create by using an SQL statement. You can use
Structured Query Language (SQL) to query, update, and manage relational databases
such as Access.
When you create a query in query Design view, Access constructs the equivalent
SQL statements behind the scenes for you. In fact, most query properties in the
property sheet in query Design view have equivalent clauses and options available
in SQL view. If you want, you can view or edit the SQL statement in SQL view.
However, after you make changes to a query in SQL view, the query might not be
displayed the way it was previously in Design view.
12 City Corporation Computer Institute
MS ACCESS
Create a Select Query with One Table:
Let us start with a simple example that creates a query to show selected fields and
records from one table.
In the database window click on the queries tab.
1. Click on New to open New Query dialog box.
2. Double click on Design View or click on Ok.
3. From the Tables list, Select the Employee Table, click on add, to add the
table to the tables list and then click on close.
4. You will see a query window which appears as given in Figure
Figure : Simple example of select query
Drag the fields from the field list to the columns in the bottom half of the window in
whichever order that you would want to see them. You can run the query at this
records
point of time to show the data for the fields you have dragged for allField list in the
Employee table.
To run the query: Select Query> run or Click on the Run icon
Example of Query to display only those records in the Employee table where
salaries are greater than 7000. Such a query requires you to add some criteria to
the query grid. Design grid
Choose a sort order
To sort a query by a particular field, choose Ascending or Descending from the drop
down list for he field's sort row in the query grid.
Run the query
Click on Run on the toolbar to see salary of a query in the datasheet view.
To return to the design view, click on the View icon in the toolbar,
In a query you can look for special criteria like records with blank values, records
that satisfy a range of values or even use Visual Basic functions like date ().
Examples of functions used in query criteria,
Expression Result
> 234 For a Quantity field, numbers greater than 234
= "Callahan"
through the end of the alphabet
#2/2/95# Display the joining date on 2 feb-95
Between #2/2/1999# And For an OrderDate field, dates from 2-Feb-99
13 City Corporation Computer Institute
MS ACCESS
#12/1/1999# through 1-Dec-99
Between 5000 And 10000 For salary field, salary from 5000 through 10000
Values that don't match (Not)
Expression Result
Not "CTG" For a address field, to find out all records without CTG
For a LastName field, employees whose names don't start with the
Not T*
letter "T"
Display the name field where the employee name are start with in N-
>=N
Z
Like ―S*‖ Display the name field where the employee name are start from S
Like‖[A- Display the name field where the employee name are start A through
D]*‖ D
Like‖*ar*‖ Display the employees whose names include the letter sequence ―ar‖
Values in a list (In)
Expression Result
In("CTG", "Dhaka") For an address where the employees live in CTG and Dhaka
Save and Change Queries
Unlike filters, queries can be saved as objects unto themselves in a database. Once a query is
saved you can run it from the database window or design it anew.
Create a parameter query
1. Create a Parameter query.
2. In query Design view, drag the fields from the field list to the query design
grid.
3. Do one or more of the following:
Use one parameter
In the Criteria cell for the field you want to use as a parameter, type an expression
with a prompt enclosed in square brackets. For example, in a field that displays the
current number of units in stock, enter the following:
[Number of Units in Stock:]
Use two or more parameters
In the Criteria cell for each field you want to use as a parameter, type an
expression with prompts enclosed in square brackets.
For example, in a field that displays dates, you can display the prompts "Type the
beginning date:" and "Type the ending date:" to specify a range of values:
14 City Corporation Computer Institute
MS ACCESS
Between [Type the beginning date:] And [Type the ending date:]
Use parameters with wildcards
In the Criteria cell for each field you want to use as a parameter, type an
expression with a prompt enclosed in square brackets. To prompt the user for one
or more characters to search for, and then find records that begin with or contain
the characters the user specifies, create a parameter query that uses the LIKE
operator and the wildcard symbol (*).
For example, the following statement searches for words that begin with a specified
letter:
LIKE [Enter the first character to search by: ] & "*"
The following statement searches for words that contain the specified character:
LIKE "*" & [Enter any character to search by: ] & "*"
Create a cross tab query on my own:
At First make this table
crosstab
Itemname customerid amount
HDD C001 2000
FDD C001 5000
Floppy C002 700
Monitor C002 6000
1. In the database window click on Queries tab and select new.
2 Double click on Crosstab Query wizard.
3 In the fist step of the wizard, select the table or the query from which the
data must be used. Let us select the Crosstab table.
4 In the second step select the row headings. Click on Item no.
5 Now select the field for column headings i.e. customerid.
6 Select Salary on the field list and then highlight Sum on the Functions list.
Give the Query a name and click on Finish.
After the finishing the wizard you will find the figure as shown bellow.
crosstab_Crosstab
Total Of
Itemname C001 C002
amount
FDD 5000 5000
Floppy 700 700
HDD 2000 2000
Monitor 6000 6000
Action Query:
Create an Update query:
1. Create a query with the tables or queries that include the records you want
to update.
Suppose I want to update Employees salary by 10% where salary <10000
a) In the Database window, click Queries under Objects, and then click
New on the Database window toolbar.
15 City Corporation Computer Institute
MS ACCESS
b) In the New Query dialog box, click Design View, and then click OK.
c) In the Show Table dialog box, click the tab that lists the tables or
queries whose data you want to work with.
d) Double-click the name of each object you want to add to the query,
and then click Close.
e) Add fields to the Field row in the design grid, and if you want, specify
criteria and a sort order.
f) To view the query's results, click View on the toolbar.
2. In query Design view, click the arrow next to Query Type on the
toolbar, and then click Update Query.
3. Drag from the field list to the query design grid the fields you want to update
or you want to specify criteria for.
4. In the Criteria cell, specify the criteria if necessary.
5. In the Update To cell for the fields you want to update, type the expression
or value you want to use to change the fields, as shown in the following
illustration.
6. To see a list of the records that will be updated, click View on the toolbar.
This list won't show the new values. To return to query Design view, click
View on the toolbar again. Make any changes you want in Design view.
7. Click Run on the toolbar to update the records.
Create a delete Query
1. Create a new query that contains the "one" table from which you want to
delete records.
Suppose you want to delete those records where the salary is <10000
In the Database window, click Queries under Objects, and then click New on the
Database window toolbar.
a) In the New Query dialog box, click Design View, and then click OK.
b) In the Show Table dialog box, click the tab that lists the tables or
queries whose data you want to work with.
c) Double-click the name of each object you want to add to the query,
and then click Close.
d) Add fields to the Field row in the design grid, and if you want, specify
criteria and a sort order.
e) To view the query's results, click View on the toolbar.
Figure : Delete query
2. In query Design view, click the arrow next to Query Type on the toolbar,
and then click Delete Query.
3. To specify criteria for deleting records, drag to the design grid the fields on
which you want to set criteria.
4. In the Criteria cell for the fields that you have dragged to the grid, type the
criteria.
16 City Corporation Computer Institute
MS ACCESS
5. To preview the records that will be deleted, click View on the toolbar. To
return to query Design view, click View on the toolbar again. Make any
changes you want in Design view.
6. Click Run on the toolbar to delete the records.
7. Create an append query
Create a query that contains the table whose records you want to append to
another table.
Suppose you have two table one name employee and another named emp .
you want to append the name field to the emp table . It will be dine by
following instruction.
1. In the Database window, click Queries under Objects, and then click
New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. In the Show Table dialog box, click the tab that lists the tables or
queries whose data you want to work with.
4. Double-click the name of each object you want to add to the query,
and then click Close.
5. In query Design view, click the arrow next to Query Type on the
toolbar, and then click Append. The Append dialog box appears.
6. In the Table Name box, enter the name of the table you want to
append records to.
7. Click OK.
8. Drag from the field list to the query design grid the fields you want to
append and any fields you want to use for setting criteria.
Figure: Here you select the name of table where you want to append your data
Create a make table query
Create a new table from all or part of the data one or more tables.
Suppose I want to make a new table by taking the field from the student details
and student test table.
To create a make table query you must have a relation with the two tables.
1. From the show table dialogue box select the student details and student
test table.
2. Select the make table query from the query menu. Give a name for the
new table.
3. select the field you want to add the new table.
4. Click run
Create SQL Query
In the Database window, click Queries under Objects, and then click New on
the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
Without adding tables or queries, click Close in the Show Table dialog box.
On the Query menu, point to SQL Specific, and then click Union.
Enter SQL SELECT statements combined with either the UNION operation if
you don't want to return duplicate records or the UNION ALL operation if you
do want to return duplicate records.
17 City Corporation Computer Institute
MS ACCESS
Note: Each SELECT statement must return the same number of fields, in the
same order. The corresponding fields are required to have compatible data types
with one exception: You can use a Number and a Text field as corresponding
fields.
If you want to specify sorting in a union query, add a single ORDER BY clause
to the end of the last SELECT statement. In the ORDER BY clause, specify the
field name to sort, which must come from the first SELECT statement.
To see the query's results, click View on the toolbar.
Some Example of SQL Statements:
SQL Query by using Select satatement
1. Select * from [Employee]
2. Select emp_salary from [employee]
3. Select [emp_name],[emp_address] from daily products
SQL Query by using where clause
Select [emp_id],emp_name from [employee] where [emp_salary]=5000
1. RELATIONAL DATABASES
We earlier remarked that MS-Access is a RDBMS or a Relational Database
Management System. Now what do we mean by a Relational database? In a
relational database the tables are related on common fields known as Key fields.
This helps in accessing data from more than one table. The relationship may be
defined at the time of table creation or at a later stage. Usually it is done right in
the beginning when planning the database design. The relational database process
provides a structured approach to modeling an information system’s data and the
business rules for that data. The three key components used in relational database
design as discussed earlier are Entities, Attributes and Relationships.
Key Field
We have already seen that in MS-Access data form more than one table can be
related on common field called key field. The key-field links the data in the two
tables. MS-Access senses this relationship by comparing the key fields and if they
match performs an automatic linking. Such an automatic linking is called AUTO-
JOIN. However users can specifically (manually) link any two tables on required key
fields by creating a relationship.
Primary Key
Primary key is a rule which ensures that unique data is entered for a field and the
field is not left blank (Null). This makes the record unique. For example, in a Bank
Master file, there will be only one record for one Account number. Student roll
numbers, Account numbers, Customer numbers, receipt numbers are examples for
primary key fields.
Foreign Key
In order to manage the data we distribute the data in many tables. The amin table
is referred to as the Master table and the lower level table in which the related data
is stored is the transaction table. The two tables are related and must be linked
based on a field, which is common. This common key field in the transaction table
is called the foreign key and its values depend on the primary key values of the
master table.
How relationships work
In the previous example, the fields in four tables must be coordinated so that they
show information about the same order. This coordination is accomplished with
relationships between tables. A relationship works by matching data in key fields —
usually a field with the same name in both tables. In most cases, these matching
18 City Corporation Computer Institute
MS ACCESS
fields are the primary key from one table, which provides a unique identifier for
each record, and a foreign key in the other table. For example, employees can be
associated with orders they're responsible for by creating a relationship between
the EmployeeID fields.
Primary key
employee
employeeid lastname firstname
1 Davalo nancy
The EmployeeID field appears
In both tables
orders
orderid customerid employeeid
10022 Lalmai 1
Foreign key
Figure : Define keys
Enforcing 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. You can set referential integrity when all
of the following conditions are met:
The matching field from the primary table is a primary key or has a unique
index.
The related fields have the same data type. There are two exceptions. An
AutoNumber field can be related to a Number field with a Field Size property
setting of Long Integer, and an AutoNumber field with a Field Size property
setting of Replication ID can be related to a Number field with a Field Size
property setting of Replication ID.
Both tables belong to the same Microsoft Access database. If the tables are
linked tables, they must be tables in Microsoft Access format, and you must
open the database in which they are stored to set referential integrity.
Referential integrity can't be enforced for linked tables from databases in
other formats.
The following rules apply when you use referential integrity:
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. However, you can enter
a Null value in the foreign key, specifying that the records are unrelated. For
example, you can't have an order that is assigned to a customer that doesn't
exist, but you can have an order that is assigned to no one by entering a Null
value in the CustomerID field.
19 City Corporation Computer Institute
MS ACCESS
You can't delete a record from a primary table if matching records exist in a
related table. For example, you can't delete an employee record from the
Employees table if there are orders assigned to the employee in the Orders
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 an employee's ID in the
Employees table if there are orders assigned to that employee in the Orders
table.
Cascade update and cascade delete
For relationships in which referential integrity is enforced, you can specify whether
you want Microsoft Access to automatically cascade update and cascade delete
related records. If you set these options, delete and update operations that would
normally be prevented by referential integrity rules are allowed. When you delete
records or change primary key values in a primary table, Microsoft Access makes
necessary changes to related tables to preserve referential integrity.
If you select the Cascade Update Related Fields check box when defining a
relationship, any time you change the primary key of a record in the primary table,
Microsoft Access automatically updates the primary key to the new value in all
related records. For example, if you change a customer's ID in the Customers table,
the CustomerID field in the Orders table is automatically updated for every one of
that customer's orders so that the relationship isn't broken. Microsoft Access
cascades updates without displaying any message.
If you select the Cascade Delete Related Records check box when defining a
relationship, any time you delete records in the primary table, Microsoft Access
automatically deletes related records in the related table. For example, if you delete
a customer record from the Customers table, all the customer's orders are
automatically deleted from the Orders table (this includes records in the Order
Details table related to the Orders records). When you delete records from a form
or datasheet with the Cascade Delete Related Records check box selected,
Microsoft Access warns you that related records may also be deleted. However,
when you delete records using a delete query, Microsoft Access automatically
deletes the records in related tables without displaying a warning.
Types of Relation Ship: There are three types of relationship.
One To one relationship
In a one-to-one relationship, 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. This type of relationship is not common, because most information related
in this way would be in one table. You might use a one-to-one relationship to divide
a table with many fields, to isolate part of a table for security reasons, or to store
information that applies only to a subset of the main table. For example, you
might want to create a table to track employees participating in a
fundraising soccer game. Each soccer player in the Soccer Players table
has one matching record in the Employees table.
20 City Corporation Computer Institute
MS ACCESS
Each soccer player has one matching record
in the
Employees table
The set of values is a subset of the
EmployeeID field in the Employees table
Figure : Example of one to one relationship
One to many relationship
A one-to-many relationship is the most common type of relationship. In a one-to-many
relationship, a record in Table A can have many matching records in Table B, but a record in Table
B has only one matching record in Table
Many To many relationship:
In a many-to-many relationship, a record in Table A can have many matching records in Table
B, and a record in Table B can have many matching records in Table A. This type of relationship
is only possible by defining a third table (called a junction table) whose primary key consists of
two fields — the foreign keys from both Tables A and B. A many-to-many relationship is really
two one-to-many relationships with a third table. For example, the Orders table and the Products
table have a many-to-many relationship that's defined by creating two one-to-many relationships
to the Order Details table. One order can have many products, and each product can appear on
many orders.
Form
A form is a type of a database object that is primarily used to enter or display data
in a database. You can also use a form as a switchboard that opens other forms and
reports in the database, or as a custom dialog box that accepts user input and
carries out an action based on the input.
Create a form
There are three ways to create a form.
Based on a single table or query by using Auto Form. Auto Form creates a
form that displays all fields and records in the underlying table or query. If the
record source you select has related tables or queries, the form will also include all
the fields and records from those record sources.
How?
1. In the Database window, click Forms under Objects.
2. Click the New button on the Database window toolbar.
3. In the New Form dialog box, click one of the following wizards:
o AutoForm: Columnar Each field appears on a separate line with a
label to its left.
o AutoForm: Tabular The fields in each record appear on one line,
with the labels displayed once at the top of the form.
21 City Corporation Computer Institute
MS ACCESS
o AutoForm: Datasheet The fields in each record appear in row-and-
column format, with one record in each row and one field in each
column. The field names appear at the top of each column.
o AutoForm: PivotTable The form opens in PivotTable view. You can
add fields by dragging them from the field list to the different areas in
the view.
o AutoForm: PivotChart The form opens in PivotChart view. You can
add fields by dragging them from the field list to the different areas in
the view.
4. Click the table or query that includes the data you want to base your form
on.
5. Click OK.
Microsoft Access applies the last auto format you used to the form. If you haven't created a form
with a wizard before or haven't used the AutoFormat command on the Format menu, it uses the
Standard auto format.
You can also create a columnar AutoForm based on the open record source or the record source
that's selected in the Database window. Click AutoForm on the Insert menu, or click the arrow
next to the New Object button on the toolbar, and then click AutoForm. If the record source
you select has related tables, AutoForm adds a subform that contains the related tables displayed
as datasheets and sub datasheets.
Based on one or more table or query with a wizard. The wizard asks you
detailed questions about the record sources, fields, layout, and format you want
and creates a form based on your answers.
How?
In the Database window, click Forms under Objects.
1. Click the New button on the Database window toolbar.
2. In the New Form dialog box, click the wizard that you want to use. A
description of the wizard appears in the left side of the dialog box.
3. Click the name of the table or other record source that includes the data you
want to base your form on.
Note You don't need to do this step if you selected Form Wizard or
PivotTable Wizard in step 3 — you can specify the record source for the form
later.
4. Click OK.
5. Follow the instructions in the wizard.
If the resulting form doesn't look the way you want, you can change it in
Design view, PivotTable view, or PivotChart view.
Notes
22 City Corporation Computer Institute
MS ACCESS
If you want to include fields from multiple tables and queries in your form,
don't click Next or Finish after selecting the fields from the first table or
query in the Form Wizard. Repeat the steps to select a table or query, and
pick fields that you want to include in the form, until you have selected all
the required fields.
If you click one of the AutoForm options, Microsoft Access uses the auto
format you last specified, either in the Form Wizard or using the
AutoFormat command on the Format menu in Design view.
On your own in Design view. You create a basic form and customize it in
Design view to suit your requirements.
How?
1. In the Database window, click Forms under Objects.
2. Click the New button on the Database window toolbar.
3. In the New Form dialog box, click Design View.
4. Click the name of the table or other record source that includes the data you
want to base your form on. If the form won't contain data (for example, if you want
to create a form to use as a switchboard to open other forms or reports, or if you
want to create a custom dialog box), don't select anything from this list.
If you want to create a form that uses data from more than one table, base your
form on a query.
5. Click OK.
Microsoft Access displays the form in Design view.
To work in a design view we have to know about control and Bands
Control: A graphical user interface object, such as a text box, check box, scroll
bar, or command button, that lets users control the program. You use controls
to display data or choices, perform an action, or make the user interface easier
to read.
Bound Control: A bound control is tied to a field in an underlying table or
query. In a Microsoft Access project, a control can be bound to a field in a view
or stored procedure. If the Access project is connected to a database in
Microsoft SQL Server 2000, a control can also be bound to a field in a function.
You use bound controls to display, enter, or update values from fields in your
database.
Unbound Control An unbound control doesn't have a data source. You can use
unbound controls to display information, lines, rectangles, and pictures.
Calculated Control A calculated control uses an expression as its source of
data. An expression can use data from a field in an underlying table or query of
a form or report, or from another control on the form or report
Toolbox : We can use the tool box for different types of control.
23 City Corporation Computer Institute
MS ACCESS
Field list : A window that lists all the fields in the underlying record source or
database object, except in data access page Design view. In data access page
Design view, it lists all the record sources and their fields in the underlying
database.
Section of a design view in Form:
In a form design view there are 5 section
Form Header: Displays, for example, a title for the form, instructions for using it,
or command buttons that open related forms or carry out other tasks. A form
header appears at full height, so the controls in the section appear on the screen at
all times. A form header displays at the top of a form in Form view and at the
beginning of a printout. It doesn’t appear in Datasheet view.
Page Header: Displays a title, column headings, or any information you want at
the top of every page. A page header appears only on printed forms.
Detail section: Displays records from the form's record source. This section
usually contains controls bound to the fields in the record source but can also
contain unbound controls, such as labels that identify a field's contents.
Page Footer: Displays the date, page number, or any information you want at the
bottom of every page. A page footer appears only on printed forms.
Form Footer: Displays, for example, instructions for using the form, command
buttons, or unbound controls to accept input. A form footer appears at full height,
so the controls in the section appear on the screen at all times. A form footer
displays at the bottom of a form in Form view and at the end of a printout. It
doesn’t appear in Datasheet view.
Figure : Design View in a form
Edit Command button in a form:
You can edit command button in a form as you like. If you edit command
button in a form you can use the Navigation Buttons property to specify
whether navigation buttons (navigation buttons: The buttons that you use to move
through records. They are located in the lower-left corner of the Datasheet view
and Form view windows. They are also available during previewing so that you can
move through the pages of your document.) and a record number box are
displayed on a form. Read/write Boolean.
24 City Corporation Computer Institute
MS ACCESS
To create a command button select the command control from the tool box
and add it in the design view where you want to make a command
The command Button wizard will come in the screen
You can choose different types of categories and action.
Then click next
You can choose your picture from picture option and then click finish.
By running a form following types of view is found in a form
About reports
A report is an effective way to present your data in a printed format. Because you
have control over the size and appearance of everything on a report, you can
display the information the way you want to see it.
Create a report Using Auto Report:
Auto Report Creates a report that displays all records in the underlying table or
query.
1. In the Database window, click Report under Objects.
2. Click the New button on the Database window toolbar.
3. In the New report dialog box, click one of the following wizards
AutoForm: Columnar Each field appears on a separate line with a label
to its left.
AutoForm: Tabular The fields in each record appear on one line, with
the labels displayed once at the top of the form.
4. Click the table or query that includes the data you want to base your
form on.
5. Click OK
Create a report with a Wizard
In the Database window, click Report under Objects.
1. Click the New button on the Database window toolbar.
2. In the New Report dialog box, click the wizard that you want to use. A
description of the wizard appears in the left side of the dialog box.
3. Click the name of the table or other record source that includes the data you
want to base your report on.
4. Click OK.
5. Follow the instructions in the wizard.
If the resulting form doesn't look the way you want, you can change it in
Design view, PivotTable view, or PivotChart view.
25 City Corporation Computer Institute
MS ACCESS
Create a Report on your own
You create a basic Report and customize it in Design view to suit your
requirements.
How?
1) In the Database window, click Report under Objects.
2) Click the New button on the Database window toolbar.
3) In the New report dialog box, click Design View.
4. Click the name of the table or other record source that includes the data you
want to base your Report on. If the Report won't contain data don't select anything
from this list.
For Example: Make a Salary sheet by using Emp table. Add a new field
with group by job title show the subtotal of all H_rent (40% of Salary) ,
medical (10% of salary) and Total (Salary+H_rent+medical). The grand
total will be shown in report footer .
The table is given bellow
Emp
Name Jobtitle Salary
Abu sufian Officer 5600
Motaleb Hossan Manager 10000
Liton poul Officer 6500
Rana Datta Manager 12000
Mukta Das Officer 8500
Omar Faruque Clark 3500
Chitra Aich Clark 3000
After finishing this procedure go to report design view.
Now you make a h_rent, medical, total salary label by label control.
For calculation you use the text box control and write the formula by the
following picture.
For group by :
1. You will select the view menu
2. Select Sorting and grouping
3. Select job tile from field expression and group header and
group footer properties will be yes.
4. Then in report design view two section will be created which
is shown in the previous picture.
The output is given bellow.
Salary Sheet
Name: Salary H_rent Medical Total Salary
Clark
Chitra 3000 1200 30 4500
26 City Corporation Computer Institute
MS ACCESS
Omar 3500 1400 35 5250
Clark 9750
Manager
Rana 12000 4800 120 18000
Motale 10000 4000 100 15000
Manager 33000
Officer
Mukta 8500 3400 85 12750
Liton 6500 2600 65 9750
Abu 5600 2240 56 8400
Officer 30900
Grand 73650
Total
27 City Corporation Computer Institute