Concatenate variable number of values to one row

[Big Pat]

Hi,

I have attached a sample accdb which shows how far I've got with this.

Background
We have 30 hospitals in our region each designated by alpha codes. The hospitals can run various clinical studies, designated by numeric codes. Sometimes studies are specific to one hospital, but more often the same study is run at multiple hospitals.

The important dates, for this exercise, are the 'application', 'open' and 'close' dates. From these I can work out which studies are currently open at which hospitals. This is shown in query 1 which returns one row of data for every open study-hospital combination

Requirement
I need one row of data per open study, with a column that includes the codes for the hospitals where that study is open, separated by commas, but all in one column. This is needed as a data source for a completely different system which will update an online database.

So I need output that looks like this, but there is no way of knowing in advance how many different codes might need to be concatenated into the "Open hospitals" column.

StudyID Open hospitals
1234..... "UHB"
1357..... "RWT"
2468..... "BCH,RWT,UHB"
5678..... "UHC,WMS" Edit: I don't need the quotes I just put them here to show that this is all one column.

I have also created query 2, as a crosstab, which at least gives me one row of data per open study. I thought I might be able to figure out a step 3 from this, to give me the output I need, but I can't do it. And maybe this isn't the right approach anyway.

Can anyone explain what I need to do to achieve this? Preferably by using one or more queries rather than by using VBA. I'm not a programmer/developer, I don't use Access very much and I probably wouldn't understand the VBA, which would make it difficult to translate any solution into the real database.

However, if it has to be VBA, go for it and I'll try hard to keep up

Thank you.

Pat

 

[CJ_London]

have a look at the concatrelated function by Allen Browne which you can use in your query

http://allenbrowne.com/func-concat.html

 

[Big Pat]

Wow....that works! I won't claim to understand it, but after inserting that module and a fair bit of trial and error replacing the right fieldnames and query names I seem to have got there.

I had no end of trouble with the Order By part until - duh! - I realised I didn't need the words "Order By", but just the fieldname in quotes and square brackets.

Supplementary question: Is it expected behaviour that the query returns duplicate rows as follows?

StudyID OpenSites
1234 UHB, WHT
1234 UHB, WHT
5678 UHC, WMS
5678 UHC, WMS
2468 BCH, RWT, UHB
2468 BCH, RWT, UHB
1357 RWT
2468 BCH, RWT, UHB

It's easily fixed by clicking the Total button which adds a Group By clause, and gives me EXACTLY what I need. but I just wonder whether I have missed a step or done something wrong.

Thanks so much for this!

 

[CJ_London]

Is it expected behaviour that the query returns duplicate rows as follows?

looks like that is duplicates in your main table - try select distinct rather than group by