Select from ? code

[rzff]

I hope somebody can help me with the next problem.
There is one table

Table: codes

Artnr Code_TYPE CODE_ID
1000 1 12345
1000 2 44444
1003 2 33333
1001 1 45554
1002 1 88888
1002 2 99999
1003 2 77777
1004 2 11111
1004 3 22222

I would like to have a query that will give me the following result

Artnr CODE(1) CODE(2) CODE(3)
1000 12345 44444 33333
1001 45554
1002 88888 99999
1003 77777
1004 11111 22222

I hope somebody can help me out

Regards
RZFF

 

[scriverb]

I responded to a similar question in the following thread:

thread703-495410

Give it a try as with a few modifications you can have it working in your query. Give me a shout if you need more assistance with it.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Darrylles]

Nope,

Author: Comical Ali

D

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[rzff]

Hallo,

Is there no way that I can get this result with a query. There are a maximums of CODE_TYPE which is 1,2 or 3.

Please let me know if somebody has an idea for this query

thanks

rzff

 

[scriverb]

Yes, as I indicated in a query you create a not column with a name and then reference the Function call. It will return the horizontal string of values as you indicated.

But, I do want to apologize for not analyzing your posting clearly. You want the values to correspond to individual columns. I believe a crosstab query will server you better in this situation.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Here is the SQL for a crosstabl query that will do the job for you:

TRANSFORM First(tblCodes.Code_ID) AS [The Value]
SELECT tblCodes.Artnr
FROM tblCodes
GROUP BY tblCodes.Artnr
PIVOT tblCodes.Code_Type;


Just change the name of the table tblCodes to what ever is the exact name of the table in your database.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[rzff]

Hi Bob,

Perfect it works fine.

Thanks you very much for your help.

Regards

RZFF

 

[rzff]

Hallo,

I have one more question. Can I include the columns names 1 should be ISIN 2= Telekurs 3= Valore. Is there a way the include this in query upfront.

Thanks

RZFF

 

[scriverb]

Save the query above and name it qryCrossTab or whatever you want to. Then use the following SQL to take that query in as input and change the names:

SELECT A.Artnr, A.[1] AS ISIN, A.[2] AS Telekurs, A.[3] AS Valore
FROM qryCrossTab as A;

That should name the columns as you requested. Just run this second query. Name and Save it.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[rzff]

Hi Bob Scriver,

You did it agian. It works and it is working fast.

Regards

RZFF

 

[scriverb]

Great. Glad to be of assistance. By the way, you should keep that initial code I gave you in case you ever need to string together text values from multiple records. It puts them all together with a deliminiter(,) as one value for display purposes.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???