Microsoft ACCESS Tutorial | All you need to know about MS Access

What is MS Access?
MS Access-RDBMS
Understanding the Relational Database Concept
Components of MS Access –Objects
How to create a Database?
MS Access –Data Types
MS Access –Create Tables
All About The Primary Key
Query Data And Query Criterion
RELATING DATA
WILDCARDS
Queries and filters
Difference between Access and Excel

Let us dive into this MS Access tutorial and learn more about

What is MS Access?

MS Access is a Microsoft-based relational database management software. It is a part of the a suite of applications provided by Microsoft Office. It stores data in its own format.

Since the onset of computers, back in the early 1950s, businesses have wanted to use computers for storing data and manage their stored data when required. This led to the generation of databases. But the problem with the early databases was, all the data was stored in file format, and it was very difficult to find and relate 2 data sets as and when required. This gave rise to the concept of RELATIONAL DATABASE (RDBMS).

RDBMS, since its discovery, was implemented by many tech giants like IBM, ORACLE etc. So, the same amount of research work was going on in Microsoft in the 1980s, and after a lot of planning and prior testing, Microsoft launched MS Access on 13th November 1992. Like most RDBMS softwares, it allows users to get the maximum benefit from their data by allowing some incredibly easy to perform tasks like sorting, comparing, basic operations like searching and replacing and much more.

Microsoft developed a database engine called Microsoft jet database engine, which helped to store the data at the backend. MS Access, since then, is undoubtedly one of the best RDBMS software that is available in the market today. It has objects which will help you to perform operations and store data. It has a lot of advantages, especially handling complex queries. As a software, MS access follows split database architecture, which means, whatever data we are storing into the tables are stored in the backend in one file, and all other things, which includes the forms, queries, reports etc., are stored in the other file, which helps to easy data fetching and smooth operations on to them.

In terms of security and data privacy, MS access is something that a user can trust blindly. It offers data protection by using passwords and prevents any unauthorized access to the database. Also, you can lock your data and limit the accessibility of who can view a particular section of the data. This has been of great help to most users as someone won’t wish to reveal some sensitive information to anyone who wishes to view it.

As an overall analysis, I could say that it is one of the best possible options available for all medium and small enterprise-level users and who wish to get such service at a very affordable price segment.

MS Access- RDBMS

When you used a database program like access back in 2016, you can’t just start entering data. Instead, you need to create a relational database design, dividing your information into one or more tables, each made up of fields (which are like any columns in a spreadsheet). But now, since the RDBMS is set, these issues have been addressed. RDBMS have a particular model which will allow a user to define what category of data he wants to store, how he wants to store and what sort of operations he wishes to have on the stored data.

Also, at times, we need to have control over who can access our data and who all can have a look at the data. Sometimes, we need to store our data so that only a few allowed people would be able to view a few sensitive sections of the data. This can be controlled by using the MS access data control feature. Access RDBMS. No matter how complex your queries are, they can still process your queries. The main need to develop an RDBMS was when traditional databases failed to address a few situations like drawing relations between 2 stored tables or values. So, a whole new set of logic systems was designed, and it was called relational algebra. That was how it all began. MS access is now one of the most used and most sought after enterprise-level database software preferred by most professionals.

Below is an example of an ms access RDBMS model.

MS access 2016 version

In all ways, MS access RDBMS is one of the best ways to increase data handling efficiency.

Understanding the Relational Database Concept

This is the theoretical model upon which all the databases work in today’s era. So, why am I stressing more on this particular concept is because, if you understand this concept well, then not only you would be able to grasp the technical aspects in a better way, but also, you will have a great working opportunity in future if you choose a career as a database administrator. So, why were we required to develop this technology in the first place?

Because there was a very serious issue of data duplicity. You may think that, even if duplicate data occurs, so what? It can be deleted later on. But let me ask you then, what if you have the same record of data that is repeated (duplicated) 100 times. Now, if at one instance, you need to change the data for that particular field, then individually, you would need to change the data for almost all 100 fields! Which is a very daunting task. Data should be precise enough without any significant redundancy (in technical terms, data duplicity is called redundancy).

So that’s why we need a relational database model, which could help us solve these real-life issues. Now, here comes another important point. If a certain record of data is repeated too many times, and it is unnecessarily taking up too much space on the hard disk of the computer, or if you store it in a cloud-based storage system, in both ways, your storage is wasted in some other manner. It is a wastage of computational resources.

Ever wondered why we write better algorithms? Is it just to bring some newness into an already existing problem-solving technique? No. Let me explain, computational resources are very much limited (since we don’t have a computer with infinitely large RAM and infinite hard disk). So that’s why we need better algorithms, and in terms of core computer science terminology, we say that we need a better time-space tradeoff. The same analogy goes in the case of databases. We require some technique, which is very much efficient in managing computational resources. So, in the digital world, we are limited to such resources, and hence redundancy can cost too much.

Components of MS Access –Objects

Microsoft Access is designed to help its users connect with Microsoft databases at the back-end and help them manage and store their data efficiently. Using Ms Access has been extremely helpful to many enterprises since they can manage and access their data in a more user-friendly manner. Coming to Ms Access, it has four entities that help people to have hassle-free work. These entities are called objects. In brief, objects are the collection of tools supported by MS access that helps the users get their job done.

There are four types of objects in MS access:

1. Tables – Tables are the places where any information is stored. The most basic work of any database is to store useful information, and tables are meant for that purpose. Tables don’t perform any other operations but simply store the data that would be used later for other useful operations. Below is an example of how an MS access table looks like:

MS Access 2016 table

2. QUERIES- Queries are the statements that will ask the software to conduct a particular detailed search from the database. Consider the example of a library having at least 1000 books. Now, there would be a database where all the relevant information of those books are stored. If you need a particular book, then you don’t have to search the whole database to get that book’s information. Instead, what you could do is simply write a query statement that will fetch all the information about the book which you are looking for. Queries are very helpful when it comes to extracting an only useful piece of information from a heap of data. Below is an example of an MS Access query.

3. FORMS– Forms display data from your table itself and help in performing the necessary tasks on the. Forms are basically an easy way to guide people using the software to perform various operations like sorting, adding, displaying in a more presentable manner, and so on. Whatever you do in a form, you are actually doing it on the table itself. You can directly do it from the table also, but then, the main challenge in doing so is, if your data set is of very large size, then it might be a problem for you to delete the non-useful data by finding it from the whole table and tracing out the useful columns for performing the important operations. Below is an example of how a form looks like-

4. REPORTS- Reports are static, easy to understand, easy on the eyes documents, which are presentable and portable. Colours can be added to it, and it & help in better managing the data that we need to present to someone. Reports are the objects which help in showcasing a particular section in order to highlight the important section. It’s more of an on-paper presentation of data. Below is an example of a report of data.

5. MACROS– Macros in MS access is used for automation purposes. It can automate tasks like opening forms, querying, changing values or even running other macros. It helps in adding functionality to your forms, reports and controls. This helps in improving efficiency when using the database.

If you need to manage the data well, then coordination of all four entities are required. If you consider the case of a book directory in a library, then the place where all the information of books is stored is called tables, if you want to search for a particular book, then the search command is the query, the place where you put your search criterion in the form, and last but not least, the result which the query has produced can be represented in a portable format as a report.

How to create a Database?

So now, as we have discussed the basic concept of MS Access, without beating around the bush, let’s start to learn how to create the database and get started with MS access. So basically, to begin with, there are many ways in which you can create a database, with templates and without templates being one the most used ones. Let’s see each one of them one by one.

• WITHOUT TEMPLATES (BLANK)- If you have a very specific requirement, then consider this to be one of the best options. It is like starting from scratch to building up the final model. Without templates, of course, the work would be more, as all the controls are in your control. Consider it like using the manual mode of a camera DSLR, since everything you are controlling, that’s why the initial set up work is more.

• WITH TEMPLATES – There are several templates that are already present in the Access framework. In the access setup, you can put your already existing data into these predefined frameworks and start a whole new database.

• BY DOWNLOADING DATA TEMPLATES – Apart from the data templates that are already existing in the access, you can also download new data templates that are available on the MS Access website. Once you are done creating the database, then you can directly begin to enter the data into the empty cells.

Regardless of how you are creating the database and the table, it’s always a good idea to set the field properties. Few properties are available in datasheet view, while few others are available in design view. The field properties are basically the parameters of the field, which will give a user a rough idea about that particular field.

So let’s start to analyse the field properties one by one-

• Field Size- The field size represents the size of the maximum string that a field can accommodate inside it. For the text, it is 255 characters at the max. For memo, it’s even more. So, each particular data type has a specific field size.

• Format- It reflects how the data has to be displayed. You can customize and select your own format.

• Input mask- Input mask is referred to as the property that will allow all the data that is having a particular format. You can imagine this like an Instagram filter, which will allow all the images which have that particular format.

• Default value- Default value is the property, which will display the normal (default) value of any data whenever new data is being entered into that field. Say suppose, for a medical practitioner, if you try to enter the BP count for ten patients, then each time the systolic pressure is entered, it would display 120mm/hg, and each time diastolic pressure is entered. It would display 80mm/hg.

• Required- Ever filled a google form where fields are marked with red Asterix (*) symbol? So what does that mean specifically? It means that it is a compulsory field that has to be filled before submission. In the same way, in Access, we have the required property, and if we enable yes into the required field, then Access won’t allow new data to be entered unless we fill that particular field. This is done to ensure that the required and mandatory field is not left vacant, even by mistake.

Copy-paste/import your data- This is one of the ways when you just want to import the data which is already being present, and no need to create and sort the whole data once again. You can do this by going to the External data section in the import and link group. You can select the data format, say, for example, XML, excel sheets, Text file etc.

Note: If you can’t find the data type present in the options, then try saving the data in a more common format, and then you can import the data into the access successfully.

You have the freedom to choose how and where the imported data would get stored in the present database in MS access. This is a great benefit for all the users.

MS Access-Data Types

Like in the case of most of the databases, we have various data types in MS access also. When we enter any data into a computer, the computer mainly categorizes the data into data types and then by categorizing the data, it stores it. The concept of data type is not only important for learning the basics of MS Access but also, It’s important because it will help you to understand the concept of VBA, which is the bedrock of Access. To learn more about VBA, take up VBA Courses that will help you upskill. There are mainly ten types of data types supported. Also, there is another data type called lookup. But in order to activate the lookup data type, we need to activate the lookup wizard in the design sheet view. So now, let’s look at the main ten types of data types supported in the MS access.

• TEXT – It is one of the most commonly used data types. It includes any combination of text, number and spaces. Now, the thing which needs to be understood here is, even though the data type can take entries in numbers, there should not be any need to have any calculations on the numbers that we have entered under the text category. The upper limit of the characters that can be entered into the text data type is 255.

• MEMO- The memo is used when we have the requirement to store more than 255 characters into a text field. Since the upper limit is bound to be 255, so in case if we required more than that, so we would require another data type separately for that purpose, this was the main mindset when memos were created by the developers. You can also store some notes regarding the record by using this data type.

• Date/time – In the business world, we often come across scenarios where we need to analyse the market trends in a quarter and also, we need to check the main peak time for our goods to be sold to the customers. So for that reason, we have the utmost important data types, called date time. Date time basically stores numerical type data, which is available to perform any operations also. Date/time can be very useful to people who are having work to record a particular time/event and perform analytics on that record later.

• CURRENCY- This data type is very much relevant to store any data which involves money and which may be subjected to calculations. Suppose I store data involving USD, and later I wanted to convert it into INR, and for that reason, we can easily store and get the required value by using this data type currency.

• Number- This is the most used data type after the text. Basically, a number is a collection of several other similar data types. The number is what we can say is the “int” data type in most of the programming languages. Its main function is to store numerical types of data which can be used to perform and subjected to calculations. The number has Byte, Integer, Long Integer, Single, Double, Replication ID, and Decimal as its subtypes, each having its own specification. Double is specifically used to store numbers that have decimal to be stored. In the same way, a long integer is used for entering data that is having integers that would be having non zero values on the right side of the decimal. A byte is referred to as the subtype where it can store bytes of data.

• Auto number- The auto number is the data type that is used to uniquely identify the various records stored in the table. It is basically the long integer type of data type, and this data type is automatically created and stored for each new entry that is being stored in the table.

• Yes/no – Well, this data type is the typical Boolean data type that will store whether the stored record is true or false. It’s like a true/false type of statement. The boolean data type is primarily used in cases like, say suppose, I store a data called “permanent employee” or “contractual employee”. So in case a person working in the position of a permanent employee, the record entered would be yes or else, it has to be stored as no.

• OLE data type- This data type is not very widely used in our day to day usage. But then again, it can’t be ignored completely. This is required when we store something which is having a binary file, say suppose a picture and a sound file.

• Attachment – Attachment is the data type that is newly included in the access ecosystem. By using this data type, we can attach image files, charts, documents etc., to a record.

• Hyperlink – This helps in storing any URL address of a website into the record.

MS Access –Create Tables

In relational databases like MS access, you store any information in the form of records (rows) and in the form of fields (columns), together of which constitutes a table. A table is created such that we can derive various relationships between the different data sets. Let’s take a real-life example to understand it more clearly. Say suppose you join an organisation in the role of an HR manager.

Now, the department of payroll asks you to provide the details of all the employees currently working in a particular department. So, you would most probably try to develop an MS Access database, with all the employees’ information like their employee id, DOB, date of joining, number of days present and also, number of days taken leave or marked absent. So, whatever fields I just mentioned are all independent entities that are required to be entered and saved into a single place. This place is technically called a table. Before entering any particular data, you need to enter the data type for that particular field. If you try to enter any data which is not of the mentioned data type, then Access would not take the entry.

Among other advantages of creating a table in Access, one of the major advantages is it prevents data redundancy. That is, it would help you prevent duplicate entries or repeated entries of particular data. Even if redundant data is not reduced to 0 levels, it can significantly lower the redundancies already present in the data. This is of great help because manually reducing or eliminating duplicate information can be a daunting task all by itself.

So let’s see how a table is opened in a datasheet view and how helpful it can be in managing the information.

Whenever you go to the navigation pane, and double click on a particular table, then the view in which the table opens is called a datasheet view. You can mark the important section of your table in this section. It is best suitable for viewing and editing the entries. At the bottom left corner of the datasheet view, you would be able to see the number of records in the table.

An example of how a table looks like in datasheet view (image source: google)

But if you want to set field names and their corresponding data types, you would need to go to the design view. In design view, it’s actually easy to manage view, and it controls the overall layout of the table that you are seeing.

So when we open the design view, we can create the table by following these simple, easy steps-

• In the Design view, go to the application ribbon and select the Create tab and then the Table Design button.

• Adding fields– You can add fields such as name, address, zip code, PAN card details, aadhar card etc. After adding the fields, it should look like this. (image source :google)

• Adjusting the data types– Now you need to think, which field would contain what type of data type, and accordingly select the data type. One thing to be noted here is, if any particular data type doesn’t need any calculations, then that data type can be selected as text instead of number. Say suppose, even though the zip code of a particular person’s address is not typically a text, but still we can select the data type as text instead of number, since we usually don’t perform any calculations on someone’s zip code.

But on the other hand, someone’s salary field should be set as number, because you may need to calculate salary, apply deductions on it and hence it is always suggested to do it in this way.

• While you save a table, maybe you would be prompted by the Access, to create a primary key. It’s a good practice to always accept the suggestion and create a primary key, the importance of which I would explain in the later part of this article.

All About The Primary Key

All of us, at some point in our lives, have come across this term called the primary key. So what’s the use of a primary key?

Let us take an example to understand it in a better way. Suppose I have a firm, my own company, and I want to store the record of each person working in my organisation. Then, when I have a few fields like DOB, date of joining, age, salary and employment ID, then certainly, there are chances that any two records of two different persons can have almost everything similar to each other. What I mean to say is, two people, who have joined the company on the same date, have been offered the same salary package, and coincidentally have their birthdays on the same date, and they may even have the same age also.

Now, to make the situation worse, there may be one in a hundred cases where even the names of both the persons become the same. ( I myself had five students named Ayush in my high school). So, it becomes a matter of confusion for the HR department to keep track of them, and it may lead to serious misconceptions at times. So, in that case, We have to define a particular field, which we call the primary key, which will be unique for each employee.

