MS-ACCESS 2021

 

MS-ACCESS 2021

MS-Access is one of the most important parts of MS-Office. This program is used for database management. In this part we are fully acquainted with DBMS. The ‘DBMS’ stands for – Database Management System.

Before we start using Ms-Access and learn about its capabilities, we need to understand what databases are and what meant by Database Management System is.

Data:- Data are the raw materials of information or we can say that all the materials by which we can prepare our information, is known as data. It may be any character, words, facts, symbols or any object.

Database:- Database is a collection of systematic and interrelated data about a certain topic or business application. Such as: - an employee database or a product database. Database helps us to organize related information in a logical manner for easy access and retrieval.

Computer databases can store in different formats, from simple lines of text (such as:- names and addresses) to complex data structures that include pictures, sounds or video images. Databases arrange information in such a way that any specific piece of information can be accessed if a reference point is known.

Book No	Title	Author	Price
101	Godaan	Premchand	130
102	Genhun Aur Gulaab	Shiv Pujan Sahay	80
103	The Great Mystery	Spook Dale	80

Introduction of DBMS

 Data management involves creating, modifying, deleting and adding data in files and using this data to generate reports. The software that allows us to perform, these functions are known as Database Management (DBMS). Thus, DBMS is a collection of data and programs that help us to retrieve this data and perform operations on it. A DBMS user enters data through data entry forms and retrieves it using a query Formatted data in the form of a report is sent to the VDU or a printer.

Data Entry: - Data Entry is the process of entering data through a database management software such as:- Ms-Access, SQL, FoxPro, VB, Oracle and other popular software.

Microsoft Access 2021 is the relational database management system (RDBMS) means the database in MS-Access are totally connected to one another and establish the meaningful relations.

When we start MS-Access first of all we have to create our own database then we can create our table, form, query, modules, macro etc.

Steps to two create your own database – Open MS-Access (By Search Option or by clicking from the program list) – Click on Blank Database/New Database – Click on Browse – Open your folder – Type the name of your Database Name in the file box (Name Database) – Click on ‘Create’.

Assignment –

Create a table for address book.

Hints:-

Open MS-Access – Create your own database/Open your own database – You will be a default table name ‘Table1’ will be available at left opened panel/if it is not available – Click on Create Menu – Click on ‘Table’ – Now create the name of your field – Click to add – Click on ‘Short Text’ -Type your field name – Likewise you can create another more fields – If you have created your all the fields – Now, fill your records in cell below as per the fields’ requirement – Press – Ctrl + W – Click on ‘Yes’ to save – Type your table name (Address Table) – Press – Ctrl + W to close the opened table.

Hints: -

Editing your field name

Suppose you missed to any field name, to add it during entry follow the steps given below: -

Go to the field – Right Click where you want to add your new field – Click on ‘Insert Field’ and type its name by double click on it.

 

 

 

 

Table in MS-Access

Table is the most important part of any database or data entry project. Table is in column and row form as we see in MS-Excel. But, in MS-Access it is quite different. In Excel table is already prepared while in MS-Access we have to create it as per our requirement. In MS-Access table is created as per the requirement of our project.

Part of table

The upper most part of table is called field where we put the heading of a column. It is created by double click on it or Click and click on ‘Short text’. Rows are the part where we fill our data or records. And, the filling up the records in the cell accordance with heading of columns is known as ‘Data Entry’. Generally, ‘Table’ is the main-sheet for the MS-Access and it is the table where we prepare the back-end programming for any data entry. (Cell:- The rectangular part of table where records are fed is known as cell.)

Note:-

1.             When we create any table first of all we have to modify its field’s name.

2.             Remember, in the fields’ creation spaces are not allowed. Instead of them we use underscore (_). Such as:- Employee Name –X, Employee_Name/emp_name

Assignment –

Create a table for the students for a school.

Name of School:- Bal Bharti, Naugachia.

Teachers table

Students Table

Attendance Register for Teacher

Attendance Register of Students Class-wise

