How do I join field names with row values?

[prakus]

I have two tables TblA and TblB

Columns in TblA are (UID, UAbbr, UName,TID)

Examples of some rows of TblA are

	UID 	UAbbr 	UName		TID
	---	-----	-------------	-----
	1	C1	ClassA		1
	2	C2	ClassB		1
	3	C3	ClassC		1
	5	C4	ClassE		1
	6	C5	Grade4		1
	10	C6	Grade5		1

Columns in TblB are (C1, C2,C3,C4,C5, C6, Yr, CID, DID)
Examples of some rows of TblB are:

C1 	C2	C3	C4	C5 	C6 	Yr	CID 	DID
-----------------------------------------------------------------------
20024	2517	998	619	24158	1077	1998	1	0
20227	2477	947	598	24249	1095	1999	1	11
20420	2534	1121	642	24717	1065	1996	6	62
20518	2413	976	651	24558	1090	2000	5	54
20806	2532	1048	636	25022	1094	1997	5	54

for a given CID, DID and Yr, I want to tie the column values of column C1, C2, C3,... from TblB with the UName (ClassA, ClassB, ClassC) in TblA through the UAbbr in TblA.

I would like to have the final results in the following format
(For CID= 1 and DID = 11 and Yr = 1999):

	UID 	UAbbr 	UName		TID	Value
	---	-----	-------------	-----	------
	1	C1	ClassA		1	20227	
	2	C2	ClassB		1	2477	
	3	C3	ClassC		1	947
	5	C4	ClassE		1	598
	6	C5	Grade4		1	24249
	10	C6	Grade5		1	1095

Could anyone suggest a way to get to this?
Any help will be appreciated.

Thank you.
PKS

 

[jbenson001]

YOu really need an ID to relate the 2 tables

 

[TJRTech]

I suspect you will need a UNION select with 6 clauses:

SELECT UID, UAbbr, UName, TID, C1 as Value
FROM TblA, TblB
WHERE TblA.UAbbr='C1'
UNION
SELECT UID, UAbbr, UName, TID, C2 as Value
FROM TblA, TblB
WHERE TblA.UAbbr='C2'
UNION
SELECT UID, UAbbr, UName, TID, C3 as Value
FROM TblA, TblB
WHERE TblA.UAbbr='C3'
UNION
SELECT UID, UAbbr, UName, TID, C4 as Value
FROM TblA, TblB
WHERE TblA.UAbbr='C4'
UNION
SELECT UID, UAbbr, UName, TID, C5 as Value
FROM TblA, TblB
WHERE TblA.UAbbr='C5'
UNION
SELECT UID, UAbbr, UName, TID, C6 as Value
FROM TblA, TblB
WHERE TblA.UAbbr='C6'

Now, the above does seem a little strange in that there is nothing actually linking TblA with TblB, but instead each specific C1, C2, etc record of TblA is linked with each record of TblB in each individual UNION. But, that seems to be what you want.

TJR