Each employee, here, in our example, can be assigned a unique employment id, which can serve as the primary key. It should be kept in mind that computers, as a device, originated as a military device whose sole work was to coordinate with the military requirements. So, typically, the databases were created, they created a unique identification number, which would be similar to the unique military id that was assigned to each machine during the WW2 era. You can also think of it as the aadhar card number.

There is actually a requirement to assign a field to be as a primary key. You cannot simply assign any field randomly to be the primary key. The main criterion is, it should not contain similar data for 2 different entries. And then, no particular cell should be left vacant. If these two conditions are satisfied, then you are all ready to set that field as a primary key.

Query Data And Query Criterion

When you want to review the most current data, or you want to summarize the previous data, or you have some specific such role. Obviously, the first thing which would come to your mind is writing a query statement.

Queries are of several types. We will discuss each type of query one by one in great detail.

• SELECT QUERY

If your information base has a table with a great deal of data about items and you need to audit a rundown of items and their costs, here’s how you’d make a select inquiry to restore only the item names and the individual cost:

Open the information base and on the Create tab, click Query Design.

In the Show Table box, on the Tables tab, double-tap the Products table and close the exchange box.

In the Products table, suppose that you have Product Name and List Price fields. Double-tap the Product Name and List Price to add these fields to the grid.

On the Design tab, click Run. The inquiry runs and shows a rundown of items and their costs.

• PARAMETER QUERY

Open the database and on the Create tab, click Query Design.

In the Show Table box, on the Tables tab, double-tap the Products table and close the exchange box.

In the Products table, suppose that you have Product Name and List Price fields. Double-tap the Product Name and List Price to add these fields to the grid. On the Design tab, click Run. The inquiry runs and shows a rundown of items and their costs.

You can likewise determine what kind of data a parameter ought to acknowledge. You can set the information type for any boundary. However, it is particularly imperative to set the information type for numeric, cash, or date/time information. At the point when you indicate

the information type that a boundary ought to acknowledge, clients see error messages in the event that they have entered some unacceptable kind of information.

Also, you can make use of this feature in a more efficient way. Let me teach you how. Make estimations dependent on your information.

You ordinarily would not utilize tables to store determined qualities, similar to subtotals, regardless of whether they depend on information in a similar data set, in light of the fact that determined values can get obsolete if the qualities that they depend on changes. For instance, you would not store somebody’s age in a table, in light of the fact that consistently you would need to refresh the value; all things considered, you store the individual’s date of birth, and afterwards, utilize a question to figure the individual’s age.

For instance, on the off chance that you have an information base for certain items, you’d prefer to sell. This data set has a table considered Orders Details that has data about the items in fields. For example, You can calculate the subtotal by using a query that multiplies the quantity of each product by the unit price for that product, multiplies the quantity of each product by the unit price and discount for that product, and then subtracts the total discount from the total unit price.

So now, since we have learnt a few concepts about the query, let us analyze the basic query criterion.

So what is a query criterion?

These are the basic rules through which queries are guided. It’s like TCP/IP of Queries.

A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPart expression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) =

“http://www.microsoft.com/”, where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.

Features

It is easy to understand, as well as for use
Sending and receiving files or applications
Provides information for individual and smaller teams
Allows creating forms, tables for storing information

Advantages

Quick and easy to form
It produces pliability and invincible data
It helps to manage and organize the specified data
Easy to search and get a database

Limitations

Not suitable for the web
Security and data probity
It is not obtainable over the internet

Uses

Used for official work i.e., in schools, or offices
It is also used for business purposes
Used for programming evolution

Basic terms

• Database file – It is a file which stores the entire database and is saved to storage devices such as RDBMS.

• Data types – Data types are the elements or components of each field. Each field consists of only one data type like text, number, data etc. Such as tables, query, form, reports, and modules.

CREATE QUERY –

Queries are selected data based on limitations. It can help in relating data from one table or to more, or with other queries. Queries can be SELECT, INSERT, UPDATE, and DELETE.

A query can be based on tables or any other queries. You can use a query to view a subcomponent of your data or to answer questions about data. To create a query, you should open the tables or queries on which you are going to base a query. A query is a vital attribute of MS access that helps to recover and perceive the information of the defined database.

You can save and go through the same queries repeatedly. If you regularly want to run a variety of specific queries, consider using a limited query. Limited query perceives information in a collective process. You can specify the data, set the different kinds of data, but you have to set data in a numeric, or time data way. The specified data should make the user understand errors and grammatical mistakes if they enter any wrong input, as it accepts the text data without displaying errors.

Designing Action Queries –

This document provides basic techniques and methods for creating, designing and accessing queries in MS Access. It allows you to delete and append records of data. It helps to transfer data from one table to another one also helps you update the information. Action query makes changes to the data and always has a backup plan before performing it.

Steps –

Assemble a backup
Create a select query and have trial it for the result
Convert it to an action query
Run the query to make changes to the data

Create an Update Query –

An MS Access update query makes an update of determined values in a table for all records or those records have particular criteria. It updates records manually in a table of a database, or a sequence or a table’s datasheets. As it changes records manually, it takes too much time. It is also incapable to send errors to itself as you update the records.

Steps –

Create a select query
Apply query criteria
View, click on the arrow to the right of the query
Press the button and choose Update Query
Now, we need to run this query, using and pressing the Run icon

Types of query

1. Select query

A select query is a collection of objects or data that shows information in a datasheet. A query does not store data, it displays data that is stored in tables. If we want to use data, we have to use all of the data from any one of the selected tables.

