Concatenating multiple records

C

computerguy

New member
Local time
Today, 03:20
Joined
Sep 1, 2004
Messages
5
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
 
Have you ever done ADO?

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

kh
 
Not really. I'm open to anything. Do you have a direction you might suggest?
 
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
 
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.
 
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
 
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.
 

Attachments

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

kh
 
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.
 
Cool - Glad to help. I too have picked up a lot just reading posts here on this forum...

kh
 
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"
 
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.
 
Thanks John

Just what I was looking for. Now I can set to and bash it to meet my own ends :D
 
You must log in or register to reply here.

Similar threads

Y
Import-export permit tracking system
Replies
13
Views
777
mike60smart
mike60smart
D
Not sure how best to display the information
2
Replies
37
Views
1,112
Mike Krailo
Mike Krailo
I
NEED HELP! Adding a button on the form to move a record from a table to another
Replies
5
Views
252
The_Doc_Man
The_Doc_Man
S
Interesting puzzler
2 3
Replies
51
Views
1,075
Gasman
Gasman
K
Design/Structure Ideas for Data Entry form with multiple levels
Replies
18
Views
696
LarryE
LarryE

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom