Bill of Material question 1

[emax6]

I posted this message in another forum, but I always cannot get it right.

I have a master item table that give me the details for each product. For ex:
Tbl1.ItemNb, Tbl1.Color, etc,,

I have a table that gives me the list of all components(items) for a specific product(item):
Tbl2.Item1, Tbl2.Component1
Tbl2.Item1, Tbl2.Component2
Tbl2.Item1, Tbl2.Component3
Etc…

Since I need to have the detail for let’s say Tbl2.Component1, I need to link Tbl2.Component1 to Tbl1.ItemNb.

Is it possible to create a SQL query that will select Tbl1.ItemNb with the list of all component listed in Tbl2, and for each component from Tbl2, the detail for each component that I can retrieve from the Tbl1.

The result of the query should return something like:
Item1,Component1, color
Item1, component2,color
etc..

Hope the explanation is clear.

 

[r937]

select assembly.ItemNb as assembly_ItemNb
     , assembly.Color  as assembly_Color
     , component.ItemNb as component_ItemNb
     , component.Color as component_Color
  from Tbl1 as assembly
inner
  join Tbl2
    on Tbl2.Item = assembly.ItemNb
inner
  join Tbl1 as component
    on component.Item = Tbl2.Component

r937.com | rudy.ca

 

[emax6]

Hi,
If I try this in MS Access, I am getting an error message 'Sintex error, missing operator....'
Is this du to MS Access?

 

[emax6]

May be mor info:

Table: Tbl1
ItemNb Color
a red
b blue
c pink
d purple
e yellow
f none
g black

Table: Tbl2
ItemNb Component
a c
a e

 

[r937]

why did you post in the SQL Server forum? there are Access forums, you know

anyhow...

Access requires that you parenthesize your joins

  from (
       Tbl1 as assembly
inner
  join Tbl2
    on Tbl2.Item = assembly.ItemNb
       )
inner
  join Tbl1 as component
    on component.Item = Tbl2.Component

r937.com | rudy.ca

 

[emax6]

Sorry, I was home and I only have ACCESS here.
Work like a charm. I will have to apply to the realstable.
Thanks for this fast help.