Benefits –

1. It helps to view data from the field or table in which you are interested by choosing it.
2. It can combine data from several data sources.
3. It helps to view records that meet criteria, and helps to see and save all the selected records.

Create a select query:

Choose the tables and queries which you want to use in data.
Classify and identify the fields which you want to include from the data sources as per your choice.
The query returns to specify criteria to limit the records.

2. Cross tab

Cross tab query is a query that summarizes, and organizes the data in Access. It is easy to understand. Cross tab calculates a sum, average or other aggregate functions. It contains date/time for column heading.

Benefits:

1) It is easy to understand and for use.
2) It can automatically make an update.
3) It has a list of fixed inputs to use in the column heading.

Create crosstab query:

On the create table, click query wizard in the queries group.
In the New Query dialog box, click the crosstab Query Wizard.
Click OK.
Select the table, and then click NEXT.
Choose the field and then click NEXT.
SELECT category and click NEXT to continue.
Choose date / time field of column headings
Select ID and click Next.

3. Action query:

Action query are queries that can add, change or delete various kinds of records at one time.

Steps to create an action query –

Double click on the Table name in the tables tab.
Run the query, to display the data.
Select the Append button in Query Design View.
Select the table Name from the list and click OK.
Click YES to confirm.

4. SQL query:

It is a language used in computers for working with sets of fields, facts and figures related between them. Steps to create:
Open your database and click the CREATE tab.
Click Query Design in the queries section.
Select the POWER table.
Click the Add button and then click the close button.
Click the home tab and the view icon which is at the left side corner.

5. Parameter query:

A parameter query is a query which acts as variable limitations that can be changed at any time. It can be updated easily. It is a part of the database and has a larger proclamation to create a criteria.

A person can filter the data, records or information through the help of parameter query. Whenever, we start or create a parameter query, it asks for a value, then it starts running. Using the values, the query can make different results based on different inputs or values. We can add SELECT, APPEND, MAKE TABLE or TO UPDATE.

Steps –

Create a query – Firstly , create a query in QUERY DESIGN.
Select and choose the tables – Select the items in the query by clicking ADD option.
Add the fields – Add the fields, which you would like to add by double clicking.
Use and run the parameter Criteria – If the users will type anything, it will be shown as the user’s prompt in the parameter criteria. At last, make sure, Click the RUN icon to run the query.
Supply the parameters – You have to add value at the prompt.
The results – At last, the results are based on the specified parameter supplied .

The parameter query is beneficial to us as it has a long previous time record from the starting of records even if you make changes also or updated into a new record. Here, it runs as fast as earlier, with different values which have different inputs. One more thing, it doesn’t edit the criteria every time but it has the records of the query prompt when you need. Whereas, it has also some limitations – As the parameter queries or mistakes and shows in various places. In this case, it also has a few or limited internet.

ALTERNATE CRITERIA

A criteria consists of field references and operations. It applies to a number of fields where it records the price, data and time, i.e. no. of years, date and time. And it can be applied to any type of field which shows records of the database. It is classified into different rows and columns of the database field. If we add alternate criteria in various fields for different rows. We can also perceive information for users i.e. last name or the job title. Alternate criteria helps to proclaim some specified items from an Access database.

It is a field where values are given to determine the particular records. Sometimes, queries can be saved and run repeatedly, so in this case we add alternate criteria. We can add criteria by following two ways – First, we can use the OR operator to combine two sets of criteria. Second, we can also grid or make it separately in different rows.

Steps –

• Open database
• Select Query Design
• Press on Add option and make add by pressing it twice.
• Double click on all the selected fields, you want.
• Now, you can see that alternate criteria in different rows of the field or in multiple fields.

Logical and OR conditions in MS Access Query criteria –

The Access query design grid offers almost a wide range, possibilities for filtering data. The application of AND and OR logic in the correct combination and this represented in the grid. These are keys to which we can filter correctly by constructing the set of data which can search and seek it.

The Unfiltered Query:

This is the select query to which we will apply for the given criteria to filter the data, information and records. These records are sorted by date, and on the date it’s started earlier.

Applying logical AND to filters:

Here, expressions are related by logical AND that implies those expressions which are inclusive. The selected query has filtered all records and data relating to address your thinking as a logical expression. In the Access query, the design grid is applying filters by putting all the patterns, charts and records into the criteria part. And so, you can link the filters by logical AND. After doing it, when the given criteria is related to the text, we can type the quoted text, mark or leave Access to put them in for future. Number are restricted to be applied in the ANDs. You may use logical AND within only one criteria expressions. Access doesn’t make differences between upper and lower case.

Applying logical OR to filters:

A logical OR determines exclusive criteria i.e., items which are related to the output of the criteria In this query, the design grid may consist of inputs, whereas logical OR filters into different rows. As an alternative order, people can be using criteria rows to create logical OR conditions, which we might create within the same row.

RELATING DATA –

It refers to the process of organizing rows, interpreting columns and tables of a relational database . It provides and improves the whole performance and probity of information and records. It is also called a ‘Relational Database‘ because it consists of one or more data in columns or tables. Each row attached to one another records and each table describes the data information in a table as each information is related to another one.

It defines relationship works by making data in both tables and have a unique consistency for each row in the table Each data classified and dependent on another one. Here, users can add records, tables regarding the entire given information . A sample data can be prepared for Create a query that determines some database in which you can add or copy data. A “Database“ is mainly a collection of related data, which contains the same types of data which are related to each other and the tables are going to be related with each other in a particular database.

