What is MS Access – Queries ?| What is Queries ?

by anonymous
May 17, 2023
21

What is Query ?

The Query is like a simple question that you ask to find specific information from the database.

To know any details of your friends form the database.

≫ Queries are a way of searching for and compiling data from one or more tables.

≫ 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.

≫ You can define queries to Select, Update, Insert, or Delete data.

What is MS Access - Queries ?| What is Queries ?

Table of Content

👉 Relationship between the Tables

👉 How to create a relationship between Tables?

👉 Creating A Query

👉 Creating a Query in Design View (Simple Query)

👉 The Design Grid

👉 How to Specify Criteria?

👉 From in Access

👉 Creating a Form

👉 Report formatting and printing Report

Relationship between the Tables

If you want to build a table relationship in Access 2016, you must first open the Relationships window. You do that by following these steps:

STEP 1: Click the Database Tools tab on the Ribbon. The Relationship group appears on the Ribbon

STEP 2: From the Relationships group, click the Relationship button. The Relationships window appear along with the Show Table dialog box.

STEP 3: For each pair of tables, you want in the relationship, click the table and then click Add. A little window in the big Relationships workspace lists the fields in the selected table. As you add tables to the layout, a separate window appears for each table. You can see these windows to the left of the Show Table dialog box.

Repeat Step 3: for each pair of tables you want to relate to. If one of the tables in the pair is already present (due to an existing relationship it has with another table), you don’t have to add it again.

STEP 4: Decide which tables you want to relate to. Since the one-to-many relationship is the most common.

STEP 5: Put the mouse pointer on the field you want to relate in the parent table (the ”one” table in the one-to-many relationship) and hold down the left mouse button.

Usually, the field you want to relate to in the parent table is the primary key.

STEP 6: While holding down the left mouse button, drag the mouse pointer from the parent field to the child field in the child table (the “many” table in the one-to-many relationship). The fields typically have the same name, such as Location ID.

STEP 7: Point to the related field in the child table and release the mouse button.

The Edit Relationships dialog box appears, detailing the soon-to-be relationship.

STEP 8: In the Edit Relationships dialog box, select the Enforce Referential Integrity and Related Records option.

This ensures that when you delete a record from the parent table, Access deletes all related records in the child table.

STEP 9: Click create to set the relationship.

The Edit Relationships dialog box closes and a join line appears connection the two fields, indicating that the fields are related.

How to create a Relationship between Tables ?

Define relationships between tables in an Access database

One-to-many relationships
Many-to-many relationships
One-to-one relationships

How to define a one-to-many or one-to-one relationship

To create a one-to-many or one-to-one relationship, follow these steps :

STEP 1: First, close all tables because you cannot create or change relationships between open tables.

STEP 2: On the Tool menu, click Relationships.

STEP 3: If you have not yet defined any relationships in your database, the Show Table dialog box is automatically displayed. If you want to add the tables that you want to relate but the Show Table dialog box. To create a relationship between a table and itself, add that table two times.

STEP 4: Double-click the names of the tables that you want to relate to, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table two times.

STEP 5: Drag the field that you want to relate from one table to the related field in the other table.

To drag multiple fields, press Ctrl, click each field, and then drag them.

In most cases, you drag the primary key field (this field is displayed in bold text) from one table to a similar field ( this field frequently has same name ) that is called foreign key in the other table.

STEP 6: The Edit Relationships dialog box appears. Make sure that the field names that are displayed in the two columns are correct. You can change the names if it is necessary.

STEP 7: Click Create to create the relationship.

STEP 8: Repeat steps 4 to 7 for each pair of tables that you want to relate.

When you close the Edit relationships dialog box, Access asks whether you want to save the layout. Whether you save the layout or do not save the layout, the relationships that you create are saved in the database.

How to define a many-to-many relationship

To create a many-to-many relationship, follow these steps:

STEP 1: Create the two tables that will have a many-to-many relationship.

