Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


A CSV String consisting of IDs

Posted on 2008-10-08
Medium Priority
Last Modified: 2012-05-05

In a access database I have two tables, lets call them table1 and table2.
table1 consists of an ID number and a text value.
table2 store several ID numbers from table1 in a comma separated format.
Let me show you have this looks like :

table 1 :
id / text
1 / text 1
2 / text 2
3 / text 3

table 2 :
id / id_of_table1
1 / 1,3
2 / 2,3,1

What i'm looking for is a select clause, for msaccess which end result looks like this.

id / text
1 / text1,text3
2 / text2,text3,text1

Is that possible ?

Best regards
Question by:Xcudo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
LVL 20

Expert Comment

ID: 22672115
You will have to build a function that performs this task, then call this function from your query.

Function MakeText(byref vText as string) as string
dim x as integer
dim j as integer
dim s as string
dim wholestring as string
for j = 1 to len(vText)
     if mid(vText,j,1) <> "," then
         '--- this concatenates the numeric value (if it should be more than 1 character)
          s = s & mid(vText,j,1)
         '---- if this is NOT the first 's',  then add a comma -------
          if wholestring <> "" then wholestring = wholestring & ","
          '--------- put the 'text' in front and add the s string
          wholestring =  "Text" & s
          s = ""
     end if
next j
maketext = wholestring
end function

select *, maketext(YourField) as YourNewQueryColumnName from Yourtable

Scott C
LVL 42

Expert Comment

ID: 22672468
Hmmm...inside the function you need to do a DLOOKUP during each iteration to retrieve the value from table 1.  Otherwise, you are just reconstructing the same string you started with.

Table 2 is a bad design. Any chance you can change it at this point?
LVL 20

Assisted Solution

clarkscott earned 60 total points
ID: 22672526
No... the data is 'fielded'  as 1,2,3.  the user wants  Text1, Text2, Text3
All you need to do is pass the '1,2,3' to the function and the function returns the 'Text1, Text2, Text3'
No lookup needed. You passing the field value to the function (from within the query).

Scott C
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 42

Expert Comment

ID: 22672994
One of us misunderstands the question.  I think the user wants to look up the the text in table 1.  I don't think the user wants to append the string 'text' to each of the csv terms.  If so, this would be much simpler:

   Select "text" & replace(YourCSVColumn,",",",text") from YOURTABLE

No function user function required.  


Author Comment

ID: 22673250
Thanks alot for your quick responses.
Let me try to re explain what result i'm looking for once more.

Lets say I have two table's table1 and table2. below is just an small example, of how my database is built, since it involves alot more columns, i've just picked the part where i'm having trouble completing it with my asp classic code. :)

table1, consist of 2 columns.
Column 1: has it's datatype set to unique indentifier and is here called table1ID
Column 2 : has it's datatype set to note and is called table1text
The content of these two columns are.

table1ID / table1text
1 / text1
2 / text2
3 / text3

table 2 also consist of two columns.
Column 1: has it's datatype set to unique indentifier and is here called table2ID
Column 2 : has it's datatype set to note and is called parentTable1ID

the content of table2's columns are.
table2ID / parentTable1ID
1 / 1,3
2 / 2,3,1

So when I then output the end result to the user, I would then like it to show as follows.

table2id / parentTable1Text
1 / text1,text3
1 / text2,text3,text1

Hope that clarifies it all :)

Otherwise i'd be happy to show my current db build or attach the mdb file.

Best regards
LVL 42

Expert Comment

ID: 22673322
Please give the results you desire if table1 looks like this and everything else is the same:

table1ID / table1text
1 / abc
2 / def
3 / ghi

And once again, I advise you to cange the design of table 2.  Are you at liberty to do so at this time?

Author Comment

ID: 22673548
Yes i am at liberty to change the design, what do you have in mind ?
I've  attached my current mdb file.

Everything is not the same, the database content is dynamic.

Best regards
LVL 42

Expert Comment

ID: 22673703
I cannot look at your .mdb because Access is back level at the machine. However, with the disclaimer that I have not looked at your design, I think Table 2 would be better designed like this:

Table 2 also consist of two columns
 Column 1: has it's datatype set to int
 Column 2: has it's datatype set to long and is called Table1ID

 Both columns particpate in the primary key
 Column 2 has a foreign key to table1ID

Using your example, Table2 would look like this:
groupNo / table1id
1 / 1
1 / 3
2 / 2
2 / 3
2 / 1


Author Comment

ID: 22676545
I don't quite understand what you mean by "because Access is back level at the machine"
But the rest i do, anyhow that's is what i've also tried, but for some reason my access sql string aren't grouping the result. instead of using the example test, i've written below in text form how my database looks like.

Members table :
M_ID / Unique ID
M_name / Text
M_address / Text
M_Floor / Text
M_FloorSide / text
M_Postno / Integer
M_City / Text
M_Phoneno / text
M_HidePhoneno / true/false
M_CellPhoneno / text
M_HideCellPhoneno / true/false
M_Sex / text

Options :
OptionsID / Unique ID
OptionsText / Text


I've then made this select clause :
SELECT Options.OptionsText, Members.M_Name, Members.M_Id, Members.M_Sex
FROM Members INNER JOIN (Options INNER JOIN CombineDataOptions ON Options.OptionsID = CombineDataOptions.parentOptionsID) ON Members.M_Id = CombineDataOptions.parentDataMembersID;

Which give's me this result.
OptionsText      M_Name      M_Id      M_Sex
aaa      Name      {72A4B541-8F9A-466A-ACF5-7EDB346C9C40}      male
cccc      Name      {72A4B541-8F9A-466A-ACF5-7EDB346C9C40}      male
fff      Name      {72A4B541-8F9A-466A-ACF5-7EDB346C9C40}      male
iii      Name      {72A4B541-8F9A-466A-ACF5-7EDB346C9C40}      male
hhh      Name      {377CBA22-9E71-438C-805A-E79BD321DCE9}      female
bbb      Name      {377CBA22-9E71-438C-805A-E79BD321DCE9}      female
fff      Name      {377CBA22-9E71-438C-805A-E79BD321DCE9}      female

But what I'm looking for is a result where optionsText is grouped ergo a result like this.
OptionsText      M_Name      M_Id      M_Sex
aaa,cccc,fff,iii      Name      {72A4B541-8F9A-466A-ACF5-7EDB346C9C40}      male
hhh,bbb,fff      Name      {377CBA22-9E71-438C-805A-E79BD321DCE9}      female

Hope that explains it :)

Best regards

Author Comment

ID: 22676555
ahh forgot to write which datatype the CombineDataOptions table is.

parentMemberID / integer
parentOptionsID / integer
LVL 42

Accepted Solution

dqmq earned 1440 total points
ID: 22682660
Hang in there, we are making progress.  

First, convert that SQL to a group by query:

SELECT GetOptions(Members.M_ID) as Options, Members.M_Name, Members.M_Id, Members.M_Sex
FROM Members
Group by Members.M_name, Members.M_id, Members.M_Sex

The next step is go to VBA and write a function called GetOptions (MemberId) that opens a recordset:

"Select Options.OptionsText From Options INNER JOIN CombineDataOptions ON Options.OptionsID = CombineDataOptions.parentOptionsID
where CombineDataOptions.ParentMemberId=" & MemberID

Iterate through the recordset and construct the list of Options in a string variable which the function will return.

Sorry, I do not have time to code it up right now, but give it a shot on  your own and ask followup questions if you run into trouble.


Author Comment

ID: 22740594
Thanks for all the help..
But I figured it all out, with a little function and when i run through my recordset i check to see if the M_ID field changes if it does i will only show one user.. well my final code is. sorry if some of the names doesn't add up..since some of it are in danish :)

Best regards
Function IIf(valg,opt1, opt2)
  If valg then
  End if
End function
While not rs.eof
  If curID<>rs.fields("m_id") then
	Response.Write "<p><b>" & rs.fields("m_navn") & "</b>"
	curID= rs.fields("m_id")
  End if
  Response.Write IIf(firstKomp,"<br>",", ") & rs.fields("kompetenceText")

Open in new window


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question