Uses –

• Use for storage in devices.
• Used in various data structures .
• Used in for storing information systems.

Adding of data –

A datasheet is a simple way to look at your data in rows and columns. We can create a new web table, Access automatically creates two views. A table open in a Datasheet view resembles an Excel worksheet.

Steps –

• Select the views in the Datasheet view.
• Similarly add some data.
• You can now see that inserting new data and updating the existing data.
• Click Yes and you will see that the selected record has been added to it.

CREATE RELATIONSHIP –

Create Relationships determines the related records of data from each table for a desired result. It defines by adding the tables which you want to combine or relate to the Relationships Window by dragging the table to the another one. It provides a detailed record of data in the table. A relationship can be created in MS access by combining data of two different tables. Each relationship consists of two tables only.

Relationships are carried out from queries i.e., we have to create queries to make a relationship between tables and records –

• One to one relationship
• One to many relationship.
• Many to many relationships.

Steps –

1. On the database Click Relationships group .
2. Secondly, click Relationships.
3. On the Design tab, click Add tables in the Relationships group.
4. Select queries and then click Add.
5. After adding queries, click close.
6. Move it from one table to the other one.
7. Add then separately click each one of the fields and drag it.
8. Click on the field name.
9. Select the field which you want and click create.

Edit a relationship –

You can also make changes and edit the Relationship of data. It allows to change the table Relationship by changing the tables or queries of both sides. You can define relationships by the given facts, figures, charts and information of the data.

Steps –

• Double click the Relationship line, it becomes thicker when it is selected.
• On the database tab, click the Edit Relationships as it appears.
• In the group, click Relationship and then Click All Relationships.
• Double click the Relationship line once again, or you can click Edit Relationship.
• Last one, click OK after making additional changes.

Update of Relationships –

You can also update your information about data.

Steps –

1. Click Relationships in the Relationships group on the database tools.
2. Click All Relationships in the Relationships group on the design tab.
3. Click the relationship line for the relationship that you I want to change.
4. Double click the relationship line, the Edit Relationships dialog box appears.
5. Select the Enforce Referential Integrity.
6. Select either the Cascade Update Related Fields or the Cascade Delete Related Records or you can also select both of them.
7. After making additional changes and then click OK. Delete a table relationship – If you want to remove a table relationship, you must delete the relationship in the Relationship Window.

Steps –

1. Click Relationship in the Relationship group on the database.
2. Click All Relationships in the group on the design table.
3. Click the relationship line, it appears thicker when it selected.
4.Press the DELETE key.
5. A confirmation message should be displayed that would you sure to delete the data or not, so Click YES.

ONE TO ONE RELATIONSHIP –

It is related data in MS access. It is data created only for its related fields, as it has primary records or unique content. As we can say also, it is a link between the information in two tables, where each record in each table only appears once.

In one to one relationship, only one record in a table is associated with one and only one record in the other table. 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 to this would be in one table. You might use a one to one relationship to divide a table with many fields. You can create one to one relationship by linking the index in one table and an index in another table which shares the same value and input. The best process is to create relationships to have the secondary table look up value from the first table.

Referential data integrity helps to keep data clean by deleting related records.

Steps of creating a one to one relationship by adding a lookup field to a table:

1. Open a table.
2. In Design view, add a new field.
3. Select Data type value.
4. Select LOOKUP WIZARD , in this the default is set to look up values of the other one.
5. Select NEXT.
6. Select the content that you want to include in the table and then press NEXT.
7. Add the field that contains the key which people want to use in the Selected field, select NEXT.
8. Set an order and change the width of the field.
9. And at last, on the final screen, clear the Enable Data.

ONE TO MANY RELATIONSHIPS –

In one to many relationships, one record in a table can be correlated with one or more than one record in another table. It returns related records when the value in the Customer ID field in the Orders table is the same as the value in the Customer ID field in the customers table. It can be identified by the infinity symbol on the line connecting the tables.

Steps –

• Click the Office icon on the upper left hand corner.
• Click Access options, Access options dialog box appears.
• Click the Current Database button.
• Select Overlapping Windows in the Application Options Section, under Document windows options.
• Click OK.

Steps to create one to many relationships –

1) First, clear the layout by clicking on the option, clear layout on the Design tab.
2) Add another table in the table task.
3) Click on the Save icon and enter tbl Tasks.
4) As the table name, and go to the Relationship view.
5) Click on the show table option.
6) Add tbl projects and tbl Tasks and close the show Table dialog box.
7) Click the create button. We now have a one to many relationship.

MANY TO MANY RELATIONSHIP –

A many to many relationship exists when one or more items in one table can have a relationship to one or more items in another one. It contains orders placed by multiple customers and a customer may place more than one order. It requires a little extra work up. It demonstrates a four step process for creating an associate table to handle the relationship. It occurs when both of the tables contain records that are related to more than one record. You must create a third table because sometimes, Access doesn’t support a many to many relationship. It contains a primary key and a foreign key to each data table. Many to many relationships are knowing data and take the place of understanding how all the data fits together to accomplish the desired result. It is easier to spot during the Design process.

Steps to accommodate many to many relationship –

• Delete the existing relationship between the two tables.
• Create the table and include a foreign key field for both related data.
• Create a one to many relationship between each of the data tables and appropriate foreign key in the associate table.
• Delete foreign key fields in the many tables of the original one to many relationship.

Associate table to base form on a query:

• Add all the non – key fields, you have to modify or may need from both the many and one table.
• At the foreign key that represents the one side from the associate table.

