MS Access Tutorial

It is an application for creating, accessing, managing and addressing data. It is a part of  Microsoft office. Access includes screens for data entry, carrying out of queries, making reports, etc.  

Features

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

Advantages

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

Limitations

  1. Not suitable for the web  
  2. Security and data probity  
  3. It is not obtainable over the internet 

Uses

  1. Used for official work i.e., in schools, or offices  
  2. Used for business purposes 
  3. 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 – 

  1. Assemble a backup 
  2. Create a select query and have trial it for the result  
  3. Convert it to an action query  
  4. 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 –

  1. Create a select query 
  2. Apply query criteria
  3. View, click on the arrow to the right of the query  
  4. Press the button and choose Update Query 
  5. 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 which summarizes, and organizes the data in Access. It is easy to understand. It 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. 

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

0

LEAVE A REPLY

Please enter your comment!
Please enter your name here

10 − 8 =