MS Office Access

Ø  What is data?

Data is presentation of fact or information or information about a particular entity.

Ø  Database management system (DBMS).

A database is a calculation of data and object related to a topic or purpose. Within a database file we can divide pure data and store it in different table. EX:foxpro,database etc.

What is a Relational Database?

A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables.

What is MS Office access?

MS Office Access is relation database management system (RDBMS). That we can use to store and manipulate large amount of information.

Ø  Full form of SQL:              Structured Query Language

Ø  The extension name of MS access 20016: .accdb

Ø  How to create database?             At first open MS Office access ⟶  click blank database(OR, go to file menu→click new)→write database name→create

Ø  How to create table?     Go to  create tab→table→right click on table →design view→ write table name →OK →write field name→choose datatype→Ctrl+W→yes

How to open table?        Double click on table/ right click on table→view

Ø  How to open design view?          Right click on table →design view

Ø  Data window

This window appears when we open an MS Office Access database or an MS Office Access project, it displays shortcuts for creation new database object and opening existing objects.

Ø  Design view window

Windows that shows the design of object, light table, queries, forms, reports etc. in design view, we can create new database object and modify the design of existing note.

Ø  Data sheet

This window display data from a table, query view all store procedure in a row and column format. In database view, we can edit fields and delete data and search for data.

Ø  Field

A field object represent a column of data with a column data with a column data type and common set of properties.

Data type of MS Office access

I.                     Short Text: Used for store alphanumeric data up to 255 characters.

II.                   Long Text: the memo data type hold up to 64000 text characters.

III.                 Number: any number can be stored.

IV.                Date/time: used to store date and time.

V.                  Currency: it is used for monitory value.

VI.                Auto number: a field which generate number automatically.

VII.               Yes/no: a vary compact data type, it is used for logical works.

VIII.             OLE objects: store ole (object linking and embedding) object graphics or other binary data.

IX.                 Hyperlink : these field hold hyper link address that jump websites, data base object or other files.

X.                  Lookup wizard: a look up field starts a wizard that places constraints on a field, on a query.

XI.                Attachment: we can attach images, spread sheet and other type of supported file to the record in our database. Attachment fields provide greater flexibility then ole object fields and they use storage place more efficiently.

  Concepts of different types of keys

Ø  Candidate key:

An attribute that uniquely identify a record/row, is called candidate key. Roll, E_ID, are that candidate key.

Ø  Primary key:

One or more field (column) whose value uniquely identify each record in a table. A primary key can not allow null value and must always have a unique index. A primary key used to related a table to foreign key in other table, (it is a candidate key whose value will not be at any record of the table in other word, it is the field of the value to identity the record is must not contain a null value)

Ø  Alternate key:

If there are multiple candidate keys in the table then the candidate keys that are not chose as primary key. This field may contain a null value.

Ø  Foreign key:

One or more field (column)  that refer to the primary key field in another table. A foreign key indicate how the tables one related (it is a primary key of one table is available as an attribute in another table is called as foreign key).

Ø  How to rename table?  Right click on table→rename→write new name→click outside

Ø  How to delete a table?  Right click on table→delete →yes

Ø  How to remove primary key?     Go to table design view→right click on primary key field→primary key

 

Ø  How to set look up wizard data type?    Select look up wizard data type in table design view → it will open a wizard window→then click I will type in the values that I want →Insert course names → next →finish

Ø  How to insert hyperlink data type?         Open table→right click on hyper link data type cell→hyper link→edit hyperlink→select your file →OK

Ø  How to show hyper link data?   Click hyperlink data →yes

Ø  How to insert OLE Object data?                 Open table→right click on OLE Object datatype cell→insert object→create from file→brows →select picture/any file→OK→OK

Ø  How to show OLE Object data?                 Double click on OLE Object data / right click on OLE Object data→package object→active contents

Ø  How to insert attachment data type?     Open table→right click on attachment→datatype cell manage attachment→add→ select file→open →OK

Ø  How to show attachment data?                                Double click on attachment data→select file name →open

Ø  Relationship:     3 types of relationship i)one to one ii)one to many iii)many to many

One to one:        at first create two tables→close all tables(do not write any data in table)→go to database tools→relationship→select table from show table→add →close→drag the parent table(students) primary key field(S_No) to child table(results) primary key field(S_No)→check on three check box 

 

→create

(do not write in child table, fill up child table by clicking ‘+’ sign in parent table)

Ø  One to many:

Ø  Many to many:

 

