Access Data

[wongray]

Hello there, Need some advice.

I have a database that I have 2 field. one is the Project Id and the other is the region.

I would like to combine all the region data that have the same project ID. could you please advice how to do it?

Here is the sample data

Project ID | Region
========= | ===========
1 | HK
1 | SG
2 | SG
3 | HK
3 | SG
3 | TK

End results

Project ID Combine Region
======== ==============
1 HK, SG
2 SG
3 HK, SG, TK

Please advice how to do this query. and many thanks for your help in advance.

[TerryDM]

You cannot do this with 'a' single query, and in the general case you cannot do it at all with SQL. The best case would be that you make a query for each region and save the results in a field for each region by project. Then after you ran all the regions, make another query that would append all of the non-null regions into another field in the new project table. Or, you could write code that would open the table via a sorted query, step thru the table by ProjectID appending each new Region to a text field, and then write it to another table by project.

[missinglinq]

Use the Search function here with the term Crosstab Query. You'll get a number of hits that I think will help.


Linq ;0)>

[wongray]

Use the Search function here with the term Crosstab Query. You'll get a number of hits that I think will help.


Linq ;0)>

THanks, can you show me the steps?

[NeoPa]

Have a look in Producing a List from Multiple Records. I think this should show you what you need. It's not a Cross-Tab solution, but I think it suits your requirement better anyway.