Unit 8.

Database
MS-Access(Table, Query, Form and Report)
Database
• Data refers to raw facts, figures, and symbols that are collected, stored, and processed to
generate meaningful information.
• A database is a structured collection of data that is organized and stored in a way that allows
efficient retrieval, management, and update of information.
• It serves as a central container for storing and managing data in a systematic manner.
• Databases are designed to support the electronic storage and retrieval of data, making it easier
to organize, analyze, and manipulate information.
• They consist of tables, which contain rows and columns, each representing a record with
specific attributes.
• The relational database model, one of the most common types, establishes relationships
between tables to ensure data integrity and consistency.
• Databases play a crucial role in various applications, from business systems to web
applications, by providing a reliable and scalable means of managing vast amounts of
information.
Database Management System(DBMS)
• A Database Management System (DBMS) is software designed to efficiently and
securely manage, organize, and facilitate the storage and retrieval of data in a database.
• It serves as an interface between the database and the users or application programs,
allowing them to interact with the data without dealing with the complexities of
storage and retrieval.
• The primary functions of a DBMS include data definition (defining the structure of
the database), data manipulation (inserting, updating, and deleting data), data
retrieval (querying and retrieving information), and data administration (ensuring
data integrity, security, and managing access control).
MS-Access
• Microsoft Access is a Database Management System offered by Microsoft. It comes as
a part of the Microsoft Office suite of application.
• Microsoft Access offers the functionality of a database and helps you analyze large
amounts of information, and manage data efficiently.
• It allows users to organize data into tables, establish relationships between tables, and
perform complex queries to retrieve specific information.
• With features like forms and reports, users can create customized interfaces for data
entry and generate formatted outputs for analysis and presentation.
• Access is particularly well-suited for small to medium-sized projects, offering a
balance between simplicity and functionality.
 Basic Objects of the Database
• Database File
• It is a file which stores the entire database. The database file is saved to your hard drive or
other storage devices.
• Datatypes
• Datatypes are the properties of each field. Every field has one datatype like text, number,
date, etc.
• Table
• A Table is an object which stores data in Row & Column format to store data.
• A Table is usually related to other tables in the database file.
• Each column must have Unique name
• We can also define Primary Key in a table.(Primary key: It is the means to uniquely
identify each record within that table. The primary key must be unique for every record,
and it cannot contain null values.)
• Query
• Queries answer a question by selecting, sorting and filtering data based on search criteria.
• Queries show a selection of data based on criteria (limitations) you provide.
• Queries can pull from one or more related Tables and other Queries.
• Types of Query can be SELECT, INSERT, UPDATE, DELETE.
• Form
• A form is a database object that you can use to create a user interface for a database application.
• Forms help you to display live data from the table. It mainly used to ease the process of data
entry or editing.
• Report
• A report is an object in desktop databases primarily used for formatting, calculating, printing,
and summarizing selected data.
• You can even customize the report’s look and feel.
Microsoft Access Datatypes
 Difference between MS Excel and Ms Access
