Multiple Columns from One Column

[Guest]

I have two tables in my database. One is a master containing an Item# Field
and a Item Description Field. The other table is a Bill of Materials table.
This table has and Item # Field, Component # Field and QTY Field. The
components are built from the master table. I would like to build a query
with all five fields from both tables with an additional sixth field. This
sixth field would be a repeat of the Item Description field from the Master
table (the Item Description and the Component Description are the same as
well as the Item # and Component #). How can I do this?

 

[Guest]

Couldn't you just name the field REPEAT:

![tblMaster].[Item_Description]?

-Steve

 

[Steve Schapel]

JK,

I can't quite get a grasp on what is the relationship between these two
tables, and what this 6th field would look like, and if it's just a
duplicatation of another field's value, what purpose you are trying to
achieve. Can you post back with some more details, particularly with a
couple of examples?

 

[Guest]

Lets say I have an items in my master as such:

Item # Description
020000 4" x 6" ALABAMA FLAG
030000 ALABAMA ROLL GOODS
040000 PLASTIC STAFF
050000 LABOR

In my Bill of Materials table I have this:

Item # Component # QTY
020000 030000 1.0000
020000 040000 1.0000
020000 050000 2.0000

I would like the query to show:

Item # Description Component # Description QTY
020000 4" x 6" ALABAMA FLAG 030000
1.0000
020000 4" x 6" ALABAMA FLAG 040000
1.0000
020000 4" x 6" ALABAMA FLAG 050000
2.0000

I would like the blank Description field above to be filled in with the
Descriptions of the master table.

 

[Steve Schapel]

JK,

Ok, thanks for the further explanation... now I understand

For this, you will need to include your Master table twice in the query,
once to relate to the Item # field in the Bill of Materials, and once to
relate to the Component # field. The SQL view of such a query will look
something like this...

SELECT Master.[Item #], Master.Description, [Bill of
Materials].[Component #], Master_1.Description, [Bill of Materials].Quantity
FROM (Master INNER JOIN [Bill of Materials] ON Master.[Item #] = [Bill
of Materials].[Item #]) INNER JOIN Master AS Master_1 ON [Bill of
Materials].[Component #] = Master_1.[Item #]

By the way, as an aside, it is not a good idea to include a # as part of
a field name.