Crosstab Query??

[smith_gw]

I have a table that includes and ID and language field where multiple records
include the different languages. Some ID's have up to three languages so it
looks like:
ID1,language1
ID1,language2
ID1,language3
ID2,language1
ID2,language2

I am trying to create a query that instead would list them as:
ID1, language1, language2, language3.
ID2, language1, language2, null/blank
ID3, language1, null/blank, null/blank.

Any assistance would be greatly appreciated.

 

[Michel Walsh]

You have a third field telling which language the record is about? If so, if
it is called languageID, then:
( I assume the second field is called comment )


TRANSFORM LAST(comment)
SELECT id
FROM yourTableName
GROUP BY id
PIVOT languageID



Hoping it may help,
Vanderghast, Access MVP

 

[smith_gw]

Thanks for the prompt response. I have the following written:
TRANSFORM PCS_Languages.LANGUAGE_CODE
SELECT NO_PCS_ID.id_of_providers
FROM NO_PCS_ID INNER JOIN PCS_Languages ON
NO_PCS_ID.language_k_of_languagereferences_provaddr =
PCS_Languages.LANGUAGE_NAME
GROUP BY NO_PCS_ID.id_of_providers
ORDER BY NO_PCS_ID.id_of_providers
PIVOT language_code;

This gives kind of what I'm looking for, however, since there are seven
language codes I get the id_of_providers column plus one column for each
language code. Each unique id_of_providers value will have no more than
three language codes tied to it. Is there a way to narrow the results to the
id_of_providers column and then three "language" columns?

 

[Michel Walsh]

If you don't care about having the languages code in any order:

TRANSFORM PCS_Languages.LANGUAGE_CODE
SELECT NO_PCS_ID.id_of_providers
FROM NO_PCS_ID INNER JOIN PCS_Languages
ON NO_PCS_ID.language_k_of_languagereferences_provaddr
= PCS_Languages.LANGUAGE_NAME
GROUP BY NO_PCS_ID.id_of_providers
ORDER BY NO_PCS_ID.id_of_providers
PIVOT DCOUNT("*", "NO_PCS_ID" , "id_of_providers=" & id_of_providers
& " AND language_k_of_languagereferences_provaddr >=" &
language_code)




where I assumed id_of_providers and
language_k_of_languagereferences_provaddr are both numerical data type (if
not, change the DCOUNT syntax appropriately).



Vanderghast, Access MVP