STEP 2: Create a third table. This is the junction table. In the junction table, add new field that have the same definitions as the primary key fields from each table that you created in step1.

In the junction table, the primary key fields function as foreign keys. You can other fields to the junction table, just as you can to any other table.

STEP 3: In the junction table, set the primary key to include the primary key fields from the other tow tables. For example, in a “Title Authors” junction table, the primary key would be made up of the Order ID and Product ID field.

STEP 4: Define a one-to-many relationship between each primary table and the junction table.

Creating A Query

You can create a query in two different ways :

๑ Creating query in Design view.

๑ Creating query by using Wizard.

We will learn to create a query in Design view.

Create a query in Design View (Simple Query)

Suppose you have created ‘Database 22’ in MS Access. Now, to create a simple query, follow these steps:

STEP 1: Open Database 22′ in which query to be created.

STEP 2: Click ‘create ‘ tab which is available on the ribbon.

STEP 3: Select ‘Query Design’ under ‘Queries’

STEP 4: ‘Show Table’ dialog box appears on the screen. Click on ‘Tables’ which displays the list of table in the selected database.

STEP 5: Select the table on which you want to make a simple query.

STEP 6: Click on ‘Add’ button and then on ‘Close’

The ‘Query1’ window appears on the screen. It is divided into two different panes. The upper part of the right pane displays the table with the field names only. The lower part of the pane displays the design grid which contains the headings as described below:

Field: It will display all the fields of the selected table.
Table: It will display the name of the current table.
Sort: It will allow sorting of data either in ascending or descending order.
Show: Checking this box means that the selected field will be visible at the time of running a query.
Criteria: It is used to specify the condition on the basis of which the record are filtered.
Or: It allows to specify multiple criteria which is used as ‘OR’ condition.

When you click on the first column under row heading ‘Field’, it will display fields of ‘Location!

STEP 7: Select the required field from the drop-down list. You will notice that the “Location” appears in the row heading “Table and a tick in the row heading” Show (by default). While creating a query, all the fields of the selected table are shown (by default). You can uncheck the box if you do not wish to include a particular field in your query.

STEP 8: Now, repeat the process and move to next column under row heading Field to select the field for the query.

STEP 9: Click ‘Run’ button that is available on ‘Design’ tab under ‘Results’ group.

The result of the query is displayed on the datasheet view.

The system gives the query name as Query1 (by default). Click ‘File’ button followed by the ‘Save’ option from the drop-downlist. The ‘Save As’ dialog box appears on the screen. If I want to make any change in the Query name then enter you an appropriate name in the box, otherwise click ‘OK’

Thus, Query1 is saved in the Database 22 and is displayed on the left pane of the window.

The Design Grid

The design grid appears at the bottom of the query design below the design canvas. The design grid is divided into rows and columns. Each column represents a field that will be included in the output.

How to Specify Criteria ?

To add criteria to an Access query, open the query in the Design view and identify the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criteria in the Criteria row for that field.

Defining Criteria in the Query

To control which records are displayed, you must define criteria in a query. The most common type of query is the Select Records query which will be discussed below.

To Define Criteria for Your Query :

STEP 1: Position your cursor in the criteria row in the field for which you wish to define the criteria.

STEP 2: Type the criteria.

To Save the Query :

STEP 1: Click the Save As icon.

STEP 2: Enter a name for the query.

STEP 3: Click OK.

Forms in Access

A Form in Access is a database object that you can use to create a user interface for a database application. A “bound” from is directly connected to a data source such as a table or query and can be used to enter, edit, or display data from that data source.

Creating a Form

A form is a database object that is used to enter or display data in a database.

To Create a Form :

๑ In the Navigation pane, select the table you want to use to create a form.

๑ Select the Create tab, locate the Forms group, and click the From command.

๑ Your form will be created and opened in Layout view.

๑ To save the form, click the save command on the Quick Access Toolbar.

Different Views :

From View – This view allows you to view, create and edit records.

