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"));

 

[SkipVought]

hi,

This is puzzling.

If you want to 'ignore the Trans Type= "R"', then simply include

and [Trans Type] <> 'R'

HOWEVER, if your data structure allows STACKING types and what you REALLY mean is that because Trans Type equals 'TR' and, oh, by the way, 'TR' CONTAINS an 'R' so I don't want THAT one, then you really have a more basic NORMAILZATION problem with your database.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[John1Chr]

Hi,

I'm trying to grab the other side of the entry for TRs plus any Rs for that Org/DEPT in one query if possible. My self join grabs the transaction that contains where the reduction went for TRs but for Rs I know that I don't want to look at where the reduction went. I tried putting <>R in the self join side and it exlcudes all type Rs. The type Rs will multiply and double up by how many lines were in the trans id.

I'm thinking that I will need to do two separate queries.

 

[SkipVought]

the other side of the entry for TRs

What does that mean?

I guess I need a functional description of WHAT you need to accomplish, rather than HOW you think it ought to be done.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[John1Chr]

The other side of the TR is the deptorg where the reduction went. When I self-join by trans id, trans date I am able to get this by specifying in the field that the amountin on Table1 <0 and the amount for Table1_1 is >0. I might need to create a query and match the amounts by multiplying the less than zero by -1...not sure yet.

 

[SkipVought]

The other side of the TR is the deptorg where the reduction went.

Well then "the other side of the entry for TRs" is "inside jargon" that only YOU and others in YOUR ORGANIZATION understand, right? Or am I missing something?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[John1Chr]

Yes that is correct.

 

[John1Chr]

TR stands for transfer...sorry, didn't make that clear intially.

 

[SkipVought]

Do you realize that NO ONE understands what you are 'talking' about?

THAT is why I asked you to provide "a functional description of WHAT you need to accomplish, rather than HOW you think it ought to be done."

Please be CLEAR, CONCISE and COMPLETE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[John1Chr]

ok...I'll try again.

I am trying to link the transfer in dept/org to the tranfer out dept/org. An accounting entry will have the trans id line that will have the transfer in info on one line and another line will have the transfer out info. Usually this is line 1 and line 2 on a transaction id. However, there could be a line 3 or line 4. This information is all within the same table - in table 1. I am linking to match the transfer in to the transfer out dept/orgs. At the same time a dept/org may have a reduction and this is only on one line of a transaction for a dept/org. I want to extract from table 1 all of the transfers out and match to the other side the transfers in and all of the reductions for the dept/org within one query. I put the Sql out there to use as reference.

In my example above Dept Number A78 has three reductions and one transfer. Can I capture this info by doing a self join without it doubling up?

532470 6/28/2012 A78 526 TR 22 A9R 994 (This was transaction number 532470 transfer out that happened on 6/28/2012 in the amount of $22 from Dept org A78 526 to Dept Org A9R 994) I can easily get this info by selfjoining by trans id and trans date. The backend dept in info (A9r 994 ) is obtained from table1_1 and is line 2 on entry 532470. I believe that I should link by amount also. I was wondering if I could capture the onesided reductions that happened to the dept org at the same time as grabbing the transfers matched lines, also.

 

[SkipVought]

I am trying to link the transfer in dept/org to the tranfer out dept/org.

Well right there, you have not included a satisfactory EXAMPLE.

Please post a cogent example AND the result that you expect in conjunction with that example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

AND...

exactly WHAT is DeptOrg?

WHERE is THAT in your example?

This is getting 'curiouser and curiouser'!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[John1Chr]

I'm going to try to simplify what I want. I am hoping to pull both a transfer of a reduction and a reduction within a dept in one query. I was just curious if this was possible. I will drop it if not clear after this.

table A

trans nbr line nbr trans type amount dept(org)
1 1 A 10 A1
1 2 A -10 B1
2 1 R -05 C1
2 2 R -15 B1
2 3 R -04 D1


Trans type A is a two sided entry - one dept transfers its reduction to the other dept. Trans type R is a one sided entry - reduction is within the dept.


I can easily capture this result by selfjoining:

trans nbr line nbr trans type amount in dept(org) out dept(org)
1 1 A 10 A1 B1


Is there a way within the self join to make the query realize that trans type R results should not look within the selfjoin for the other side of the equation. Type R is a one-sided entry. I would want to see these results:

trans nbr line nbr trans type amount in dept(org) out dept(org) or reduced org
1 1 A 10 A1 B1
2 1 R -05 Blank C1
2 2 R -15 blank B1
2 3 R -04 Blank D1

 

[SkipVought]

Your moniker bespeaks your mind. Sorry I cannot break thru.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[John1Chr]

It is ok. I will create two queries.

I just wanted to see if a self-join could be performed to do a match for a certain transaction at the same time as excluding a certain transaction from grabbing from table_1. I don't think it can.

Thanks

 

[PHV]

I don't think it can
I think it can.
Please, provide the SQL code of your two queries solution.

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

 

[PHV]

I think at 2 possibilities:
1) either an UNION query
2) or a LEFT JOIN with an embedded view.

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

 

[John1Chr]

PHV - That's easy and just takes a second. Can you combine the two? Here it is:

1. This does the selfjoin to grab the other side of the Dept Transfer:

SELECT TABLE1.TRANS ID, TABLE1.TRANS DATE, Left([TABLE1]![DEPTORG],3) AS ORG, Right([TABLE1]![DEPTORG],3) AS ORG, TABLE1.DEPTORG, TABLE1.TRANS TYPE, TABLE1.AMOUNT, TABLE1_1.TRANS TYPE, Left([TABLE1_1]![DEPTORG],3) AS ORGIN, Right([TABLE1_1]![DEPTORG],3) AS ORGIN
FROM TABLE1 LEFT JOIN TABLE1 AS TABLE1_1 ON (TABLE1.TRANS ID = TABLE1_1.TRANS ID) AND (TABLE1.TRANS DATE = TABLE1_1.TRANS DATE)
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 "A9R*"));

2. This does the Pulling of the one sided Dept Reduction (not like A9R can be taken out it doesnt do anything):

SELECT TABLE1.TRANS ID, TABLE1.TRANS DATE, Left([TABLE1]![DEPTORG],3) AS ORG, Right([TABLE1]![DEPTORG],3) AS ORG, TABLE1.DEPTORG, TABLE1.TRANS TYPE, TABLE1.AMOUNT
FROM TABLE1
WHERE (((Left([TABLE1]![DEPTORG],3)) Like "A78*") AND ((TABLE1.DEPTORG) Not Like "A9R*") AND ((TABLE1.TRANS TYPE)="R"));

 

[PHV]

Sorry, but the code you posted can't be the SQL code of running queries (spaces in field names, duplicate alias, ...)

 

[John1Chr]

TRANS DATE should be TRANS_DATE
TRANS TYPE should be TRANS_TYPE
and TRANS ID should be TRANS_ID

I'm doing a selfjoin so running the table against itself.