Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

A CSV String consisting of IDs

Hello,

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
X
0
Xcudo
Asked:
Xcudo
  • 5
  • 5
  • 2
2 Solutions
 
clarkscottCommented:
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)
     else
         '---- 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
0
 
dqmqCommented:
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?
0
 
clarkscottCommented:
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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
dqmqCommented:
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.  

0
 
XcudoAuthor Commented:
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
X
0
 
dqmqCommented:
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?
0
 
XcudoAuthor Commented:
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
X
databaseEE.mdb
0
 
dqmqCommented:
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
 




0
 
XcudoAuthor Commented:
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

CombineDataOptions:
parentMemberID
parentOptionsID

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
X
0
 
XcudoAuthor Commented:
ahh forgot to write which datatype the CombineDataOptions table is.

CombineDataOptions:
parentMemberID / integer
parentOptionsID / integer
0
 
dqmqCommented:
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.










0
 
XcudoAuthor Commented:
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
X
curID=""
firstKomp=true
Function IIf(valg,opt1, opt2)
  If valg then
	IIf=opt1
  Else
	IIf=opt2
  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")
	firstKomp=true
  End if
  Response.Write IIf(firstKomp,"<br>",", ") & rs.fields("kompetenceText")
  rs.movenext
  firstKomp=false
wend

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now