Grouping and Concatentation

[hyp527]

I have a set of data below:

Example:

R Item Num C Item Num
123 569
134 569
145 569
145 456
136 456
234 456

My goal is to concatenate the data so that it looks like (below) in their respective column:

C Item Num R Item Num
569 123 | 134 | 145
456 145 | 136 | 234

What's the best way to write this query?

Thanks!

 

[ajetrumpet]

I think the best way to do something like this would be to export the table out to Excel, and manipulate it that way. I don't think an Access query can do this.

 

[gemma-the-husky]

you would have to select all the C items (unique values query), and then have a function which examined the different R values for each, and asembled them into a string. The trouble is this is non-relational, so yuo wont do this with normal queries.

The other thing you could do is create a table for each C item, with enough columns to cover all the R items, and then slot the R items into the table

again, its non relational and you would need code to do it.

depends how you need the presentatino to look.

-------
lots of people ask for stuff like this, but its not trivial for access to do, as information in this way is non relational. (ie the data is very difficult to search the other way - if you need to find the C item corresponding to a given R value.)

 

[hyp527]

if you need to find the C item corresponding to a given R value.)

My purpose for doing this is to find all the corresponding R items for the C unique value and put it in a string format. Can you show me steps to do this or codes to do this? What is a good function to use?

Thanks.

 

[pbaldy]

This type of thing?

http://www.mvps.org/access/modules/mdl0004.htm

or

http://support.microsoft.com/kb/210163/en-us

 

[Mike375]

The attached DB might do what you want. It was made by KenHiggs and can be found in the SampleDB forum.

Delete the records from the table tempMedications and then open Form1 and click to run the routine. No look at tempMedications and see what it has done with table Medications.

 

[Mike375]

it did not attache,,. try again

 

[hyp527]

http://support.microsoft.com/kb/210163/en-us

That looks like what I want. But all these coding is like Greek to me. Do you have a visual in the design view?

Thanks.

 

[hyp527]

it did not attache,,. try again

This is exactly what I want to do. I don't see codes in the form. Are there special codes embedded behind the button "Run Routine"?

Thanks.

 

[Mike375]

This is exactly what I want to do. I don't see codes in the form. Are there special codes embedded behind the button "Run Routine"?

Thanks.

Check the module

 

[hyp527]

Thanks Mike!

 

[Mike375]

Thanks Mike!

KenHigg is the man to thank. I was just the courier and a professional DB stealer

 

[Sinfathisar]

it did not attache,,. try again

Hi, I'm new to the forum and I found this thread which is exactly what I wanted.

I downloaded the sample DB you posted and modified the module to work with my tables and query. However, when I tested it out I noticed that the concatenation is dropping the last entry. I tried a few things to see if I made a mistake somewhere, but everything seemed OK. Then I looked at the sample database again and saw that it is doing the same thing. What I mean is (for the sample DB) the last client ID listed in the Medications table is being dropped and doesn't show up in the tempMedications table (which contains the concatenated records), nor does its associated med description.

It seems to me that the problem might be related to the placement of the loop, but I'm not sure excatly what or how to go about fixing it. If anyone has any advice I would sure appreciate it.

Here's the code in the sample DB module:

Public Sub s_runMe()
    Dim cn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Set cn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
 
 
    rs1.Open "query1", cn, adOpenDynamic, adLockOptimistic
    rs2.Open "tempMedications", cn, adOpenDynamic, adLockOptimistic
 
    rs1.MoveFirst
    rs2.AddNew
    rs2![client id] = rs1![client id]
    rs2![med description] = rs1![med description]
    rs1.MoveNext
 
 
        Do While Not rs1.EOF
        If rs1![client id] = rs2![client id] Then
            rs2![med description] = rs2![med description] & ", " & rs1![med description]
        Else
            rs2.AddNew
            rs2![client id] = rs1![client id]
            rs2![med description] = rs1![med description]
        End If
 
        rs1.MoveNext
        Loop
 
    rs1.Close
 
    MsgBox "Done!"
 
End Sub

 

[Brianwarnock]

Add

rs2.AddNew

between the loop label and the Close

Brian

 

[KenHigg]

Thanks Brian - Must have missed that

I'll fix it soon as I get a few minutes...

 

[Mike375]

Thanks Brian - Must have missed that

I'll fix it soon as I get a few minutes...

I want a refund

 

[KenHigg]

Add

rs2.AddNew

between the loop label and the Close

Brian

Actually rs2.save is what should go there. The .AddNew works in this case because it saves the current record and then the new record it creates is dropped because there is nothing saving it.

What's happening is every new record that is created is saved by the next .addnew command. Except of course the last record which was added because there is no .addnew command following it. Some may say there needs to be a .save after every record but this slows the routine down and is not required. (IMHO)

 

[Brianwarnock]

Hi Ken I bow to your superior knowledge, but don't get carried away as I have not done that kind of thing before it is not difficult to know more than me so it was a quick fix, which is why I didn't change the original.

I do worry about peoples' testing and use of code they don't understand.

Good on Sinfathisar for spotting the problem but the quick fix was simple and obvious if technically not quite correct.

Brian

 

[KenHigg]

That's cool - I promise I wasn't trying to be condescending. It's just that I spent about an hour this AM trying figure out why/how your fix worked. When the light did come on I just had to share it -

 

[Brianwarnock]

No problem Ken, I appreciate the education.

Brian