Solved

A CSV String consisting of IDs

Posted on 2008-10-08
12
240 Views
Last Modified: 2012-05-05
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
Comment
Question by:Xcudo
  • 5
  • 5
  • 2
12 Comments
 
LVL 20

Expert Comment

by:clarkscott
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)
     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
 
LVL 42

Expert Comment

by:dqmq
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?
0
 
LVL 20

Assisted Solution

by:clarkscott
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
0
 
LVL 42

Expert Comment

by:dqmq
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.  

0
 

Author Comment

by:Xcudo
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
X
0
 
LVL 42

Expert Comment

by:dqmq
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Xcudo
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
X
databaseEE.mdb
0
 
LVL 42

Expert Comment

by:dqmq
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
 




0
 

Author Comment

by:Xcudo
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

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
 

Author Comment

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

CombineDataOptions:
parentMemberID / integer
parentOptionsID / integer
0
 
LVL 42

Accepted Solution

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










0
 

Author Comment

by:Xcudo
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
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now