A CSV String consisting of IDs

Posted on 2008-10-08
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
  • 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 20 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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 480 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

765 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