Self Join for certain trans type also grab other trans type without pulling from other alias

[John1Chr]

I would like to grab the other side of the Trans Type="TR" and ignore the Trans Type= "R" if possible. The trans type ="R" appears to double up when I do a self join on the table. Is it possible to self join and grab only the trans type ="TR"? and pull in the R without it selfjoining or searching for the other side of the equation. The type "R" is where that dept. took the reduction and the trans type TR is where another dept took the reduction.

If i did it in two queries it would be easy I would just grab the type TRs in a self join and pull the type R only going after the table without a self-join. Perhaps this is what I will need to do?

Table 1
Trans ID Trans Date Dept Nbr Org Nbr Trans Type Amount Dept Trans To Org Tran To
519781 6/17/2012 A78 210 R 273
519781 6/17/2012 A78 510 R 19
519781 6/17/2012 A78 310 R 32
532470 6/28/2012 A78 526 TR 22 A9R 994


SELECT Table1.Trans ID, Table1.Trans Date, Left([Table1]![DeptOrg],3) AS DeptNmbr, Right([Table1]![DeptOrg],3) AS ORG, Table1.DeptOrg, Table1.Trans Type, Table1.Amount, Table1_1.Trans Type, Left([Table1_1]![DeptOrg],3) AS DeptIn, Right([Table1_1]![DeptOrg],3) AS ORGIN
FROM Table1 LEFT JOIN Table1 AS Table1_1 ON (Table1.Trans Date = Table1_1.Trans Date) AND (Table1.Trans ID = Table1_1.Trans ID)
WHERE (((Left([Table1]![DeptOrg],3)) Like "A78*") AND ((Table1.DeptOrg) Not Like "A9R*") AND ((Table1.Trans Type)="TR") AND ((Left([Table1_1]![DeptOrg],3)) Like "G9R*")) OR (((Left([Table1]![DeptOrg],3)) Like "A78*") AND ((Table1.Trans Type)="R"));

 

[PHV]

Please, post the REAL SQL code of the two RUNNING queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[John1Chr]

PHV - you are right - I gave you crap. Was starting to feel like there was no hope and my efforts were futile even though I kept trying. It would be awesome if this is possible!

This is the real stuff. I built tables off of our production tables and this is what I am using. So the names are a little odd. I tested and it is giving me what I want.

Query #1
SELECT TABLE1.TRANS_ID, TABLE1.TRANS_DATE, Left([TABLE1]![DEPTORG],3) AS ORG, Right([TABLE1]![DEPTORG],3) AS DEPT, TABLE1.ORG, TABLE1.TRANS_TYPE, TABLE1.AMOUNT, TABLE1_1.TRANS_TYPE, Left([TABLE1_1]![DEPTORG],3) AS ORGIN, Right([TABLE1_1]![DEPTORG],3) AS DeptORGIN
FROM TABLE1 LEFT JOIN TABLE1 AS TABLE1_1 ON (TABLE1.TRANS_DATE = TABLE1_1.TRANS_DATE) AND (TABLE1.TRANS_ID = TABLE1_1.TRANS_ID)
WHERE (((Left([TABLE1]![DEPTORG],3)) Like "A78*") AND ((TABLE1.ORG) Not Like "A9R*") AND ((TABLE1.TRANS_TYPE)="X") AND ((Left([TABLE1_1]![DEPTORG],3)) Like "A9R*"));

Query #2
SELECT TABLE1.TRANS_ID, TABLE1.TRANS_DATE, Left([DEPTORG],3) AS ORG, TABLE1.DEPTORG, TABLE1.TRANS_TYPE, TABLE1.AMOUNT INTO TABLE1
FROM TABLE1
WHERE (((Left([DEPTORG],3)) Like "A78*") AND ((TABLE1.TRANS_TYPE)="R"));

 

[PHV]

Why a SELECT ... INTO (Query#2) ???

 

[PHV]

Furthermore, what is the difference between TABLE1.ORG and TABLE1.DEPTORG ?

 

[John1Chr]

Using Select in 2nd query just to pull in one sided reductions that can be thrown into a report. Org is a higher level - Division level and DeptOrg is just a department within that division.