Chapter AE, Problem AE7.2

Explanation of Solution

a.

Creating Table:

Step 1: Open MS Access from your computer. Take a blank desktop database.

Screenshot of MS Access

Step 2: Give the database name as “BOM” and click the “Create” option.

Screenshot of MS Access

Step 3: To create a table, click “CREATE” from the menu bar and then choose “Table” from the options. A table, named “Table1” will be created. Right click “Table1” and then select “Design view” from the list. A new window pops up. Enter the table name as “PART” and then click “Ok” button.

Screenshot of MS Access

Explanation of Solution

b.

Writing Query:

Step 1: Select “CREATE” option from the menu bar and then choose “Query Design” from it.

Screenshot of MS Access

Step 2: Close the “Show Table” dialog box and click “SQL View” from the top left corner. A query form gets displayed as shown below...

Explanation of Solution

c.

Writing “Level 2” and “Level 3” queries:

Create another “Query design” and write the below “Level 2” query.

Query “level 2”: SELECT * FROM PART WHERE (Level <> "2");

After writing the query save it by pressing “Ctrl+S” and name it as “Level 2”.

Screenshot of MS Access

Right click “Level 2” and then choose “Datasheet View” to view the result.

Screenshot of MS Access

Explanation of Solution

d.

Bill of Materials Form:

Step 1: To open form, click “CREATE” option from the menu bar and then select “Form” from it as shown below.

Screenshot of MS Access

Step 2: Click and delete “QuantityRequired” and “PartOf” fields form it...

Explanation of Solution

e.

Creating Subform/ Subreport:

Step 1: Right click “Bill of Materials” form and choose “Design View” from the list.

Screenshot of MS Access

Step 2: Choose “DESIGN” from the menu bar and then select “Subform/ Subreport” option as shown below.

Screenshot of MS Access

Step 3: Drag the mouse and create a box as shown below.

Screenshot of MS Access

Step 4: After leaving the mouse, a SubFrom Wizard appears. Choose the category as shown below and click “Next” button.

Screenshot of SubForm Wizard

Step 5: Select “Level 2” and then move all the fields to the other side as shown below...

Explanation of Solution

f.

Creating Subform:

Step 1: Right click and open “Level 2 Subform” as shown below. Set it to “Design View” to add a Subform in it.

Screenshot of MS Access

Step 2: Add the Subform and link the fields as shown below. Close the tab after completing.

Screenshot of MS Access

Explanation of Solution

g.

Open “Bill of Materials” form:

After opening the form, it looks like “FIGURE AE-8”. Click “ENTER” button on the keyboard to view the fields from “FIGURE AE-8”.

Screenshot of MS Access

Screenshot of MS Access

Explanation of Solution

h.

The added new items gets reflect in the queries “Level 1”, “Level 2”, and “Level 3” and it is shown below.

Result of “Level 1” query is shown below.

Screenshot of MS Access

Result of “Level 2” query is shown below...

Explanation of Solution

i.

Bill of Materials result:

Screenshot of MS Access

Screenshot of MS Access

Screenshot of MS Access