generate string from normalised data set (1 Viewer)

[wiklendt]

hi everyone,

i'm looking for hopefully an easy solution to something i don't even know where to start (query/report/VBA?)

i have a set of normalised data - basically samples vs positive results ("targets"). looks something like this:

what i need now is to generate a string of the results to sit next to the sample, delimited by a space-pipe-space (" | ", without the quote), to look like this:

the result i ended up getting by laborious excel/manual methods (using a combination of "subsitute" and "trim" formulae, from a non-normalised set, followed by copy/paste-as-values, sorting, etc...), but i'd like to automate this somehow because i've now got a normalised setup, and want to be able to arbitrarily move around the order of the results in the string, and ideally allow others to do this easily too.

i've started myself off by adding an order field to my targets table (see "order" field in the normalised screenshot), and can sort by this successfully, but i'm not sure where to go from there? not sure even what kind of keywords to use in searches, all i'm finding is people wanting to normalise free-text, rather than 'denormalising' or joining rows (grouping by?), for want of a better term.

i need the data as a string as a visual indicator of patterning in our results... and other useful features of having them displayed that way.

can anyone point me in the right direction??

ta.

 

[John Big Booty]

So the four elements you are trying to combine are related to each record? and you wish to combine them for display/report purposes?

 

[HiTechCoach]

This might help:

Return a concatenated list of sub-record values

 

[wiklendt]

This might help:

Return a concatenated list of sub-record values

thanks HiTechCoach! looks like what i'm after. i'll play with it sometime this weekend.

John Big Booty, yes, sort of. i wish to combine them as you say ("concatenate" was the keyword i couldn't remember yesterday!), not just for display but for pattern analysis and graphing, so HTCs reference to Allen Browne's query-based VBA solution will be fabulous because i can then use that resulting string in other queries or exporting to excel (to make pretty graphs, which is something that still eludes me within access) or even put in a report if i want to.

thanks guys. i'll tinker with the code and report my successes.

 

[wiklendt]

ok, i have SORT OF managed to get the code, reference by HTC, to work. the only problem i'm getting is that it returns ALL "sub-record" data for every parent record, rather than just those it's supposed to.

i.e., returns something like that (using allen browne's code - i've managed to order the targets as i desire, and able to reorder them sporadically (that is, i have found that the 'parent' and 'child' tables can be queries)):

rather than something like this (copy/pasted from an earlier, manual concatenation):

see attached db. any help appreciated. i've removed some records as we are working to publish this data... you understand.

edit: sorry, forgot. database now uploaded, too. "qryProfiles" is the query that opens 'on open' of database, and this is they very query with the "fConcatChild" function. i've limited the DB to the very very very only absolutely required objects.

would be great if i could get this to work - will make much of our analyses for other projects so much easier too.

 

[DCrake]

Did notice that on the concat function call your last argument is being passed as a literal string as opposed to the value of the field contents. If you remove the quotes Access puts [] 's around it so it will now pass the value to the function. Because the rs =false and it is trapped to resume next it will always return the same values for all target ids

 

[vbaInet]

The only reason why the Allen Browne's code isn't working for is because your concatenation requires two unique fields to be looked up, CCCID and OurRef. His example usese one unique ID.

I haven't had a look at your db but something like this should work for you:

Public Function ConcatTarget(theID As Long, theRef As String) As String
    
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim prevID As Long, prevRef As String
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT Target FROM [COLOR=Red][B]myQuery[/B][/COLOR] WHERE " & _
                               "CCCID = " & theID & " AND OurRef = '" & theRef & "'")
    
    ConcatTarget = ""
    
    With rst
        Do While Not .EOF
            ConcatTarget = ConcatTarget & IIf(Len(!Target & "") <> 0, "| " & !Target, "")
        Loop
    End With
    
    rst.Close
    set rst = Nothing
    set db = Nothing

    If Left(ConcatTarget, 2) = "| " Then
        ConcatTarget = Right(ConcatTarget, Len(ConcatTarget) - 2)
    End If

End Function

Notice the two parameters that need to be sent in the function are CCCID and OurRef.

 

[wiklendt]

The only reason why the Allen Browne's code isn't working for is because your concatenation requires two unique fields to be looked up, CCCID and OurRef.

no it doesn't. CCCID is the uniqueID (PK, if you will) for the samples. "OurRef" is just the text field which stores the sample name. it is useful to display this name when humans are looking at it, since an autonumber field is meaningless to them, but i haven't used it at all in the fConcatChild criteria.

thank you for the code, however. i will look at it, who know when it might be useful in the future!

DCrake, your solution fixed it! i changed my criteria from:

Profile: fConcatChild("qryCCC_Targets","CCCID","Target","Long",[COLOR="red"]"[/COLOR]CCCID[COLOR="Red"]"[/COLOR])

to:

Profile: fConcatChild("qryCCC_Targets","CCCID","Target","Long",[COLOR="red"][[/COLOR]CCCID[COLOR="red"]][/COLOR])

, as per your suggestion.

now that i look back at the instructions, it is clear this is what it is supposed to be, however, i had initially misinterpreted the last parameter to be "optional", as i erringly linked the square brackets in this function to square brackets in other functions, which denote an optional parameter.

thanks for pointing your finger directly at the issue!

it's working well now

big hugs to you

You must spread some Reputation around before giving it to HiTechCoach again.

You must spread some Reputation around before giving it to DCrake again.

 

[vbaInet]

Oh I see now. I thought you wanted to use two unique fields and not just one

Glad you got it working.

 

[wiklendt]

Oh I see now. I thought you wanted to use two unique fields and not just one

Glad you got it working.

no worries. i'm sure one day i'll have a setup that requires two unique IDs kudos for helping out

 

[vbaInet]

no worries. i'm sure one day i'll have a setup that requires two unique IDs kudos for helping out

Maybe one day Thanks, you're welcome.

 

[wiklendt]

hey everyone, i've improved on the code/process a little bit:

1. made the delimiter a declared string, and used the length of that string (i.e., "Len(strDelimiter)") to trim the end of the concatenation (so that i can change the delimiter and not worry about changing the number of characters to remove at the end)
   
   • the original code simply removed 1 character, because it's delimiter was ";"
   • mine removes the length of the delimiter, since i'm using " | " (3 chars) and someone else might want a different delimiter again.
2. created a pair of action queries: to delete and append (i.e., to "refresh") the resulting query strings in a separate table with appropriate primary key. this is because we will be doing things with the strings, and the query takes a long time to run each time (e.g., sort by string). anywhere we do things with the string (as opposed to generating the string) we use the table, rather than the parameter query.

hope that helps anyone using this function in the future

thank you to all who helped (all of you, really!) on this one.

cheers.

 

[HiTechCoach]

Agnieszka,

Glad to see that code ended up working for you.