Ø  Input marks: go to table design view→select field name→go to field properties→click input marks→write your marks          EX.-  Reg no:-    input marks:"333-2353"9999

Note:    9 for number only, a for text only

Ø  Validation rule:      go to table design view→select field name→go to field properties→click validation rule→write your criteria→click validation text→write your text EX.-  Roll:-    validation rule:>=0 and <=20                validation text: "write your text up to 20"

Ø  How to import MS Office access database in your own  database?              Go to external data tab→click access under import & link  group →click brows→select any database→open →OK→select any table→OK→close

Ø  How to import excel file in your own database?                At first create a file in excel→go to external data tab in MS Office access→click excel→brows→select your excel file→open→OK→next →next→next→click no primary key→write table name → finish →close

Ø  How to import text file in your own database?  At first go to note pad→write your data→separate each data with coma(tab/semicolon/coma/space) →go  to external data tab inn MS Office Access → click text file under import & link group→brows→select your file→open →OK→click delimited-character such as coma or tab   each field→next →click coma→first row content field names →next → next→click no primary key →next→finish →close

Ø  How to export your database table in excel?      At first select your table→go to external data tab→click excel under export group→OK→close

 

Ø  Form design:    

at first   create a blank table→do not wrte any data in table→go to create tab→click form design→click add existing fields→select your table→double click in your field name→click button under controls→select button in your design area→select category under command button wizard→select action→next →select text/pic→finish→Ctrl+W→write form name→OK

Ø  Query :                 query is a process to search or modify of any information of any criteria from a table

Ø  Types of query:

o   Select query

o   Action query: types of action query

§  Update query

§  Make table query

§  Delete query

§  Append query

o   Parameter query

o   Aggregate query

Press Ctrl+W→yes

Ø  How to open query design?     At first click create tab→click query design under queries group→select table name→click add→close

Select query

Ø  How to create select query?       At first click create tab→click   query design→select your table name→add→close

→run →yes→query name

Ø  Create query when student name start with A(name) or address=lalbag:              at first click create tab→click query design→select your table name→add→close→

→run →yes→query name

Ø  Display five character in name or address <>berhampore:          

at first click create tab→click query design→select your table name→add→close→→run →yes→query name

Ø  Display all student’s name address where roll 1 to 3:     

Ø  Display all student’s name, roll ,address whose name start with B:

Ø  Display all student’s name, roll ,address whose name start with B and 5 characters:

Update query

Ctrl+W→yes

Go to create tab→click query design→select table(salary)→add →close→click update query→

→run → yes→query name

Ø  Change department name ACC to ACCT:

Ø  Update query using iif: 

Go to create tab→click query design→select table→add →close→click update query →add your field(Connection)→

Update to: IIF([Phone_number] like "34*","land", IIF([Phone_number] like "99*","airtel", IIF([Phone_number] like "93*","reliance", IIF([Phone_number] like "97*","vodafone"))))

Make table query

Go to create tab→click query design →close→click make table query →write your new

table name→OK→

→Ctrl+W→yes→query name

Delete query

Ø  Go to create tab→click query design→select table(salary)→add →close→click delete query→

 → Ctrl+W→yes→query name

Append query

Append query uses To shifting data one table to another table

at first create two table (do not enter data in second table) →go to create tab→click query design→select your first table(student2) from show table→add→ close→add your field name→click append query→select second table name(bappa1)→OK→select field name from append to drop down box→OK→Run(and save the query)

Parameter query

Create a parameter query

Creating a parameter is similar to adding a normal criterion to a query:
1. Create a select query, and then open the query in Design view.

2. In the Criteria row of the field you want to apply a parameter to, enter the text that you want to display in the parameter box, enclosed in square brackets. For example, [Enter the start date:]

3. Repeat step 2 for each field you want to add parameters to.
When you run the query, the prompt appears without the square brackets.

Fill in the value you’re looking for, and then click OK.

You can use multiple parameters in a criterion. For example, Between [Enter the start date:] And [Enter the end date:] will generate two prompts when you run the query.

 Create a select query, and then open the query in Design view.
In the Criteria row of the field you want to add a parameter to, type Like "*"&[, the text that you want to use as a prompt, and then ]&"*".

When you run the parameter query, the prompt appears in the dialog box without the square brackets, and without the Like keyword or wildcard characters:

Aggregate query

 Cross tab:           at first click create tab→click query design→select your table name→add→close→click cross tab→

Run→yes

Ø  SQL View:           at first click create tab→click query design→close→click SQL view→

Create Table [EMP] ([Roll] text,[Name] text);       →Run