Menu of MS-Access

Like all the previous programs MS-Access has also its menu which are as follows:- File, Home, Create, External Data and Database Tool. All tools are very important with the view of developing database programing. Since, in MS-Access we create or develop any type of database programs which are specially query based and form based which look like Visual Basic Programming. In MS-Access we can apply all the Visual Basic commands by using its VBA option.

Create Menu has the following panels:- Templates, Tables, Queries, Forms, Reports, Macros and Code.

Generally, we use ‘Tables’ panel to create the any type of tables. There are three ways to create a table –

·      By clicking on ‘Table’ option and inserting the fields.

·      By using the ‘Table design’.

·      By using SQL (Structured    Query Language) and

·      By using Visual Basic commands.

Generally, Table and Design view are the best tools to create the table in MS-Access because we take the help of SQL command or Visual Basic code we have to be the master these two languages, otherwise it is difficult to create the table through these ways.

How to create a table by using ‘Table Design’?

If we want to create the table through the ‘Table Design’ first of all we have to understand the data types used in MS-Access.

Generally, there are ten types of data types used in MS-Access which are as follows: - Short Text, Long Text, Number, Date and Time, Currency, Auto Number, Yes/No, Ole Object, Hyperlink, Attachment and Calculated etc.

Table Creation by the help of ‘Design View’

In table creation by the help of ‘Table Design View’ we have to follow the following steps:-

Click on ‘Create Menu’ – Click on ‘ Table Design View’ – A double tagged window appears on the screen – The top window is the part where we create our fields as per our requirement  and another window which appears to the bottom side this is the field properties window where we get the settings for our created fields.

The window where we create the field name has three parts –

·      Field Name:- In this part we create our fields according to our use. We create its name like – Emp_Name, Name, Students _Name etc.

·      Data Type:- Data types are already available in indexing form. They are used by selecting at the time of field creation. In data type we find the following data type names –

o  Short Text: - It is selected when our records contains short texts.

o  Long Text:- This is used to apply when our records contains the long texts.

o  Numbers:- It is selected when our records contains up to 1 to 9 numbers long.

o  Date and Time:- It is selected when the format of our records are in date and time.

o  Auto Number:- If we require the automatic numbers in our records we select this data type.

o  Ole: - When we require to fill the photo/pictures/clipart etc as our records we choose this option.

Query (खोज, सवाल करना ):- Query in MS-Access is used for various purposes. But especially, it is used for all types calculations as we use in MS-Excel but the method in Access is quite different and unique. Since, MS-Access is fully based on Relational Databases and SQL (Structure Query Language) therefore, it is necessary to be serious while using the calculations for our Database Tables by the help of Query Designs.

Assignment –

Apply Query Calculation for any Result Sheet.

Hints: -

·      Create a Table for ‘Result Sheet’ – and apply query calculation in Obtained Marks and Full Marks.

 

How to find out sum of result sheet by the help of query?

Steps: -

Create a table for result sheet by the help of ‘Table Design’ – Set the appropriate data type – Close your table – Click on create menu – Click on ‘query design’ – Click on ‘Add’ – Close your ‘Show table’ wizard – Double click on each subject and in which you have to show the total one by one – Click on ‘Update’ tool bar – Right Click below the ‘Total’ added field – Click on ‘Build’- A wizard of expression builder will be appeared on the screen – From the first box (Expression Elements) – Double Click on your database – Double Click on ‘Tables’ – Select your table for that you want to apply the addition – The list of fields will be shown in the expression categories – Click on the upper box – Put the sign of (=) – Double click on each field with plus sign (+) leaving at the last field – Click on ‘OK’ – Click on ‘Yes’ to save the query – Type the name of your query (Total Query) – Select the query from the list and by pressing several enters update it and now open your table, you will see your calculation will be completed.

Assignment –

·      Create Purchase Report

·      Create a sales report.

·      Salary Payslip.

Form Designing

