Query to show descriptions of two codes 2

[deedar]

In an Access Query, I have two tables; Dimension and Transaction.

Dimension Table has columns DimLabel, DimCode and DimDescription. Transaction Table has columns TransactionNumber, Amount, DimCode1 and DimCode2. Below are example values to explain the query: (Values are separated by commas just for illustration)

Dimension Table:
(B,101,Funding Source 1)
(B,102,Funding Source 2)
(B,103,Funding Source 3)
(C,102,Program 2)
(C,103,Program 3)
(C,104,Program 4)

Transaction Table:
(1,1000,101,102)
(2,2000,102,103)
(3,3000,103,104)

I have to build a query, which can produce the following output:
(1,1000,101,Funding Source 1,102,Program 2)
(2,2000,102,Funding Source 2,103,Program 3)
(3,3000,103,Funding Source 3,104,Program 4)

With the following query, I am getting the first 5 columns:

SELECT Transaction.TransactionNumber, Transaction.Amount, Transaction.DimCode1, Dimension.DimDescription, Transaction.DimCode2
FROM [Transaction], Dimension
WHERE (((Transaction.DimCode1)=[Dimension].[Dimcode]) AND ((Dimension.DimLabel)="B"));

But I am stuck as how to show the sixth column; i.e. Description of DimCode2 (I have to look for only those descriptions where Dimension.DimLabel=”C”)

 

[PHV]

SELECT T.TransactionNumber, T.Amount, T.DimCode1, D1.DimDescription, T.DimCode2, D2.DimDescription
FROM ([Transaction] AS T
INNER JOIN Dimension AS D1 ON T.DimCode1 = D1.Dimcode)
INNER JOIN Dimension AS D2 ON T.DimCode2 = D2.Dimcode
WHERE D1.DimLabel = 'B' AND D2.DimLabel = 'C';

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

 

[traingamer]

Add the dimension table a second time:

SELECT Transaction.TransactionNumber, Transaction.Amount, Transaction.DimCode1, Dimension.DimDescription, Transaction.DimCode2, [COLOR=blue]d2.DimDescription[/color]
FROM [Transaction], Dimension, dimension d2
WHERE (((Transaction.DimCode1)=[Dimension].[Dimcode]) AND ((Dimension.DimLabel)="B"))
[COLOR=blue] and (((Transaction.DimCode2)=[d2].[Dimcode]) AND ((d2.DimLabel)="B"))[/color];

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Of course d2.DimLabel="C" not "B". (and I like PHV's answer better anyway).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[deedar]

Thank you very much. Both solutions displayed accurate results on actual data.