Combining data from multiple records into one record *Access

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
User avatar
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

Post by Tribble »

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.
Image
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: Combining data from multiple records into one record *Ac

Post by doo_much »

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
User avatar
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

Post by Tribble »

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.
Image
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: Combining data from multiple records into one record *Ac

Post by doo_much »

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
User avatar
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

Post by Tribble »

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.
Image
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: Combining data from multiple records into one record *Ac

Post by doo_much »

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.
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
User avatar
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

Post by Tribble »

Semi colon if you don't mind :D Thank you so much. I really appreciate it.
Image
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: Combining data from multiple records into one record *Ac

Post by doo_much »

:oops:

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
User avatar
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

Post by Tribble »

Thanks - you do this - and you shall get an even bigger hug next time we meet :D Thanks
Image
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: Combining data from multiple records into one record *Ac

Post by doo_much »

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! :shock:

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
User avatar
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

Post by Tribble »

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. :D
Image
Post Reply