Form Designing is used for creating the graphic user interface and it is the most important part of front-end data entry. It is the same in model as we use Visual Basic Programming. This is very easy and user friendly because it contains the user made menus, buttons and controls. Form designing can be created in five ways –

·      By Clicking on ‘Form’ option

·      By using ‘Form Design’

·      By clicking on ‘Blank Form’

·      By using ‘Form Wizard’ and

·      By using ‘Visual Basic Programming’

Assignment –

·      Create table by using Form. 

·      Create a table by using Form Designing.

·      Create a table by using Blank Form.

·      Create a table by using form Wizard

Hints for Form Designing in MS-Access 2021: -

As we know that Form Designing in MS-Access is the same part as we use in Visual Basic Programming. Form designing is used for developing a program for graphic user interface means by using this we can develop our own program for data entry and we can give a beautiful visual look. Remember, in MS-Access we can develop both sides of programs which are known as –Back End program and Front End program.

Create a Form by the help of ‘Form’ option. Hints- Open your database – Select a ‘Table’ from the list – Click on ‘Create’ – Click on ‘Form’ – An automatic form will be designed as a default -  Save it  and open it now from the list.

Create a form by the help of ‘Form Designing’.

Hints:- Open your database – Select a ‘Table From the list’ – Click on ‘Form Design’ – Click on ‘Add Existing Field’ – Click on ‘Table’ for which you want to create the ‘Form’ – At right side the list will be appeared – Drag it on the from one by one – Set it, size it , design it – Save it and see the effect by closing the master form – Again, if you want readjust – Right click on the heading/title of the form and Click on ‘Design View.’

Controls of MS-Access 2021

Like Visual Basic Programming, MS-Access has also some tools to design the graphic user interface. Graphic user interface means where the external users who will operate the developed program in Access for a particular purpose. As we know that we can develop our own programs in this application. These tools are as follows:-

Label:- Label is used to define the name of the field on the form or by using it we can put the heading of our program.

Text Box:- This control tool is used to insert a text in which record will be inserted by users on developed graphic user interface.

Button:- Button is also called ‘Command Button’. By using this button we can create a personal execute button like – Next, Open, Exit or any calculation button. When we use this button at time of developing it indicates us as a picture button or text button. We can use both of them for our graphic user interface. Steps to create any button – Switch on the design view mode – Click on design view menu – Click on ‘Button’ tool –Drag the mouse on the place where you want to create a command button the page – Here, a window will open name ‘Command window wizard’, here we will get two options in it – Categories and Action- Select the option of categories and click on the option for action as per your requirement – Click on ‘Next’ button – Select text and type the command name or Select the picture – Close your form and open it in data entry mode and see the effect.

Assignment –

Create the following command buttons for  any form designing – Next Record, Previous Record, Close Form, Save, Open, Delete Record, Undo etc. [Note:- For all these we can also set the picture command.]

Tab Control Box:- This is used to create a tab control on graphic user interface area. Steps:- Click on it and drag it on the form and see the effect.

Hyperlink:- This option is used to create a hyperlink like web page on our graphic user interface. Steps:- Switch on the ‘Design View Menu’ -  Click on ‘Hyperlink Control Box’ – Open your old file and select what you want to connect – Click on Ok and see the effect in run mode.

Assignment –

1.              Create an admission table.

2.              Create a particular Bio-Data in Word for each recoded name.

3.              Create a particular address in word for that record.

4.              Create the admission in Form designing.

5.              Create hyperlink for – Bio-Data, Address,

Web Browser Control: - This is used to add a particular web browser and it can be controlled from the graphic user interface.

Navigation Control:- This is used to create navigation button as we get on any opened or running . For this – Go to ‘Design View’ – Click on Navigation Control’ – Drag your mouse on the form – Add navigation button and type the name of navigation and run your program.

In MS-Access, navigation button can be created in various modes:-

1.              Horizontal Navigation Button:- This will be create by using the following steps:- Go to the ‘Design View’ – Click on Create – Click on ‘Navigation Button’ – Click on ‘Horizontal Tab’ – Add the name of button by typing and press ‘Tab’ to create the new column – Save it and see the effect.

