Combining data from multiple records into one record *Access
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Combining data from multiple records into one record *Access
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.
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.
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: Combining data from multiple records into one record *Ac
Get Ruad to do is in SQL - you're going to trip yourself up doing it in VBA.
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Combining data from multiple records into one record *Ac
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.
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: Combining data from multiple records into one record *Ac
How many different agents do you have?
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Combining data from multiple records into one record *Ac
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.
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: Combining data from multiple records into one record *Ac
So you just want to concatenate every experience code seperated by a hyphen?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.
I'll give the loop a try.
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Combining data from multiple records into one record *Ac
Semi colon if you don't mind Thank you so much. I really appreciate it.
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: Combining data from multiple records into one record *Ac
Semi-colon it is.
I have to be off. I'll have a look when I'm back home.
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Combining data from multiple records into one record *Ac
Thanks - you do this - and you shall get an even bigger hug next time we meet Thanks
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: Combining data from multiple records into one record *Ac
Well, I certainly tripped myself up trying this!doo_much wrote:Get Ruad to do is in SQL - you're going to trip yourself up doing it in VBA.
Anyway. Apparently - if you know what you're doing - this is quite easy.
Have a look at this.
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Combining data from multiple records into one record *Ac
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.
Thank you again - that is a big help.