Concatenating multiple records

[computerguy]

I'm trying to concatenate multiple record field items to another table/query (with the purpose of exporting them to another program). I'm a seasoned Access person with limited visual basic knowledge. Any direction or suggestions would be appreciated.

My table/query is as follows;

Client ID Medication
1 Asprin
1 Tylenol
1 Advil
2 Asprin
2 Tylenol

Desired new table/query;

Client ID Medication
1 Asprin, Tylenol, Advil
2 Asprin, Tylenol

 

[KenHigg]

Have you ever done ADO?

I have done it with ado / recordsets but there may be an easier method...

kh

 

[computerguy]

Not really. I'm open to anything. Do you have a direction you might suggest?

 

[KenHigg]

1. Create a tmp table with Client ID and Medication flds.
2. Create a select query with the original data and sort on client id

Do a pc of code that:
3. Opens the the query in an ado record set
4. Opens the temp table in a ado record set
5. Add a new record to the temp table
6. Paste the patid and med from the first record into the temp table recordset
7. Loop through all the records concatenating the medid's. When a new pat id appears, add a new record in the temp table and continue...

I know this is kind of confusing (esp if you don't know ado), and surely someone else has a better solution...

kh

 

[computerguy]

Thanks Ken, I'll try to figure out how to do that. I printed it off. It does sound a bit above my head though.

 

[KenHigg]

If you get hung up, post a db with a couple hundred rows and I'll see if I can whip up a pc of code...

kh

 

[WayneRyan]

computerguy,

There's an MS Knowledge Base article on this, but I can't seem to find it
anymore.

I had to do something similar for a report.

Maybe this thread will help:

http://www.access-programmers.co.uk/forums/showthread.php?t=70399

Wayne

 

[computerguy]

here is a db with a medications table in it. I'll also check out that knowledge base file. I really appreciate the help. I've been racking my brain trying to get this to work.

 

[KenHigg]

I'll try to look at this tomorrow...

kh

 

[KenHigg]

See if this will do what you need:

kh

 

[computerguy]

Ken Rules!!

Ken, that is exactly it. I don't know how to thank you? I have about 3 different tables similar to the medications table that require the same thing. Now I can adapt the situation for all of them since you have put me on the right course. I can't even start to describe how happy this makes me that I can finally put this project to rest. You rule man!! I hope to be able to return the favor some day.

 

[KenHigg]

Cool - Glad to help. I too have picked up a lot just reading posts here on this forum...

kh

 

[John Big Booty]

Don't know if anyone has had a play with Ken's fix on the medication database.

Whilst I was having a play with it and trying to "bash" it to meet my own ends, I discovered that it only concatenates X-1 of X clientID's. ie. in the database as presented there are 32 discrete ClientID's but after the procedure is run only 31 of them have been concatenated. I discovered this after I had implemented the code in my database, and then went back to the original zip file to confirm it was a product of the original code and not something I had inadvertently done whilst I was messing with it.

I'd be interested to now if anyone is able to come up with a fix for this "Feature"

 

[john471]

Ken's script doesn't perform an update operation on RS2. The last update is discarded when the procedure ends and RS2 goes out of scope.

This could be rectified by adding
rs2.Update
rs2.Close
between the existing
rs1.Close
and
MsgBox "Done (KenHigg is so smart!)"


i.e.

rs1.Close
rs2.Update
rs2.Close
MsgBox "Done (KenHigg is (usually) smart!)"


HTH Regards

John.

 

[John Big Booty]

Thanks John

Just what I was looking for. Now I can set to and bash it to meet my own ends