2.              Vertical Navigation:- In Vertical Navigation there are about four types. The steps will be same only options will be selected as vertical and mode will be depended upon our choice.

Note:-

1.              Normal Tables which are created by using table design view are in ‘Datasheet Mode’.

2.              Suppose, we have a created form in our database and we want to convert it into ‘Form to Datasheet’, use the steps as given below:- Select the table – Click on ‘Create’ menu – Click ‘More Form’ – Click on ‘Datasheet’.

Reporting of database record

In MS-Access ‘Reporting’ is the final part of database. In ‘Report’ creation we finalize the important things which are important to be printed. Reports can be prepared for the created tables, for the queries, for the form also. It is something same as we create the ‘Form Designing’ in Access. So far as the ‘Report Creation’ in MS-Access’ 2021 is concerned it is created in three ways:-

1.             Report

2.             Report Design and

3.             Blank Report.

How to use ‘Report’ option to create a report?

Steps:- Select any table what you have created before – Click on ‘Create Menu’ – Click on ‘Report’ – Here, a report will automatically generated – Save it and Close. Now, open form the report panel and see it.

SYNTAX OF CREATE TABLE THROUGH SQL IN MS-ACCESS: -

Syntax: - Create Table <table name>

(

<column_name1> <datatype>(<size>), <column_name2> <datatype>(<size>)

);

Note: -

a.  The CREATE TABLE command defines each column of the table uniquely. Each column has minimum of three attributes, a name, datatype and size. Each table column definition is a single clause in the create table syntax. Each table column definition is separated from the other by a comma. The SQL statement is terminated with a semi-colon.

b. All the table columns belong to a single record. Therefore, all the table column definitions are enclosed within parenthesis.

c.   Fundamental of Table –

a.  A table is database object that holds user data. This is in spreadsheet mode. The cell of the spreadsheet equates to the column of a table having a specific datatype associated with them.

b. Datatype of SQL/ORACLE

                                                           i.      CHAR(size) – This datatype is used to store the character strings value fixed length. The maximum number of it is 255 characters.

                                                        ii.      VARHCAR (size) – This datatype is used to store the variable length alphanumeric data. The maximum number of it is 2000 characters.

                                                    iii.      DATE – This is used to store to modify the date.

How to edit your table through SQL when it is created?

In navigation window check the ‘Query’, your created query will by visible, here we see our created item – Right click on it – Click on ‘Design view’.

How to insert value to the table record?

Syntax: -

INSERT INTO <TABLE NAME> VALUES

(RECORD ONE, RECORD TWO,……..);

 

       How to create the indexing in MS-Access?

Generally, indexing is applied to the table creating in MS-Access. Suppose, we have to create the indexing for ‘Village/Town’ – Choose ‘Lookup Wizard’ as Data Type – Select ‘I will type in the values that I want’ – Click ‘Next’ and type the name of villages or town one by one and click the ‘Next’/ ‘Finish’ – Save it.

How to edit the created indexing items?

Open the table in ‘Design View’ – Click on its ‘Data Type’ – Open ‘Lookup’, here you will see the list of created items or Click on …..and edit the items.

Project Work

IT Professionals

Create the following tables: -

1. Invitation Cards Distribution

a.  Executive_Codes

b. Executive_Name

c.  Executive_Full_Address

d. Mob_No

e. Adhar_Card

f.    Pan_No

g.  No_Invitation_Cards

2.  Enquiry_For_IT_Professionals

a.  Enq_SLNO

b. Name

c.  Full_Address

d. Mob_No

e. Date

f.    Possible_Adm

g.  General_Course

h. IT_Professional

 

Field Name	Data_Type	Record
Name	Text	Rakesh
Age	Number	35
DOB	Date and Time	12/12/1985
Basic_Salary	Number	15000
Working Company Name	Text	ETC_WORLD
Photo	Ole_Object