Probably a join or group by question

[RichMixture]

Help please! What I have is:
Table1
ID,WO_Number

Table2
ID, dept, op code, op_code_desc

Table3
ID, line_no, part_no, part_desc

Example:
Table1:
365,W19555

Table2:
365,940,800,PAINT
365,949,730,CUT
365,949,750,ASSEMBLE

Table3:
365,1,89005,1/2x3/4 Plywood
365,2,88003,grommet
365,3,77723,window
365,4,00111,molding

The number of lines in tables 2 and 3 will usually not be the same. The desired output eventually is a work order report that will look something like:

W19555

Dept 940 800 PAINT
949 730 CUT
949 750 ASSEMBLE

1 89005 1/2x3/4 Plywood
2 88003 grommet
3 77723 window
4 00111 molding

I'm trying to write a query on which I will base a report, and I keep getting the line numbers repeated for each of the op code/operation descriptions. I'm pretty stuck. Any help is greatly appreciated!!! My query, which queries two other queries (one queries tables 1 and 2, the other queries table 3), returns:

365,W19555,1,89005,1/2x3/4 Plywood,PAINT
365,W19555,2,89005,1/2x3/4 Plywood,CUT
365,W19555,3,89005,1/2x3/4 Plywood,ASSEMBLE
365,W19555,1,89005,grommet,PAINT
365,W19555,2,89005,grommet,CUT
365,W19555,3,89005,grommet,ASSEMBLE
365,W19555,1,89005,window,PAINT
365,W19555,2,89005,window,CUT
365,W19555,3,89005,window,ASSEMBLE
365,W19555,1,89005,molding,PAINT
365,W19555,2,89005,molding,CUT
365,W19555,3,89005,molding,ASSEMBLE

I need some Tek-Tips heat! Thanks ever so much.

 

[lespaul]

try this (it may need a little tweaking on the joins)

SELECT T1.WO_NUMBER, T2.DEPT, T2.OP_CODE, T2.OP_CODE_DESC, T3.LINE_NO, T3.PART_NO, T3.PART_DESC FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.ID = T2.ID
INNER JOIN TABLE3 AS T3 ON T2.ID = T3.ID

This should work directly on the tables instead of running multiple queries.

You should also be able to build this query in the design view by adding the three tables, there should be relationship lines between the ID fields. You can change those relationships within the query without affecting the "real" relationships. Check those relationships and see if there's on going from table1 to table2 and one from table3 to table1. If so, maybe by changing the relationship of table3 to be to table2, you might get the results you are expecting.

HTH


Leslie

 

[Transcend]

Hi there

where you have

Table2
ID, dept, op code, op_code_desc

Table3
ID, line_no, part_no, part_desc

are those actually the ID fields that reference the ID in table one? The reason I ask is because if ID is the primary key in table2 then you have a serious problem! A primary key shouldn't have the same value more than once.

I assume that those ID fields are actually foreign keys referencing table1 and that these two tables have no primary key?

Transcend

 

[Transcend]

or am i completely wrong and the primary keys in table2 and table3 are multi-column .... ?

Transcend

 

[lespaul]

I assumed Table2 and Table3 had multiple field keys?

Leslie

 

[Transcend]

yah ... typed out that first response before my brain got into gear i think

Transcend