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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Undo Button 1 31
Saving data on a subform when I close the form 12 27
Display field if column exists 7 30
Access #Deleted data 20 35
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

685 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