Comma Seperated Values in Table reference another table depending on value

[Xonos]

Alright, so allow me to explain this with the least amount of information necessary to explain my need in order not to circumvent the topic. And again, hello everyone - long time reader, recent registrant.



Let's assume the following data/tables are how I have my database setup.


Tables

[tblClients]
ID - Autonum
Name - Text
Categories - Text

[tblCategories]
ID - Autonum
Category - Text
Description - Memo

##########
Relationships

[tblClients].[ID] -> One-To-Many <-[tblCategories].[ID]

###############
Example Table Data

tblClients

ID	Name	Categories
1	John	T1, A22, IB22, 11
2	Sam	L23, K2, TPC9, B9
3	Adam	T1, K2, TPC8, L23

tblCategories

ID	Category	Description
1	T1	Test
2	A22	Test
3	IB22	Test
4	11	Not cool enough to be 12.
5	L23	This is L23, yay!
6	K2	Test
7	TPC9	Test
8	B9	Test
9	TPC8	Test

<Description will have rich text formatting.>

















Alright, so here's my problem - in the tblClients categories column, each row contains a series of comma-separated values (stored in the "Categories Column") that correspond to the tblCategories.Category column which houses a description for each value. What I need is a method to hyperlink these individual values in the tblClients.Categories field to a form that will filter depending on what value you click to display the description.

For example:

tblClients

ID	Name	Categories
1	John	T1, A22, IB22,
2	Sam	L23, K2, TPC9, B9, A22
3	Adam	11, K2, TPC8, L23

Upon clicking on one of the values, such as "L23" or "11" - a form would popup and display the following:

Category: 11
Description: Not cool enough to be 12.

Category: L23
Description: This is L23, yay!

Making the form popup isn't my issue but grasping the method at which I will link the data is where I am lost. My database is normalized with a surmount of unending relations (which is why I didn't want to get into that part.) but the reason why I have comma-separated values in a table is because the information stored in my table is actually US Department of Transportation hazardous material codes that MUST remain in the formatting that they're in. Each record may have up to 40 different codes specifying US regulation and provisions to be referenced and they're all separated by commas so moving this data elsewhere doesn't seem like a time-effective or practical matter.

Do any of you have any advice on the terminology I should use when searching for something of this particular nature? I've tried looking up table to table hyperlinking as well as various other clues to no avail. If you have any resources that I can read that you believe would benefit me in my quest for knowledge, please let me know! And ofcourse, thank you all for your effort to provide support - you are the reason why I have a job.

[June7]

Is the Categories field in tblClients simple text or a multi-value field?

Review this article about using multi-value fields http://office.microsoft.com/en-us/ac...001233722.aspx

[Xonos]

It is not a multi-value field. After reading that article entirely, I went into the DB and severed its relations - selected "Text" and navigated to Lookup Field. I selected that I wanted to retrieve values from another table. I then realized I needed to create a table to store the multi-value data and relate it with keys because I was unable to lookup the data already in place. After doing that, when I went back it errored and said that the db was in use... so I suspect that I cannot lookup based on a related table linked by id. So I turned around and re-created the table and pasted the data back and tried a slightly different approach. Same error... It keeps telling me that the table I am using is being used by another person or process but I have closed access entirely several times. I don't get it... I've removed all relations to the table.

[June7]

Not following that. You want to provide the project for analysis?

[Xonos]

I'm behind DSL and my database is massive.


Allow me to explain... tblClient.Categories is a text field storing comma-delimited values as you can see. I have not yet entered design view to change the Lookup to Combo or List.

Each of these comma-delimited values (in tblClient.Categories) are also stored in tblCategories which gives a description of each. tblClients and tblCategories are related by their ID fields as one-to-one. What I want is to pull the [tblCategories].[Description] based on the comma-delimited values stored in each tblClient.Categories field. And I want each of these values to be a hyperlink (or I can listbox them and set on-click, w/e works.) that displays the description in a popup.

Edit: If I were to enter design view for tblClients, go to the lookup tab under its properties and set the following options:
Display Control: List Box
Row Source Type: Value List
Allow Multiple Values: Yes
Allow Value List Edits: No

Unfortunately, this gives undesired results and will not separate the values.

[June7]

You could extract just the relevant tables with a reduced dataset into another Access.

Okay, use VBA to parse the string and its relevant description to a listbox. Which part of this suggestion do you need info about?

1. parsing string: Split function
2. finding the description: DLookup function
3. populating listbox: AddItem method

You say tblClients and tblCategories are one-to-one but from what I see they really don't have a relationship, not that allows a query join.

[Xonos]

Alright, I am going to leave work now - been here for 12 hours so far. Anyways, I read up on the split function and from what it seems - I have to run it through a loop that will use the specified delimiter as a break between arrays. I understand the theory however I do not know exactly how to complete this. Once I figure it out, I will post source for anyone that may need this information... I can't imagine parsing comma-delimited strings is entirely uncommon.

I haven't used DLookup before (by use of my own code) but I am sure I can figure it out.. Will the Split() function also be responsible for additem as well? I guess I should store the count of the array in a variable somewhere too... Hmmm, I need to read on how to set parameters in functions. I'm new to all of this.

I'll post results tomorrow and thanks agian June for your guidance.

[June7]

Use Split to parse items to array, loop the array to get each element. In the same loop can look up the description and include it to also show in the listbox if want to.

[Xonos]

Alright, so this is what I've got. Currently, upon record selection I am pasting the CSV data to an input box that will be hidden but just for the sake of learning how to use split in a loop, I am setting the values to a string and then parsing through the loop however I am getting an odd error. "Data type miss-match"..

Private Sub inputSProvisions_Change()
Dim varCodeArray As Variant
Dim strDelimitedCodes As String
Dim i As Integer

Set strDelimitedCodes = Me.inputSProvisions.Text


With strDelimitedCodes
.MoveFirst
Do While Not .EOF
varCodeArray = Split(strDelimitedCodes, ",")
For i = 0 To UBound(varCodeArray)
Call AddItemToEnd(comboSPCodes, varCodeArray(i))
Next i
.MoveNext
Loop
End With
End Sub

Adding item function:

Function AddItemToEnd(ctrlListBox As ListBox, _
        ByVal strItem As String)

    ctrlListBox.AddItem Item:=strItem

End Function

Fixed one issue but now when I run this, it says "Object Required". I'm still working on it, I'll post results and source to help others when I fix it.

[Xonos]

Finally was able to pass a string of data (IE: Joe, Mary, John, Sam) into an array like so:

varCustomer(1) = Joe
varCustomer(2) = Mary
varCustomer(3) = John
varCustomer(4) = Sam

and then use this data to populate a combo box or list using the comma-delimited string. This was my approach, all thanks to June leading me in the right direction - I knew I could figure this out without begging for someone to tell me the answer.

Function SplitCommaDelimitedString(delimitedstring As String) As String
   Dim varArray
   Dim i As Integer
   Dim tmp As String
   
'Here is where you specify your delimiter, where it be a space or ";"...
   varArray = Split(delimitedstring, ",")
   For i = 0 To UBound(varArray)
    'This adds each item in the array as seperate items in a list object.
    comboNameHere.AddItem Item:=varArray(i)
   Next
   If tmp <> "" Then tmp = Mid(tmp, 2)
   SplitCommaDelimitedString = tmp
End Function