Select From...SecID 1

[harfri]

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

 

[harfri]

When using MS-Acces this works fine:

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

 

[PHV]

A starting point for no more than 4 secnum per secid:

SELECT SECID, MAX(SecNum1) AS SECNUM_1, MAX(SecNum2) AS SECNUM_2, MAX(SecNum3) AS SECNUM_3, MAX(SecNum4) AS SECNUM_4
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
FAQ181-2886

 

[harfri]

Hi,

Thank you for this query. I have try to run this in MS-Access.
But then I get a syntax error.

Any Idea?

regards

 

[PHV]

You've posted in the ANSI_SQL forum, so I gave you an ANSI answer ...
The MS-Access answer is in the thread you've started in the JetSQL forum.