I am using Access 2010 and my data in table looks like this:

state_id provider_name beg_date end_date description
99999 initial provider name 3/23/2011 4/15/2011 initial service
99999 follow-up provider name 6/6/2011 12/1/2499 follow-up service 1
99999 follow-up provider name 6/6/2011 12/1/2499 follow-up service 2
99999 follow-up provider name 6/6/2011 12/1/2499 follow-up service 3
99999 follow-up provider name 6/6/2011 12/1/2499 follow-up service 4
99999 follow-up provider name 6/6/2011 12/1/2499 follow-up service n


In Access report I want it to look like this:
state_id beg_date end_date Follow-up Date provider Services
99999 3/23/2011 4/15/2011 6/6/2011 follow-up provider name follow-up service 1,follow-up service 2,follow-up service 3,follow-up service 4

It needs to be grouped by initial provider name and state_id.
I'm sure some type of array needs to be built but I'm not sure how to do this.
Can someone help me out with this code to do this.

Thanks
Mark

Hi Mark,

Check out Brent's MakeCSV() function in the Code Archive.

Just my 2 cents...

I'm sorry but I am a programming newb. Where do I submit this procedure from?

Mark

Hi Mark,

You would call the function in a query that you can then use for your report.

I hope that helps...

DBGuy,
Thanks for your help so far.

I created a query with this in field 1: ServicesCombined: MakeCSV((select [VisitswServiceDt]![description] from [VisitswServiceDt] )).
It said the other 3 parameters of the function were optional so I didn't use them. The query fails with the error "at most one record can be returned from this subquery."
Do you know what I'm doing wrong?

Thanks,
Mark

Hi Mark,

Try replacing the () with "". For instance:

ServicesCombined: MakeCSV("select...")

See the difference? Use quotes instead of parens.

Just my 2 cents...

Well, I'm making progress. The function runs but is producing undesired results: "Acute partial hospitalization,Acute partial hospitalization,Acute partial hospitalization,Acute partial....." repeatedly the same service.

I am looking at the code to figure out what I am doing wrong, any suggestions?

Thanks,
Mark

Hi Mark,

You might have to use two queries or a subquery to get the final result.

Just my 2 cents...

Makes sense.

Thanks

Hi,



Good luck! Let us know how it goes...