Microsoft Access

What are relationships?
Relationship establishes relation or link between two or more than two database tables. That means in a relational database (Access), the data in one table is related to the data in other tables. We use Relationship in three different ways: one-to-one, one-to-many or many-to-many.
One to One:
In a one-to-one relationship, 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.
One to Many:
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.
Many to Many:
In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A.
Relationships between tables
1. Create two different tables in which at least one common field with common records.
2. Click on Database Tools--> Relationships

3. After then Show Tables box will appear. Here add required tables one by one.

4. Now select table1 common field, drag it holding with left mouse button and release button on common field of table2.
5. After then Edit Relationships box will appear. Here click on Create button.

What is Query?
A query is a request for data results depend up on criteria. Actually Query allows us to put criteria on one or more than one database table on multiple fields to extract record accordingly.
1. Click on CREATE --> Query Design button on the Ribbon.

2. Choose the tables to include in the query from list box.

3. Choose the fields to include, and put the criteria

4. Click on Run button.

Query with Criteria
Query criteria helps you to retrieve specific items from an Access database depend up on matches with all the criteria. it appears in the query results, an item matches with all the criteria.
Example
Assume that a table(named tblStud) contains fields StudName, Roll, Course, Add, Funda, Windows and Word.(Marks of windows and word).
Now display only those records whose name start with "A", course is "ADCA" and marks of word is greater than 45 and less than 80.
1. Create table with required fields.
2. Go to the Create tab and click on Query Design.
3. In the Tables tab on Show Table dialog, double-click on the tblStud table and then close the dialog box.
4. Let us now add some field to the query grid such as StudName, Roll, Course, Add, Funda, Windows and Word as shown in the following screenshot.

Create an Update Query
An update query provides to change the data in tables with specific expression, and can use an update query to enter criteria to specify which rows should be updated.
You use update queries in Access databases to add, change, or delete the information in an existing record. You can think of update queries as a powerful form of the Find and Replace dialog box.
Q: Assume that a table(stud) contains following fields. Tot field has no any data, now update and fill total value.

Steps:
1. Open the database that contains the records you want to update.
2. On the Create tab, in the Queries group, click Query Design -->Update.
The query designer opens, and the Show Table dialog box opens.
3. Click the Tables tab(stud).
4. Select the table or tables that contain the records that you want to update, click Add, and then click Close.