Layout View – This view is similar to Design View but is more visually oriented in that each control displays real data. As a result, this is a very useful view for setting the size of controls or performing many other tasks that affect the visual appearance and usability of the form.

Design View – This view gives you a more detailed view of the structure of the form. You can see the header, detail, and footer sections for the form. You cannot see the underlying data while you are making design changes.

Report formatting and printing Report

Report can be based on tables or queries.

To Create a Report :

STEP 1: Open the table or query on which you are basing the report.

STEP 2: Click on the Create Tab.

STEP 3: Click on the Report in the Report group A repot is created in Layout View.

Formatting Report :

STEP 1: Right-click the report in the Navigation Pane Click Layout View.

STEP 2: Select the required controls and on the Format tab, in the Control Formatting group Click Conditional Formatting.

STEP 3: In the Conditional Formatting Rules Manager dialog box, click New Rule and select a rule type. Select an option from Edit the rule description. Select the formatting that you want to apply and click OK.

Printing Reports

To Print a Report :

STEP 1: Switch to Print Preview from View on the Design tab under Report Layout Tools.

STEP 2: Click the Print icon.

STEP 3: Click on OK.

🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛🖛 FAQs 🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚🖚

Q. What is a design grid ?

A. A design grid, also known as a grid system or layout grid, is a system for organizing and organizing design elements in graphic design, web layout, and other visual disciplines It is basically an invisible outline of horizontal vertical lines acts as a guide

The purpose of the design grid is to provide a sense of organization, coherence and visual structure within the design. By aligning elements within the grid, artists can create balance, harmony, and visually beautiful compositions. The grid helps create relationships between components, ensuring proper alignment and providing consistent spacing throughout the design.

Design grids can be based on different principles, such as modular grids, hierarchical grids, golden ratio grids, or baseline grids. These grids describe the overall layout, including the number and width of columns, the spacing between columns, and the placement of key structural elements

Websites can be created manually using tools such as rulers and guides in design software, or can be created with pre-defined templates or frameworks provided by design applications Many design software packages also offer web customization options, empowering designers tailoring the web to their specific design needs can do it.

Overall, a design grid is an important tool in graphic and grid design that helps designers create visually appealing and well-organized layouts through the assumption of regular alignment and spacing of elements lifting the oath.

Q. How to build a relationship between tables ?

A. Creating relationships between tables is key to building a database of relationships. This involves establishing links or connections between tables based on common fields or keys. Here is the general procedure for creating relationships between tables.

Importing tables: Start by importing the tables in your database schema. Each table represents a specific element or concept in your system.

Define Primary Key: Specifies the primary key for each table. The primary key table has a unique identifier for each record. It can be a single field or a combination of fields.

Show relationships: Analyze tables and show relationships between them. Relationships can be divided into three types: one-to-one, one-to-many and many-to-many.

Peer-to-peer relationships: In a peer-to-peer relationship, each record in one table is exactly related to a record in another table, and vice versa You can key one table basically placed on another table as a foreign key to establish a one-to-one relationship.

One-to-many relationships: In a one-to-many relationship, any record in one table can be related to multiple records in another table, but any record in table a on the second it is associated with the only record in the first table. Add that there is a foreign key to the table.

Many-to-many relationship: In a many-to-many relationship, multiple records in one table can be linked to multiple records in another table. When you want to represent a many-to-many relationship, you often come up with a third table called a join or association table. The connection table contains foreign keys that identify the primary key of the two tables that connect the relationship.

Also Read :-

Maximizing Your Earnings Potential with Freelancing

Invest In This App To Earn Big Money: How A Mangaluru Victim Was Scammed Out Of Rs 25 Lakh

How Screen Time Is Affecting Our Mental Health ? | The Dark Side of Technology

How To Get Free Goodies From Google ?

Preparing for the (IAS) exam | Best exam time table

What is Query ? The Query is like a simple question that you ask to find specific information from the database. To know any details of your friends form the database. ≫ Queries are a way of searching for and compiling data from one or more tables. ≫ Running a query is like asking a detailed…