It is a type of mathematical data which refers the relationship between two entities i.e., A and B. This relationship is usually implemented by means of an associative table A many to many relationship between entity types represented by a logical model database table. It occurs when multiple records in a table are associated with multiple records in another table.

Example – such as many to many relationships exists between employee and projects.

WILDCARDS –

Wildcards are special characters that can stand in for unknown characters in a text value and are located for various items with similar but not an identifier one. It helps to perceive data based on a specified pattern. Access have two sets of wildcards characters because it supports two standard for structured Query Language.

• ANSI – 89

It describes the traditional Access SQL syntax , which is the default for Access databases. The wildcard characters conform to the Microsoft Visual for Application (VBA) specification. You can use the ANSI – 89 wild cards while running queries and finding and replacing operations.

• ANSI – 92

It helps to use when you want your syntax to be compliant with a Microsoft SQL server database. You can use the ANSI – 92 wildcards when you run queries against Access projects. Access files connected to Microsoft SQL server database. SQL uses the standard so Access used ANSI – 92 standard.

Using of wildcard –

Match all characters in your data:

• Open your query in Design view. Under queries, right click the query and click Design view.
• In the criteria cell under the field you choose, add an asterisk or any side of your criteria or on both sides.
• On the Design tab, in the Results group, click the RUN icon.

Match a character within a pattern :

• Open your query in Design view.
• Type the operator like in front of your criteria, you want to use in the Criteria cell.
• Replace one or more characters in the criteria with a wildcard character.
• On the Design tab, Click Run in the Results group.

Retrieve a list of companies A through H:

• Open your query in Design view.
• Enter LIKE in the criteria cell of the field you want to use.
• Within the double quotes, add a pair of square brackets and the range of characters .
• You can also use wildcards outside the brackets.

Purpose –

The wildcard is an advanced search technique that can be used to maximize your search results in library databases. Wildcards are used in searching items to represent one or more characters.

LIKE mean in Access –

The LIKE condition allows you to use wildcards in the where clause of a SQL statement in Access 2007. It helps to perform pattern matching.

You can use wildcard characters in Access to add an additional level of flexibility to your query criteria. Characters of wildcards within Access represent unknown values. The asterisk ‘ * ‘ and the question mark ‘?‘ are two main characters of wildcards in Access. The asterisk represents multiple unknown characters. Whereas the question mark represents one unknown character. You can also type as many question marks as needed to fill in the required unknown characters. Whenever we type wildcard characters in Access into the criteria row of the grid, Access places the word “ LIKE “ before them. This is simply the required syntax. It is not necessary for you to type it with yourself if you do not want to, as Access adds it for you.

CALCULATED EXPRESSION –

A calculated field is a column in a query that results from an expression. An expression is a combination of mathematical or operators, constants, functions, table fields and properties that evaluates to a single value.

Forms and reports:

The tables in this section provide examples of expression that calculate a value in a control located on a form or report. You can enter an expression in the Central Source property of the control to create a calculated control.

• Text operations –

The expression in the table, use the ampersand (&)and the plus (+) operators to combine text or to create a calculated control.

• Arithmetic operations –

You can use expressions to add , subtract, multiply and divide the values in two or more fields or controls. You can also use it to perform on dates.

• Headers and footers –

You can use the PAGE and the properties of PAGE to display or print page number in forms or reports. Properties are available, so they do not appear on the sheet for the report. It helps to identify the data collection in a table.

• Values in other controls –

Sometimes, you need a value that exists somewhere else, such as in a field or control on another form or report.

• Average values –

You can use a type of function called an aggregate function to calculate value for one or more fields or controls.

• SQL aggregate functions –

You can also use a type of function called a SQL or domain aggregate function when you need to calculate values selectively. A “domain “ consists of one or more fields in one or more tables.

• Data operations –

Tracking dates and follow times is a fundamental database activity. You can use arithmetic operations on dates to calculate the date 30 days after the date in the Invoice Date field and control.

• Conditions of only two values –

Here, it is given three agreements, First it is an expression that must return in True value and second, it’s values are returned in FALSE one.

Queries and filters:

This part contains expressions that you can use to create a calculated field in a query or to supply criteria to a query. You can use criteria in the query to limit the records that you work with it.

• Text operations –

The expressions in the table use the & and + operators to combine text strings, and use built in function to operate it.

1. If creates a field called Full Name that displays the value in the First and the Last Name fields.
2. Creates a field called Address that displays the values.
3. Creates a field called Product function to use the left function to display the value of product name .
4. Creates a field called Type code, and then uses the right function to display the last two characters of the values.
5. Creates a field called Area code.
6. Name the calculated field and the use function to calculate the total items of applied discount.

• Arithmetic operations –

You can use expressions to add, subtract, multiply and divide the values in two or more fields or controls.

1. It creates a field that displays freight charges plus 10% in a field called Prime Freight.
2. Creates a field called Order Amount that displays the values of the product.
3. Creates a field called Lead Time and then displays the difference between the values Required Date and Shipped Date.

• Data operations –

All databases store and track date and times. You work with it by setting the date and time fields in the data type.

• SQL aggregate functions –

It performs to summarize the data or you can say, sum, count and average the data. It allows to count the number of records in the query, calculate the percentage of freight charges and calculate the average freight on all orders to combine in a total query.

• Fields with missing data –

Here, the expression shows work with fields with potentially missing information, such as those unknown or undefined values. You encounter null values, such as an unknown price for a new product or a value that a employee forgot to add. The ability to find the null and values can be a critical part of database operation.

• Calculated fields with subqueries –