• Data Analysis:
• Excel: Primarily used for data analysis, calculations, and creating charts. It is suitable for smaller datasets and individual
data analysis tasks.
• Access: Designed for managing and querying larger databases, making it more suitable for scenarios where data
relationships and complex queries are essential.
• Usage:
• Excel: Ideal for tasks like budgeting, financial analysis, simple databases, and creating visual representations of data.
• Access: Suitable for scenarios requiring structured data storage, relationships between tables, and advanced querying
capabilities. It is often used for creating and managing databases with multiple related tables.
• Data Relationships:
• Excel: Limited support for managing relationships between datasets. Data is often organized within the same worksheet.
• Access: Provides robust support for defining and managing relationships between tables, making it suitable for handling
complex data relationships.
• Querying:
• Excel: Supports basic filtering and sorting functions. PivotTables and PivotCharts offer more advanced data analysis
features.
• Access: Designed for creating complex queries using SQL (Structured Query Language). Users can create queries to
extract specific information from the database.
Advantages of Ms Access
• User-Friendly: Microsoft Access is easy to use, even if you're not a computer whiz.
You can organize your information and do stuff with it without needing to be a tech
expert.
• Quick to Start: You can make databases fast using templates and tools that are
already set up for you. This saves time because you don't have to start everything from
scratch.
• Works with Other Microsoft Stuff: Access plays nice with other Microsoft programs
like Excel, Outlook, and Word. You can move your data between them easily, which
makes your work smoother.
• Can Grow with You: Access can handle both small and big databases. So, if you start
with a small project but need more space later, Access can handle it. You can connect it
to fancier systems like SQL Server if you need to do more later on.
Advantages of Ms Access
• You Can Make it Yours: You can change Access to fit your needs. You can make it
look the way you want and do special things like forms, reports, and automatic tasks.
• Lots of People Can Use It Together: Access lets many people work on the same
database at the same time, even if they're not in the same place. This is great for teams
and projects where lots of people need to work together.
• Does Fancy Reports and Analysis: Access helps you make cool reports and
understand your data better. You can see your information in charts and graphs, which
makes it easier to figure out what's going on and make smart decisions.
Disadvantages of Ms Access
• Can't Handle Huge Amounts of Data: Access struggles with really big databases. It
might slow down or crash if you put too much information into it.
• Not the Safest Option: It's not as secure as some other databases. If you have really
important or secret information, Access might not keep it as safe as you need.
• Not Great for Really Tricky Stuff: If your database needs to do really complicated
things, Access might not be able to handle it. It's better for simpler projects.
• Needs Other Microsoft Software: You have to have other Microsoft programs to use
Access. If you prefer different software or use a different kind of computer, Access
might not work well for you.
Disadvantages of Ms Access
• Not the Best for Working Together: While a few people can use the same database in
Access, it's not super good for teamwork. It doesn't have all the fancy features for
working together smoothly.
• Gets Slow with Lots of Data: Access can get sluggish when dealing with lots of
information or when doing complicated searches. This can make it frustrating to use.
• Not Good for Websites: Access is mainly for computers, not for making websites. If
you want to make an online database or have lots of people using it on the internet,
Access might not be the right choice.
Important Components/Objects of Ms Access
• Tables
• Form
• Query and
• Report
Tables
• Tables are the foundation of any database in Microsoft Access. They store data in rows
and columns, similar to a spreadsheet. Each table consists of fields (columns) and
records (rows). Fields define the type of data that can be stored, such as text, numbers,
dates, or attachments.
• Creating tables in Access is straightforward using the Table Design View or by using
pre-designed templates. Users can define relationships between tables to establish
connections between related data. Access provides various data types and properties to
customize tables according to specific requirements, ensuring data integrity and
consistency.
Creating Tables:
• Table Design View:
• In Access, you can create tables using the Table Design View. This view allows you
to define the structure of the table by specifying the fields (columns) and their
corresponding data types.
• Datasheet View:
• Alternatively, you can input data directly into a table using the Datasheet View. This
view resembles a spreadsheet and allows you to enter and edit data in a familiar grid
format.
Table Components:
• Fields:
• Fields represent the columns in a table and
define the type of data that can be stored,
such as text, numbers, dates, or
attachments.
• Each field has specific properties that
control its behavior, such as data type, field
size, and validation rules.
• Records:
• Records are the rows in a table and contain
the actual data.
• Each record corresponds to a unique entry
in the table and consists of values for each
field defined in the table structure.
Defining Relationships:
• In relational database design, tables often have
relationships with one another.
• Access allows you to establish relationships
between tables using primary keys and foreign
keys.
• Primary keys uniquely identify each record in a
table, while foreign keys establish links
between tables by referencing the primary key
of another table.
2. Forms
• Forms in Microsoft Access provide a user-friendly interface for entering, viewing, and
modifying data in tables.
• They allow users to interact with the database without directly accessing the
underlying tables. Forms can be customized using design tools to create a layout that
suits the application's needs.
• Access offers several types of forms, including bound forms that are linked to a
specific table or query and unbound forms that do not directly interact with the
database.
• Users can add controls such as text boxes, buttons, and drop-down lists to forms to
enhance functionality and improve usability.
Purpose / Uses of Form
• Data Entry: Forms provide a convenient way for users to input data into tables. They
present fields in a clear and organized layout, making it easy for users to enter
information accurately.
• Data Viewing and Editing: Users can view and edit existing data stored in tables or
queries using forms. Forms display data in a readable format and allow users to make
changes to individual fields or records.
• Navigation: Forms often include navigation controls, such as buttons or tabs, to help
users navigate through different records or sections of the database efficiently.
• Data Presentation: Forms allow users to customize the presentation of data by
specifying layout, formatting, and styling options. This enables users to create visually
appealing and intuitive interfaces tailored to their specific needs.
Designing Forms
• Data Entry: Forms provide a convenient way for users to input data into tables. They
present fields in a clear and organized layout, making it easy for users to enter
information accurately.
• Data Viewing and Editing: Users can view and edit existing data stored in tables or
queries using forms. Forms display data in a readable format and allow users to make
changes to individual fields or records.
• Navigation: Forms often include navigation controls, such as buttons or tabs, to help
users navigate through different records or sections of the database efficiently.
• Data Presentation: Forms allow users to customize the presentation of data by
specifying layout, formatting, and styling options. This enables users to create visually
appealing and intuitive interfaces tailored to their specific needs.
3. Query
• Queries in Microsoft Access are used to retrieve, filter, and manipulate data stored in
tables.
• They enable users to extract relevant information from large datasets based on specific
criteria.
• Access provides a Query Design View where users can visually create queries using a
graphical interface.
• Users can build simple or complex queries by specifying criteria, sorting options, and
calculated fields.
• Access supports various types of queries, including select queries, parameter queries,
action queries, and crosstab queries.
• Queries are essential for data analysis and generating meaningful insights from the
database.
Purpose/ Uses of Queries
• Data Retrieval: Queries enable users to retrieve specific subsets of data from one or more
tables or queries in the database. Users can specify criteria to filter records based on
certain conditions, such as date ranges, text matches, or numerical comparisons.
• Data Manipulation: Queries allow users to manipulate data retrieved from tables or
queries, such as sorting records, performing calculations, and combining fields from
multiple sources. Users can create calculated fields, concatenate text strings, and perform
mathematical operations within queries to transform and analyze data.
• Data Analysis: Queries facilitate data analysis by summarizing and aggregating
information to generate reports, charts, or other visualizations. Users can create summary
queries to calculate totals, averages, counts, or other aggregate functions across groups of
records.
• Data Maintenance: Queries can also be used to update, delete, or append records in tables
based on specified criteria. Users can execute action queries to perform bulk operations
on data, such as updating prices, deleting outdated records, or adding new entries to a
table.
Types of Queries
• Select Queries: Select queries retrieve data from one or more tables or queries based
on specified criteria. They are commonly used for retrieving information to display on
forms or reports.
• Parameter Queries: Parameter queries make users to input values for specified criteria
when the query is executed. This allows users to dynamically filter data based on user-
defined parameters, such as dates, names, or product IDs.
• Action Queries: Action queries perform data modification operations, such as
updating, deleting, or appending records in tables. Examples of action queries include
update queries, delete queries, and append queries.
• Crosstab Queries: Crosstab queries summarize data by displaying it in a matrix format
with rows and columns representing different categories or groups. They are useful for
analyzing data across multiple dimensions and generating summary reports.
Creating Queries
• Queries in Access can be created using the Query Design View or the Query Wizard.
In the Query Design View, users can visually design queries by selecting tables,
adding fields, and specifying criteria and sorting options.
• The Query Wizard provides step-by-step guidance for creating common types of
queries, such as select, crosstab, or parameter queries.

