Loop Through a Field to Lookup Help Needed (1 Viewer)

[graviz]

I’m not sure where to start so I’ll just describe the scenario.

The Master table contains all the information I need however there’s a field “SCRs” that need to be converted by using a key in another table. I basically need to join the “SCRs” field to the “TS_ID” field but the “SCRs” field contain multiple IDs separated by commas.

Table Name: Master
Field: “SCRs”

Table Name: SCR_Lookup
Field: “TS_ID”, “Name”


Record in the Master table
“SCRs” = ,59090,59092,59094, (It. could be any amount of numbers. It’s not always going to be 3 which is the tricky part.)

3 Records in the SCR_Lookup table
“TS_ID” = 59090 with “Name” = API_0504
“TS_ID” = 59092 with “Name” = DP_0588
“TS_ID” = 59094 with “Name” = CSG_7777


Desired Result: API_0504, DP_0588, CSG_7777

I hope this makes sense but am unsure how to do this easily and systematically.

Any ideas?

 

[MarkK]

but the “SCRs” field contain multiple IDs separated by commas.

This is your trouble. You need to create another table, and each of the individual IDs in the SCRs field should get its own row.

 

[pbaldy]

It would be a simple join in a query if the data was normalized and those values were records in a related table instead of all in one field. As it is, the only way that comes to mind is a function that accepted the field as an input and returned the desired result. It would use the Split() function to break apart the string of numbers and a recordset or DLookup() to get the associated name value, concatenating those together as it went.

 

[graviz]

It would be a simple join in a query if the data was normalized and those values were records in a related table instead of all in one field. As it is, the only way that comes to mind is a function that accepted the field as an input and returned the desired result. It would use the Split() function to break apart the string of numbers and a recordset or DLookup() to get the associated name value, concatenating those together as it went.

Public Function Split_SCR()
Dim Org_RS As Recordset
Dim New_RS As Recordset
Dim SCR_Array As String
Set Org_RS = CurrentDb.OpenRecordset("RFC_STAGING_1")
Set New_RS = CurrentDb.OpenRecordset("SCR_Split")
Org_RS.MoveFirst
While Not Org_RS.EOF
SCR_Array = Split(Org_RS.Fields("SCRs"), ",")
For i = 0 To UBound(SCR_Array)
With New_RS
    .AddNew
    !RFC = Org_RS!RFC
    !SCR = SCR_Array(i)
    .Update
End With
Next
Org_RS.MoveNext
Wend
End Function

I'm getting an Expected Array error on "For i = 0 To UBound(SCR_Array)"

Any ideas what I need to adjust my code to? I think I'm close?

 

[pbaldy]

I'm not a big array user but when I use one I declare it like:

Dim SCR_Array() As String

 

[graviz]

I'm not a big array user but when I use one I declare it like:

Dim SCR_Array() As String

I knew I was missing something simple. Thanks! One step closer!

 

[pbaldy]

No problemo!