Pulling multiple repeating values into a concatenated string.

[jbrew]

Hello,

I am a student working on a project for class. We have an Access database that we are building and I have run into a problem with some of the reports we need to generate. Some of the report values have to be produced into sentence format on the report. Below I will try to explain the problem.

I have a report that is producing multiple clients, multiple medications per client and multiple dosages and times of delivery per medication. Just producing these items in a blocked format works fine. Displaying with a hierarchy of:

client          medication        dose     time
John Doe
                 med 1               1 tab    12:00AM
                                        2 tab    1:00PM
                 med 2               1 tab    12:00AM
                                        2 tab    3:00PM

Susie Q      
                 med1                1 tab    12:00PM
                 med2                2 tab    3:00PM
                                        3 tab    9:00PM

What needs to be produced is this information in the format below:

Client           medication   
John Doe
                   med 1, 1 tab @ 12:00AM AND 2 tab @ 1:00PM                  'Name reproducing values from one textbox
                   med 2, 1 tab @ 12:00AM AND 2 tab @ 3:00PM                  'Time reproducing values from one textbox
Susie Q                                                                                         'Dose reproducing values from one textbox
                   med 1, 1 tab @ 12:00PM
                   med 2, 2 tab @ 3:00PM @ 3 tab @ 9:00PM

I have reviewed the concatenation example online about concatrelated() and tried to use that solution in a module. It would loop through the fields dose and time and produce the values in a concatenated string. I think that solution was only meant to be displayed once as it gave me the answer I was looking for but did not repeat on the report in the appropriate text boxes per client, per medication. I later realized that I would need to also loop through the client names since they were repeating. So my understanding is that I need to loop through the client names, loop through the dose and time values and set the medication text box to: =[medication name] ", " & FunctionforDose() "@ " & FunctionforTime(). I would need to pull the value from the name text box so that the function knows where it needs to look to pull the correct information, but it also has to use a function to reproduce the repeating value of the names on the report. I have color coded examples of my understanding of the problem points here. Am I over complicating this process? Is there an easier way of doing this operation? Any help would be greatly appreciated. I will provide any clarification needed.

Thanks in advance,
Jared

[June7]

VBA is really the only option for this data manipulation.

Who's code example did you try?

Post your code or provide db (instructions at bottom of my post) for analysis.

[jbrew]

The code I example I tried to use was from Allen Browne. Here is the link:
http://allenbrowne.com/func-concat.html
I am attaching the working version of the database which does not have the function in a module on it. I have a copy of the database with the function added but it does not function well. I can repost that version if you prefer but I will have to remove some data. The database provided is the version we would like to add a working function too. There are 4 reports in question for this issue but for example sake the "physicians documentation" is the only report we need to look at. Once again I greatly appreciate your time and will provide any other information you require.

ForumV1.zip

[June7]

Here's what I accomplished.

1. Paste Allen's code into a general module. No changes to code necessary.

2. Create this query
SELECT Orders.ClientID, Orders.MedID, [Med Schedule].Time, [Dosage] & " @ " & Format([Time],"Short Time") AS DoseTime, ConcatRelated("[DoseTime]","[Query1]","[ClientID]=" & [ClientID] & " AND [MedID]=" & [MedID],"[ClientID],[MedID],[Time]"," AND ") AS MultDoseTime
FROM Orders INNER JOIN [Med Schedule] ON Orders.[ID] = [Med Schedule].[OrdersID];

[jbrew]

Sorry for the late reply. We worked with this solution and with a few variations came up with the string we needed! Thank you very much for your help.