MIS 1

Lab Exercise: Create a BOM in Access

This Lab is to create a sample of bill of materials, as shown below:

In addition, you will be asked to design queries to calculate the minimal amount to order for each of the level 3 parts. To accomplish the task, you shall follow the steps below:

Step 1: Create a table named PartLevel1 with the following columns:

· PartNumber (type: Number).

· Description (type: Text)

· InventoryLevel (type: Number)

· OrderQuantity (type: Number)

Note. You need to specify PartNumber as the primary key.

Make sure you don’t leave any space between the words. E.g. Part Number will not work.

From the table design view, it should look like below:

Enter the record for this table: only one data record for this level of part (level 1):

PartLevel1
PartNumber	Description	InventoryLevel	OrderQuantity
1	Wagon	0	100

Step 2: Create another table named PartLevel2 with the following columns:

· PartNumber (type: Number, Primary Key).

· Description (type: Text)

· PartOf (type: Number). i.e. the part number which this part is used to produce.

· QuantityPerParentPart (type: Number)

· InventoryLevel (type: Number)

Enter data records as show below.

PartLevel2
PartNumber	Description	PartOf	QuantityPerParentPart	InventoryLevel
2	HandleBar	1	1	30
5	WagonBody	1	1	10
6	Front Wheel Assembly	1	1	20
10	Rear Wheel Assembly	1	1	150

Step 3: Create another table named PartLevel3 with the same data columns as PartLevel2. Add the following records:

PartLevel3
PartNumber	Description	PartOf	QuantityPerParentPart	InventoryLevel
3	Bar Grip	2	1	100
4	Bar Tang	2	1	50
7	Front Wheels	6	2	50
8	Front Axel	6	1	80
9	Front Wheel Retainer	6	2	80
11	Rear Wheels	10	2	20
12	Rear Axel	10	1	10
13	Rear Wheel Retainer	10	2	20
14	Bar Stock	2	1	150

Step 4: Go to “Database Tools” ( “Relationships” to link the relevant fields.

Warning : you should save the relationship by closing this view before you continue to next step.

Step 5. CALreate the BOM Form.

Click table PartLevel1, and go to the menu “Create” ( “Form”.

Adjust the format. Name the form “BOM”. Your form should look exactly like the one shown at the beginning of this instruction (page 1).

Step 6: Create query to calculate the quantity that need to be produced/purchased for Level 2 parts.

Go to the menu “Create” ( “Query Design”. Add table PartLevel1 and PartLevel2 to the design interface. Choose the following data fields in the query:

· PartNumber from PartLevel2

· Description from PartLevel2

· A field named “QuantityNeed” which should be calculated from expression:

QuantityNeed:([PartLevel1].[OrderQuantity] - [PartLevel1].[InventoryLevel]) * [PartLevel2].[QuantityPerParentPart] -[PartLevel2].[InventoryLevel]

Note:

1. The “:” sign separates the name of the data field “QuantityNeed” from the formula which should be used to calculate the value of this field.

2. The formula, following the “:” sign, shows which data fields you use to calculate the value.

a. PartLevel1.OderQuantity - PartLevel1.InventoryLevel = the net quantity you need for the Wagon (Level 1 part).

b. The result above multiplies the PartLevel2.QuantityPerParentPart field is how many you need for each Level 2 product to produce that many Wagon

c. The above result – PartLevel2.Inventory = the net quantity you need to order or produce.

3. The QuantityNeed field might show a negative number, which indicates excess inventory. In order to show only positive number, you can add under “criteria” a condition “>0”.

See the figure below:

Name the query “Level2Query”. Now open and view the query, which should look like below.

Step 7. Now you are to add another query. Go to the menu, “Create” ( “Query Design”. Add table PartLevel3 and query Level2Query to the design view.

Then add the data fields

The “QuantityNeed” field should be adding the following expression:

QuantityNeed:

[Level2Query].[QuantityNeed]*[PartLevel3].[QuantityPerParentPart]-[PartLevel3].[InventoryLevel]

Save the query as Level3Query. Open it to view the result.

Level3Query
PartNumber	Description	QuantityNeed
4	Bar Tang	20
7	Front Wheels	110
9	Front Wheel Retainer	80

Step 8. Testing:

Go to table PartLevel1 and change the order quantity to 500 and inventory level to 100 View the results in Level2Query and Level3Query.

For submission, please copy paste your screen shots for Level 2 Query and Level 3 Query below (with Part Level 1 Quantity 500).

Right click the space and choose “Zoom” to enter the codes above.

Put the criteria to show only positive number

Click Queries tag to find Level2Query