Figure CE11-5, the sample bill of materials, is a form produced using Microsoft Access.

Producing such a form is a bit tricky, so this exercise will guide you through the steps required.

You can then apply what you learn to produce a similar report. You can also use Access to experiment on extensions of this form.

a. Create a table named PART with columns Part Number, Level, Description, QuantityRequired, and PartOf. Description and Level should be text, PartNumber should be AutoNumber, and Quantity Required and PartOf should be numeric, long integer. Add the PART data shown in Figure CE11-5 to your table.

b. Create a query that has all columns of PART.

Restrict the view to rows having a value of 1 for Level. Name your query Level1.

c. Create two more queries that are restricted to rows having values of 2 or 3 for Level. Name your queries Level2 and Level3, respectively.

d. Create a form that contains PartNumber, Level, and Description from Level1. You can use a wizard for this if you want. Name the form Bill of Materials.

e. Using the subform tool in the Toolbox, create a subform in your form in part

d. Set the data on this form to be all of the columns of Level2.

After you have created the subform, ensure that the Link Child Fields property is set to PartOf and that the Link Master Fields property is set to PartNumber. Close the Bill of Materials form.

f. Open the subform created in part e and create a subform on it. Set the data on this subform to be all of the columns of Level3. After you have created the subform, ensure that the Link Child Fields property is set to PartOf and that the Link Master Fields property is set to PartNumber. Close the Bill of Materials form.

g. Open the Bill of Materials form. It should appear as in Figure CE11-5. Open and close the form and add new data. Using this form, add sample BOM data for a product of your own choosing.

h. Following the process similar to that just described, create a Bill of Materials Report that lists the data for all of your products.

i. (Optional, challenging extension) Each part in the BOM in Figure CE11-5 can be used in at most one assembly (there is space to show just one PartOf value). You can change your design to allow a part to be used in more than one assembly as follows: First, remove PartOf from PART.

Next, create a second table that has two columns:

AssemblyPartNumber and ComponentPart Number. The first contains a part number of an assembly and the second a part number of a component. Every component of a part will have a row in this table. Extend the views described above to use this second table and to produce a display similar to Figure CE11-5.