Sub Query

[ariklewis]

I have two tables

ID	Job#	Piece Mark	Description	Quantity	Length
1	2847	10AP1	W10X47	10	12
2	2847	10AP2	W11X35	8	10

ID	Major	Piece Mark	Description	Quantity	Length
1	1	a10	pl2x2	5	4
2	1	b10	pl2x2	4	4
3	2	a10	pl2x2	6	3
4	2	b10	pl2x2	2	3

The result I'm looking for is something along the lines of:

Job#	Piece Mark	Description	Quantity	Length
2847	10AP1	W10X47	10	12
2847	a10	pl2x2	5	4
2847	b10	pl2x2	4	4
2847	10AP2	W11X35	8	10
2847	a10	pl2x2	6	3
2847	b10	pl2x2	2	3

How can I achieve this?

[TheShabz]

What is your connection between the two source tables?

[ariklewis]

The connection is one Major (First Table) to many Detail (Second Table)

[TheShabz]

I dont see "Detail" as a field in either table

Further, is there a reason why the same type of data is being held in multiple tables? The only difference I see in those tables is Major and Job #. Is there any reason they cant exist in one table?

[ariklewis]

The name of the first Table is Major containing major piece marks. The second is Detail containing detail piece marks. Each item in the Major table has components (detail piece marks). The column "Major" in the 2nd or Detail table relates to which Major piece mark those detail piece marks belong to. When a major piece mark is updated, each detailed piece that correlates to that major needs to be updated. That's why they are separate. The job# field is just to connect them to a job. The relationship is one Job# to many Major and one Major to many Detail. But I don't think that the job table is needed for this query.

[ariklewis]

Does anyone know how this can be done?