How do I create queries in MS Access to add new fields and combine fields from multiple tables?

Question

ScanSolve AI Tutor · Accepted Answer

Here are the steps to complete the tasks:

QUERY ONE: Creating queries with new fields

Go to the Create tab on the ribbon.
In the Queries group, click Query Design.
In the Show Table dialog box, select the personal details table and click Add, then Close.
Double-click on the fields Paynum, Forename, Department, and Birthdate to add them to the query grid.
In the next empty field column in the query grid, type `Age
Date()-[Birthdate]in theField` row.
Click Run in the Results group on the Design tab to view the query results.
Click Save on the Quick Access Toolbar or File > Save.
Type Personnel ages as the query name and click OK.

QUERY TWO: Create another select query using fields from various tables
Go to the Create tab on the ribbon.
In the Queries group, click Query Design.
In the Show Table dialog box, select personal details, Appointment, and Payments tables.
Click Add for each, then Close.
Double-click on the following fields to add them to the query grid
- From personal details: Paynum, Surname, Department
- From Appointment: Appointed, Salary
- From Payments: Tax, Medical aid, Pension
In the next empty field column in the query grid, type NET SALARY:[SALARY]-[TAX]-[MEDICAL AID]-[PENSION] in the Field row.
Click Run in the Results group on the Design tab to view the query results.
Click Save on the Quick Access Toolbar or File > Save.
Type net salaries as the query name and click OK.

QUERY THREE: CREATING CROSSTAB QUERIES
Go to the Create tab on the ribbon.
In the Queries group, click Query Wizard.
Select Crosstab Query Wizard and click OK.
In the first step of the wizard, select `Table
personal detailsand clickNext`.
For the row heading, select Nationality and click Next.
For the column heading, select Department and click Next.
For the calculation, select Paynum and choose Count as the function.
Ensure Yes, include row sums is selected. Click Next.
Type Department by nationality summary as the query name.
Click Finish to view the results.
QUERY FOUR: CREATING ACTION (UPDATE) QUERY
Go to the Create tab on the ribbon.
In the Queries group, click Query Design.
In the Show Table dialog box, select the appointment table and click Add, then Close.
In the Query Type group on the Design tab, click Update.
Double-click on the Salary field to add it to the query grid.
In the Update To row for the Salary field, type [salary]*1.15.
Click Run in the Results group on the Design tab.
A warning message will appear indicating that you are about to update records. Click Yes to proceed.
Open the appointment table to verify that the salaries have been increased by 15%.

How do I create queries in MS Access to add new fields and combine fields from multiple tables?

Step-by-step solution

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Design`.

In the `Show Table` dialog box, select the `personal details` table and click `Add`, then `Close`.

Double-click on the fields `Paynum`, `Forename`, `Department`, and `Birthdate` to add them to the query grid.

In the next empty field column in the query grid, type `Age

Click `Run` in the `Results` group on the `Design` tab to view the query results.

Click `Save` on the Quick Access Toolbar or `File > Save`.

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Design`.

In the `Show Table` dialog box, select `personal details`, `Appointment`, and `Payments` tables.

Double-click on the following fields to add them to the query grid

In the next empty field column in the query grid, type `NET SALARY:[SALARY]-[TAX]-[MEDICAL AID]-[PENSION]` in the `Field` row.

Click `Run` in the `Results` group on the `Design` tab to view the query results.

Click `Save` on the Quick Access Toolbar or `File > Save`.

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Wizard`.

Select `Crosstab Query Wizard` and click `OK`.

In the first step of the wizard, select `Table

For the row heading, select `Nationality` and click `Next`.

For the column heading, select `Department` and click `Next`.

For the calculation, select `Paynum` and choose `Count` as the function.

Type `Department by nationality summary` as the query name.

Click `Finish` to view the results.

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Design`.

In the `Show Table` dialog box, select the `appointment` table and click `Add`, then `Close`.

In the `Query Type` group on the `Design` tab, click `Update`.

Double-click on the `Salary` field to add it to the query grid.

In the `Update To` row for the `Salary` field, type `[salary]*1.15`.

Click `Run` in the `Results` group on the `Design` tab.

Open the `appointment` table to verify that the salaries have been increased by 15%.

Still stuck on this one?

More Computer Science Questions

How do I create queries in MS Access to add new fields and combine fields from multiple tables?

Step-by-step solution

Go to the Create tab on the ribbon.

In the Queries group, click Query Design.

In the Show Table dialog box, select the personal details table and click Add, then Close.

Double-click on the fields Paynum, Forename, Department, and Birthdate to add them to the query grid.

In the next empty field column in the query grid, type `Age

Click Run in the Results group on the Design tab to view the query results.

Click Save on the Quick Access Toolbar or File > Save.

Go to the Create tab on the ribbon.

In the Queries group, click Query Design.

In the Show Table dialog box, select personal details, Appointment, and Payments tables.

Double-click on the following fields to add them to the query grid

In the next empty field column in the query grid, type NET SALARY:[SALARY]-[TAX]-[MEDICAL AID]-[PENSION] in the Field row.

Click Run in the Results group on the Design tab to view the query results.

Click Save on the Quick Access Toolbar or File > Save.

Go to the Create tab on the ribbon.

In the Queries group, click Query Wizard.

Select Crosstab Query Wizard and click OK.

In the first step of the wizard, select `Table

For the row heading, select Nationality and click Next.

For the column heading, select Department and click Next.

For the calculation, select Paynum and choose Count as the function.

Type Department by nationality summary as the query name.

Click Finish to view the results.

Go to the Create tab on the ribbon.

In the Queries group, click Query Design.

In the Show Table dialog box, select the appointment table and click Add, then Close.

In the Query Type group on the Design tab, click Update.

Double-click on the Salary field to add it to the query grid.

In the Update To row for the Salary field, type [salary]*1.15.

Click Run in the Results group on the Design tab.

Open the appointment table to verify that the salaries have been increased by 15%.

Still stuck on this one?

More Computer Science Questions

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Design`.

In the `Show Table` dialog box, select the `personal details` table and click `Add`, then `Close`.

Double-click on the fields `Paynum`, `Forename`, `Department`, and `Birthdate` to add them to the query grid.

Click `Run` in the `Results` group on the `Design` tab to view the query results.

Click `Save` on the Quick Access Toolbar or `File > Save`.

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Design`.

In the `Show Table` dialog box, select `personal details`, `Appointment`, and `Payments` tables.

In the next empty field column in the query grid, type `NET SALARY:[SALARY]-[TAX]-[MEDICAL AID]-[PENSION]` in the `Field` row.

Click `Run` in the `Results` group on the `Design` tab to view the query results.

Click `Save` on the Quick Access Toolbar or `File > Save`.

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Wizard`.

Select `Crosstab Query Wizard` and click `OK`.

For the row heading, select `Nationality` and click `Next`.

For the column heading, select `Department` and click `Next`.

For the calculation, select `Paynum` and choose `Count` as the function.

Type `Department by nationality summary` as the query name.

Click `Finish` to view the results.

Go to the `Create` tab on the ribbon.

In the `Queries` group, click `Query Design`.

In the `Show Table` dialog box, select the `appointment` table and click `Add`, then `Close`.

In the `Query Type` group on the `Design` tab, click `Update`.

Double-click on the `Salary` field to add it to the query grid.

In the `Update To` row for the `Salary` field, type `[salary]*1.15`.

Click `Run` in the `Results` group on the `Design` tab.

Open the `appointment` table to verify that the salaries have been increased by 15%.