Executing Queries:
• Once created, queries can be executed by running them from the Query Design
View or by opening them directly from the Navigation Pane.
• Access retrieves data based on the query criteria and presents the results in a
datasheet or other appropriate view.
4. Reports
• Reports in Microsoft Access allow users to present data in a structured and
professional format.
• They are used to summarize, analyze, and visualize information stored in tables or
queries.
• Access provides a Report Design View where users can design custom reports using a
range of formatting and styling options.
• Users can add headers, footers, groupings, and calculations to reports to organize data
and highlight key insights.
• Access supports exporting reports to various formats such as PDF, Excel, and Word for
easy sharing and distribution.
• Reports play a crucial role in communicating data findings and supporting decision-
making processes.
 Purpose/ Uses of Reports
• Data Presentation: Reports provide a formatted and visually appealing way to present data
to users, making it easier to understand and interpret. They can include headers, footers,
titles, and other design elements to enhance readability and professionalism.
• Data Analysis: Reports can summarize and analyze data by aggregating information,
calculating totals, averages, counts, or other statistical measures. Users can create summary
reports to display key metrics, trends, or patterns in the data.
• Data Visualization: Reports allow users to visualize data using charts, graphs, and other
graphical elements. Users can create bar charts, pie charts, line charts, or other types of
visualizations to represent data in a meaningful and informative way.
• Data Export: Reports can be exported to various formats, such as PDF, Excel, Word, or
HTML, for sharing and distribution to stakeholders. Users can generate reports in different
formats to meet specific needs or preferences.
Types of Reports
• Tabular Reports: Tabular reports present data in a simple table format, with rows and
columns displaying individual records and fields from the underlying data source. They
are suitable for displaying detailed information with minimal formatting.
• Grouped Reports: Grouped reports organize data into groups based on specified criteria,
such as category, region, or date. They include group headers and footers to provide
summary information for each group, making it easier to analyze data by different
categories.
• Summary Reports: Summary reports aggregate data to calculate totals, averages,
counts, or other summary statistics across groups of records. They are useful for
generating high-level overviews and insights into the data.
• Cross-tab Reports: Cross-tab reports display data in a matrix format, with rows and
columns representing different categories or groups and intersecting cells showing
summarized data. They are suitable for analyzing data across multiple dimensions and
identifying trends or patterns.
Creating Report:
• Reports in Access can be created using the Report Design View or the Report Wizard.
• In the Report Design View, users can visually design reports by adding fields, labels, and
other controls to the report canvas.
• The Report Wizard provides step-by-step guidance for creating common types of reports,
such as tabular, grouped, or summary reports.
Customizing Reports:
• Once created, reports can be customized to meet specific requirements by adjusting layout,
formatting, and styling options.
• Users can add headers, footers, logos, and other design elements to enhance the appearance
of the report. Access provides a range of design tools and features to customize reports
according to user preferences.
Printing and Viewing Reports:
• Reports can be printed directly from Access or previewed in Print Preview mode to review
the layout and formatting before printing.
• Users can also view reports on-screen or export them to other formats for sharing and
distribution.