By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,652 Members | 1,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,652 IT Pros & Developers. It's quick & easy.

Access Data

P: 17
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.
Jan 27 '08 #1
Share this Question
Share on Google+
4 Replies


P: 14
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.
Jan 27 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Use the Search function here with the term Crosstab Query. You'll get a number of hits that I think will help.


Linq ;0)>
Jan 27 '08 #3

P: 17
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?
Jan 28 '08 #4

NeoPa
Expert Mod 15k+
P: 31,487
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.
Feb 2 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.