Access Basics

 Before going to Access, let's have brief understanding about what data is ? When we try to understand the real meaning of data, we can find various descriptions and acronyms. Below are few of them.

Data

factual information (such as measurements or statistics) used as a basis for reasoning, discussion, or calculation

https://www.merriam-webster.com/dictionary/data

Data are characteristics or information, usually numerical, that are collected through observation.[1] In a more technical sense, data is a set of values of qualitative or quantitative variables about one or more persons or objects, while a datum (singular of data) is a single value of a single variable.

https://en.wikipedia.org/wiki/Data

Computer data is information processed or stored by a computer. This information may be in the form of text documents, images, audio clips, software programs, or other types of data. Computer data may be processed by the computer's CPU and is stored in files and folders on the computer's hard disk.

https://techterms.com/definition/data

any set of characters that is gathered and translated for some purpose, usually analysis. If data is not put into context, it doesn't do anything to a human or computer.

 Below are tyes

• Single Character
• Boolean
• Text
• Number
• Picture
• Sound
• Video

Example for data

Data manipulation

process of changing data to make it easier to read or be more organized.

• Ex: register -  names of pupils store in alphabetical order -> easy to locate
• Accounts - manipulate data to figure out product costs, sales trends, or potential tax obligations.
• Stock market analysts are frequently using data manipulation to predict trends in the stock market and how stocks might perform in the near future
• Computer -  data manipulation to display information to users in a more meaningful way
• 23062020 --> in excel =DATE(YEAR(B14)+4,MONTH(B14)+5,DAY(B14)+3)
• In a program 23rd of June 2020

How to Control ?

• Need to Store
• Where ?
• As files – inside folder
• Database

What is Database

collection of information that is organized so that it can be easily accessed, managed and updated. Computer databases typically contain aggregations of data records or files, containing information about sales transactions or interactions with specific customers.

• Oracle , MySQL , DynamoDB , Aurora …….

MS Access

• Database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.
• Like Relational DB – let link relational info easily
• Customer and order data
• import or link directly to data stored in other applications and databases.
• support the Open Database Connectivity (ODBC) standard, including SQL Server, Oracle, and DB2.
• work directly with data from other sources
• understand and use a wide variety of other data formats
• export data to and import data from word processing files, spreadsheets, or database files directly

Access

MS Access Objects

• Basic Objects
• Table
• Form
• Query
• Report
• Other
• Macro
• Module

Sample

Table

• Each field must have a unique name, and data type.
• Tables contain fields or columns that store different kinds of data, such as a name or an address, and records or rows that collect all the information about a particular instance of the subject, such as all the information about a customer or employee etc.
• You can define a primary key, one or more fields that have a unique value for each record, and one or more indexes on each table to help retrieve your data more quickly.

Query

• Running a query is like asking a detailed question of your database.
• When you build a query in Access, you are defining specific search conditions to find exactly the data you want.
• In Access, you can use the graphical query by example facility or you can write Structured Query Language (SQL) statements to create your queries.
• You can define queries to Select, Update, Insert, or Delete data.
• You can also define queries that create new tables from data in one or more existing tables.

Form

• Forms are used for entering, modifying, and viewing records.
• The reason forms are used so often is that they are an easy way to guide people toward entering data correctly.
• When you enter information into a form in Access, the data goes exactly where the database designer wants it to go in one or more related tables.

Report

• You can view a report on your screen before you print it.
• If forms are for input purposes, then reports are for output.
• Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period, or a set of mailing labels.
• Reports are useful because they allow you to present components of your database in an easy-to-read format.
• You can even customize a report's appearance to make it visually appealing.
• Access offers you the ability to create a report from any table or query.

Create DB

Let's create from Blank template.

Give a name for file to save.

After that we get Access preview desktop. You can create tables, forms , Reports ....etc

Before create going further, let's have a quick look look over the Data types available. Below are a short description

Create Table

Click  Table icon under Create menu

You will get a bank table like below

Add fields --> click to Add ( select data type )

        

Give name eg: first Name  and you can start adding more fields

Add field to table ...

• Rename primary key
• Select “ID” auto name field
• Click “Name and Caption”
• Enter field properties
• Finally column rename

     

Table – design view

Right Click table “Design View”

Select Primary Key

• Right click and select “primary key”
• Icon – key – indicating primary key

• Go back to Data sheet view
• Right click
• Design View
• Data Sheet view

Query Data

• Click “Query Design”
• Double click “Student” table
• Table you need

Close “Show Table ” panel

Add the fields

Click “Run”

Query Result

• You will get query result

• Different views
• Right click
• Design view
• SQL view
• Data sheet view

Relating Tables or Data

Normalization

• process of organizing columns (attributes) and tables (relations) of a relational database to minimize data redundancy.

Relationships

• Matching data in key columns usually columns with the same name in both the tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table. 
• One to Many
• Many to Many
• One to One

Normalization

Processes of reducing the redundancy of data in the table and also improving the data integrity. 

So why is this required? 

We face issues 

• Insertion anomaly: It occurs when we cannot insert data to the table without the presence of another attribute
• Update anomaly:  It is a data inconsistency that results from data redundancy and a partial update of data.
• Deletion Anomaly: It occurs when certain attributes are lost because of the deletion of other attributes.

• End --> large table will  break into small tables

• 1st Normal
• Cannot hold multiple values – remove duplicates
• 2nd Normal
• 3rd Normal
• 4th Normal - BCNF

Example