You can use a subquery to create a calculated field that displays data about the category name, product Id from the categories table is the same as the product one.

• Match text values and data criteria –

Here, the expressions determine the whole or partially text values. And the date criteria expressions demonstrate the uses of dates and related functions in the criteria expressions i.e., Shipped date, Required date and Order date.

• Find missing data –

A null value represents the absence of information, it does not represent a zero or any value at all. Access supports the idea of missing information because the concepts play a vital role in the probability of a database. And here, databases must be able to record information of displaying orders for customers whose fields are null or contains a value.

• Match record patterns with LIKE –

The LIKE operator provides flexibility when you are trying to match rows that follow a pattern, because you can use LIKE with wildcard characters and defined patterns for Access to match. It finds all records in the Ship Name field.

• Match rows with SQL aggregates –

A domain aggregate function needs to sum, count or average values selectively. It displays all the orders.

• Match fields with subqueries –

You can use a subquery to calculate a value for a criteria. It displays products price, product unit price above the average and earning of each sales representatives.

• Update queries –

You can use an update query to modify the data in one or more fields in a database. The field consists of Title, date of project when started and confirmation code.

• SQL statement –

SQL ( Structured Query Language) is a query language that is used in Access, it creates queries. Design View can also be expressed by using SQL.. It displays the values in the First and Last Name field and displays the values of product Id and product name. It also records the similar matches of category id.

TABLE EXPRESSION:

Here, there are two ways to use expressions in table –

• Field default values –

Whenever you design a database, you might want to assign a default value to a field. Here, Access supplies the default value .When a new record contains the field is created or when an object contains, the control is created. The table represents default values for a field, and if a control is bound to a field in a table, takes precedence.

• Field validation rules –

You can create a validation rule for a field or control by using an expression. To create a validation rule, we have to identify the properties of the field or control it. The component holds the text that displays the rule is violated.

MACRO EXPRESSION:

Sometimes, you might want to carry out an action in a macro if the given condition is TRUE. It must resolve either TRUE or FALSE value.

Sorting and Filtering Records

We know that Access allows us to work with a humongous amount of data. But, going through such data while querying or manipulating is highly difficult and almost impossible for a human. So, we use sorting and filtering functionalities provided by the MS Access application to make our tasks easier. Sorting and Filtering are two tools that help us organise our data in our desired format and then view it. Let us understand each of them:

Sorting: In Sorting, you organise data in a particular order, a logical order. This results in the stored data being easier to read and understand over unsorted data. There are a number of ways you can perform sorting on the data. By default, the records are sorted by their id numbers. Some of the other ways include: sorting by name, last name, order_date, city, zip code, and category, etc.
Filtering: This feature allows you to view only the records or data you want to see. While creating filters, you set the criteria or the constraints for the data you want to view. The filter then hides the unwanted data or records and only the desired ones are displayed to you. This increases efficiency and reduces the distraction caused by unwanted data.

Difference between Access and Excel

While both of these belong to the Microsoft Office Suite, they are not the same. They have very different functionalities.

Let us briefly understand the difference between Access and Excel:

MS Access	MS Excel
This application is a database program. It helps to create or sort data in a database.	This application uses spreadsheets to create graphs, charts, etc.
It is used for storing and manipulating large amounts of data.	It is used for computational and financial purposes.
The storage capacity is more as it is particularly built for storing data.	The storing capacity is less as it isn’t built for storing data.
It is more flexible.	It is less flexible.
This is difficult to learn.	This is easy to learn.

MS Access Tutorial FAQs

Q: What do you use Microsoft Access for?

A: Microsoft Access helps in fulfilling business requirements for several types of small-scale database solutions. MS Access is a very productive tool; therefore, valuable results can be fast produced, which ultimately helps the business. You can also call it an information management tool that helps in storing information for reporting, reference, and analysis.

Q: How can I learn MS Access?

A: There are MS Access tutorials for beginners available online. However, if you want to learn it with the simplest instructions, you can refer to the tutorials offered by Great Learning.

Q: Is MS Access hard to learn?

A: MS Access is not extremely hard to learn. However, those who are in general familiar with database concepts and relational database concepts will find it easier to learn.

Q: What is the difference between Excel and Access?

A: Microsoft Excel is an application in which spreadsheets are used for creating charts, graphs, and tabular models. Microsoft Access is yet another application that works as a database program and helps in assembling as well as sorting the data. MS Access is mostly used for spreadsheets and even financial calculations.

Q: What are the disadvantages of Access?

A: The disadvantages of MS Access include that entire data is stored in one file, limited database system, time-based transactions are difficult to carry in.

Q: Why is Microsoft Access so popular?

A: Microsoft Access is popular because even beginners can come up with a simple database quite quickly. The experienced MS Access developers can create a huge range of software functionality much faster as compared to the other development platforms.

Q: Which language is used in Microsoft Access?

A: An object-based programming language that supports MS Access is Visual Basic for Applications (VBA).

Q: Is Access harder than Excel?

A: Access is helpful in managing data, while Excel helps in analyzing data. Access helps in keeping data organized and is available to several users at a time. However, for producing higher quality charts, performing difficult calculations, you would need Excel.

Q: Can Access do calculations like Excel?

A: In the Access queries, a calculated field can very easily be created. Access can also calculate values that have been entered manually.

This brings us to the end of the blog on Microsoft Access Tutorial. We hope that you found this helpful. If you wish to learn more about such concepts, you can check out Great Learning Academy’s pool of free online courses.

Also, do check out our tutorials on other topics to up-skill and power ahead.