Bill of Materials Query - Selecting the rightmost column

[pkahlo]

I'm trying to use a query to determine all the individual items in a Bill of Materials. I have two tables: Items and ItemAssemblies.

Items descibes all the information about each item including items that are parents of items in the same table. ItemAssemblies only includes three fields: ItemAssemblyID, ItemID, Quantity. ItemAssemblyID and ItemID are the primary keys. This table serves only to determine the BOM hierachy.

Using this self-join query I can output this multi-level hierachy:

SELECT ItemAssemblies.AssemblyID, ItemAssemblies.ItemID, ItemAssemblies.Quantity, ItemAssemblies_1.ItemID, ItemAssemblies_1.Quantity, ItemAssemblies_2.ItemID, ItemAssemblies_2.Quantity, ItemAssemblies_3.ItemID, ItemAssemblies_3.Quantity, ItemAssemblies_4.ItemID, ItemAssemblies_4.Quantity
FROM (((ItemAssemblies LEFT JOIN ItemAssemblies AS ItemAssemblies_1 ON ItemAssemblies.ItemID = ItemAssemblies_1.AssemblyID) LEFT JOIN ItemAssemblies AS ItemAssemblies_2 ON ItemAssemblies_1.ItemID = ItemAssemblies_2.AssemblyID) LEFT JOIN ItemAssemblies AS ItemAssemblies_3 ON ItemAssemblies_2.ItemID = ItemAssemblies_3.AssemblyID) LEFT JOIN ItemAssemblies AS ItemAssemblies_4 ON ItemAssemblies_3.ItemID = ItemAssemblies_4.AssemblyID;

This is some sample output:
AssemblyID ItemAssemblies.ItemID Quantity ItemAssemblies_1.ItemID Quantity
CD - Packaged CD Label 1
CD - Packaged CD Sleeve 1
CD - Packaged CD-R 700 MB 1
CD - Packaged Mailing seal 1
New Install CD CD - Packaged 1 CD Label 1
New Install CD CD - Packaged 1 CD Sleeve 1
New Install CD CD - Packaged 1 CD-R 700 MB 1
New Install CD CD - Packaged 1 Mailing seal 1


Given an AssemblyID, I want to be able to output all of the items in the Assembly Group. Although this query DOES do this, I only want to select the 2 rightmost columns (ItemID and Quantity) in each row

How do I do this?