ConcatRelated

[Workaholic_711]

Hello experts,

I am learning VBA coding and came across the following issue:

I have an access database which has a table named CPT_CODES_String. It has the following fields:
CLAIM_ID, SERVICE_DATE, CPT_CODES, KEY(CLAIM_ID+SERVICE_DATE)

One Claim can have mutiple cpt codes. I want to concatenate CPT Codes corresponding to a Claim_ID in a single row.

For example:
Claim ID CPT Code
1 A
1 B
1 C
2 Z
2 E

Desired Output:
Claim ID CPT Code
1 A,B,C
2 Z,E

I tried Allen Browne's ConcatRelated but failed to write the vba code and also the ConcatRelated function using fields from one table only.

Could you please help me write the function as well as the vba code using the above table and field names?

Thank you.

 

[theDBguy]

Hi. Not sure why you would need to create a function. You're supposed to just use the one AllenBrowne provided. Here's another example of the same thing: SimpleCSV()

 

[pbaldy]

What did you try that failed? The query would look like:

SELECT [Claim ID], ConcatRelated(...)
FROM Tablename
GROUP BY [Claim ID]

With the appropriate arguments filled in for Allen's function.

 

[Workaholic_711]

I tried the folowing select statement:
SELECT DISTINCT [CLAIM_ID], ConcatRelated([PROCEDURE_CODES],[003_Claim_CPT_Codes],"[CLAIM_ID] = """ & [CLAIM_ID])
FROM 003_Claim_CPT_Codes group by [CLAIM_ID]

 

[pbaldy]

And what happend? The quotes in Allen's examples are required, so more like:

ConcatRelated("[PROCEDURE_CODES]", "[003_Claim_CPT_Codes]", "[CLAIM_ID]= " & [CLAIM_ID])

 

[Workaholic_711]

I forgot to add the quotes. It's running but taking a long time...Will let you know the outcome as soon as it finishes running.

Thank you!

 

[Workaholic_711]

It's not working correctly. i got the following output:

CLAIM_ID Expr1001
ABC CPT1[CLAIM_ID] = "ABCCPT2[CLAIM_ID] = "ABCCPT3[CLAIM_ID] = "ABCCPT4[CLAIM_ID] = "ABCCPT5[CLAIM_ID] = "ABCCPT6
XYZ CPT1[CLAIM_ID] = "XYZCPT2[CLAIM_ID] = "XYZCPT3[CLAIM_ID] = "XYZCPT4[CLAIM_ID] = "XYZCPT5[CLAIM_ID] = "XYZCPT6

This is my select statement:
SELECT DISTINCT [CLAIM_ID], ConcatRelated("[PROCEDURE_CODES]","[CptTest]","[CLAIM_ID] = " & [CLAIM_ID])
FROM [CptTest] group by [CLAIM_ID]

 

[pbaldy]

Is Claim ID text or numeric (its data type)? If text:

ConcatRelated("[PROCEDURE_CODES]","[CptTest]","[CLAIM_ID] = '" & [CLAIM_ID] & "'")

Can you attach the db here?

 

[Workaholic_711]

IT WORKED!!!!!

THANK YOU SO MUCH!!! CLAIM_ID was a text field.

 

[pbaldy]

Happy to help! Allen's site addressed the field being text.

 

[Workaholic_711]

Is there a way i can set the field to hold more than 255 characters. The output is truncated where the string is more than 255 characters. I tried setting the final output field as Long text but that didnt seem to make any dent.

Thanks again for your help!

 

[pbaldy]

Sorry, I've never come close to that so haven't needed it.

 

[theDBguy]

Is there a way i can set the field to hold more than 255 characters. The output is truncated where the string is more than 255 characters. I tried setting the final output field as Long text but that didnt seem to make any dent.

Thanks again for your help!

Hi. Can you post your final query SQL? Thanks.

 

[Workaholic_711]

Here you go:

SELECT [Raw Data].Key, Trim(ConcatRelated("[Value]","[Raw Data]","[Key] = '" & [Key] & "'")) AS FinalString INTO [Final Report]
FROM [Raw Data]
GROUP BY [Raw Data].Key;

 

[Workaholic_711]

I ran into a new issue with this: My team wants a list box with multiple delimiter options. I created a list box and tried to plug the listbox.ItemsSelected in the strSeperator field, but it's not working.

 

[pbaldy]

This is a shot in the dark, but it could be that the make table won't create a long text field. Try creating a table with that specified, and make this an append query instead of a make table query.

How did you try to pass the listbox value to the function?

 

[Workaholic_711]

Thanks, I 'll change it to an append query.

so this is what i did:

Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strMyListbx As String, _
Optional strMyListbox = lstDelimit.ItemsSelected, _
Optional strSeperator = strMyListbox) As Variant


It gave me a compile error

 

[pbaldy]

No, you wouldn't change the function, you'd pass the listbox value to it. Something like

ConcatRelated("[Value]","[Raw Data]","[Key] = '" & [Key] & "'", , Me.ListboxName)

I suppose you could hard-code it into the function, but I'd just pass it.

 

[Workaholic_711]

i have it like this but it's erroing out:

INSERT INTO [Final Report] ( [Key], FinalString )
SELECT [Raw Data].Key, ConcatRelated("[Value]","[Raw Data]","[Key] = '" & [Key] & "'",[Forms]![frmStringDB].[lstDelimit]) AS FinalString
FROM [Raw Data]
GROUP BY [Raw Data].Key;

 

[pbaldy]

What's the error? Can you attach the db here?