Select From....Security ID 2

[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
Can someone help me on this I would like the report to show:

SECID SECNUM(1) SECNUM(2) SECNUM(3) SECNUM(4)
US00111 1234
US00222 1235
US00333 1236 1237 1238 1239

 

[dhookom]

Since you want this on a report, just create a main report based on the unique SECID values. Then create a multi-column subreport of the TABEL A joined to TABEL B. Use the SECID as the link master/child properties of the subreport.

Duane
Hook'D on Access
MS Access MVP

 

[harfri]

I would rather use SQL instead. Do you know how this can be done?

thanks in advance

 

[dhookom]

Sure, you first create a ranking query so you number the records 1, 2, 3, and 4 within each group. Then you create a crosstab based on the ranking query where the Column Heading is the "ranking" field. You can't expect to use a subquery to build the ranking field.

Duane
Hook'D on Access
MS Access MVP

 

[harfri]

Can you give me a hand...Or is it really difficult for an expert.

thanks in advance

Maybe Swampboogie knows how to solve this

 

[harfri]

Can someone help me with this query plz

 

[dhookom]

Your first query to get the ranking would be:
qrnkSECID

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;

Then create your crosstab like:

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

Duane
Hook'D on Access
MS Access MVP

 

[harfri]

Hi,

The first step works fine But i do not understand how to run the second step.

Is it possible to do this in 1 query?

Many thanks for your help.

 

[dhookom]

Did the second query work? If not, what were your results. Why not use 2 queries? I can probably do it with one using a domain aggregate function but they are slow.

Duane
Hook'D on Access
MS Access MVP

 

[harfri]

The first part works I have copy and paste your query.

But I do not know how I have to run the second part....

Regards
Harrie

 

[harfri]

This is what i have know:


SECID SECSYMBOL ColNum
US00111 AAA 1
US00222 BBB 1
US00333 CCC 1
US00333 DDD 2
US00333 EEE 3
US00333 FFF 4

 

[harfri]

I have to tabels in MS

SECID SECSYMBOL
US00111 AAA
US00222 BBB
US00333 CCC
US00333 DDD
US00333 EEE
US00333 FFF


SECSYMBOL SECNUM
AAA 1234
BBB 1235
CCC 1236
DDD 1237
EEE 1238
FFF 1239
GGG 1240

 

[harfri]

Hi Dhookom,

After some try and error from my side it works perfectly

SECID SEC(1) SEC(2) SEC(3) SEC(4)
US00111 1234
US00222 1235
US00333 1236 1237 1238 1239


But what if I would like to convert this into PLSQL?
Will this query work?

 

[dhookom]

If you have a PLSQL questions, you might want to ask in a PLSQL forum.

Duane
Hook'D on Access
MS Access MVP

 

[harfri]

Hi dhookom,

Thank you for your help. I will and have posted my question on some other forum also.

Thanks so far

 

[PHV]

Is it possible to do this in 1 query?

SELECT SECID, MAX(SecNum1) AS [SEC(1)], MAX(SecNum2) AS [SEC(2)], MAX(SecNum3) AS [SEC(3)], MAX(SecNum4) AS [SEC(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 PHV,

Sorry that I have confused you. For me it was not so clear where to post my question.

If have run your query but I without result. Is it because of a SQL Dialect?

regards