Select From...SecID
Select From...SecID
(OP)
Can anyone help me with this SQL Query?
I have two tabels
TABEL A
SECID SECSYMBOL
US00111 AAA
US00222 BBB
US00333 CCC
US00333 DDD
US00333 EEE
US00333 FFF
TABEL B
SECSYMBOL SECNUM
AAA 1234
BBB 1235
CCC 1236
DDD 1237
EEE 1238
FFF 1239
This is what I would as a result.....
SECID SECNUM(1) SECNUM(2) SECNUM(3) SECNUM(4)
US00111 1234
US00222 1235
US00333 1236 1237 1238 1239
I have two tabels
TABEL A
SECID SECSYMBOL
US00111 AAA
US00222 BBB
US00333 CCC
US00333 DDD
US00333 EEE
US00333 FFF
TABEL B
SECSYMBOL SECNUM
AAA 1234
BBB 1235
CCC 1236
DDD 1237
EEE 1238
FFF 1239
This is what I would as a result.....
SECID SECNUM(1) SECNUM(2) SECNUM(3) SECNUM(4)
US00111 1234
US00222 1235
US00333 1236 1237 1238 1239
Talk To Other Members
RE: Select From...SecID
SELECT [Tabel A].SECID, [Tabel A].SECSYMBOL, Count([Tabel A_1].SECID) AS ColNum
FROM [Tabel A] INNER JOIN [Tabel A] AS [Tabel A_1] ON [Tabel A].SECID = [Tabel A_1].SECID
WHERE ((([Tabel A_1].SECSYMBOL)<=[Tabel A]![SECSYMBOL]))
GROUP BY [Tabel A].SECID, [Tabel A].SECSYMBOL;
and then
TRANSFORM First([Tabel B].SECNUM) AS FirstOfSECNUM
SELECT qrnkSECID.SECID
FROM [Tabel B] INNER JOIN qrnkSECID ON [Tabel B].SECSYMBOL = qrnkSECID.SECSYMBOL
GROUP BY qrnkSECID.SECID
PIVOT "SECNUM(" & [ColNum] & ")";
Maybe
RE: Select From...SecID
CODE
FROM (
SELECT A.SECID, B.SECNUM AS SecNum1, Null AS SecNum2, Null AS SecNum3, Null AS SecNum4
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=1
UNION SELECT A.SECID, Null, B.SECNUM, Null, Null
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=2
UNION SELECT A.SECID, Null, Null, B.SECNUM, Null
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=3
UNION SELECT A.SECID, Null, Null, Null, B.SECNUM
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=4
) U
GROUP BY SECID
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Select From...SecID
Thank you for this query. I have try to run this in MS-Access.
But then I get a syntax error.
Any Idea?
regards
RE: Select From...SecID
The MS-Access answer is in the thread you've started in the JetSQL forum.