PCFormat Forum Archive

Here is my problem.

I have a query that combines data from multiple fields into one field. I now need to combine all the generated fields from multiple records into one record in a query. I will then use that query in another query (yes - I know SQL is better for this but I have no idea how to do it in SQL)

EG:
Query Structure is as follows:
[Agent code] [CPDActivID] [Date of Advice] [Date of Int Serv] [Date of Serv Super] [Experience] [Process Flag]

The fields that I am interested in are [Agent code] and [Experience]
The query is called qryFSBExperience

Each agent would have one or more records listing the experience. I need to combine them in a query that would only contain [Agent code] and [Experience] to look something like this:

[Agent code] [Experience]
1.................1/2/02022002/01032004//
1.................1/12//01012000///
2.................1/2/01091999/03032001/21092003
2 .................1/7//03071995///
3................. ////


should become

[Agent code] [Experience]
1 .................1/2/02022002/01032004//;1/12//01012000///
2 .................1/2/01091999/03032001/21092003;1/7//03071995///
3 .................////

I know it has to be done in a Conc module (concatenate) but I am not getting it right.

Please help - my knowledge of VBA is dangerous.

Get Ruad to do is in SQL - you're going to trip yourself up doing it in VBA.

Mmmm - really? I have a friend who will work on the SQL solution but I need it sooner and not sure how long he is going to take. I googled it and there is very good help but only if you understand wth they are doing. And I don't.

How many different agents do you have?

About 600 if I remember correctly. Sorry - not at the office anymore so I don't have access to the SQL tables. But this report will only run twice a month - so it doesn't matter if it is sluggish.

Tribble wrote:About 600 if I remember correctly. Sorry - not at the office anymore so I don't have access to the SQL tables. But this report will only run twice a month - so it doesn't matter if it is sluggish.

So you just want to concatenate every experience code seperated by a hyphen?
I'll give the loop a try.

Semi colon if you don't mind Thank you so much. I really appreciate it.

Semi-colon it is.

I have to be off. I'll have a look when I'm back home.

Thanks - you do this - and you shall get an even bigger hug next time we meet Thanks

doo_much wrote:Get Ruad to do is in SQL - you're going to trip yourself up doing it in VBA.

Well, I certainly tripped myself up trying this!

Anyway. Apparently - if you know what you're doing - this is quite easy.

Have a look at this.

Thank you so much - that is more helpful than I have found to date. I shall read through and give it a try.

Thank you again - that is a big help.