Transpose and concatenate query results

[DevTycoon]

I am trying to generate a simple report that can be imported to excel for analysis. Currently the database documents failure causes associated with a repair. I need a "Report" that does not duplicate the failed items in the event there is more than one cause associated with the failure. some repaired items may have up to 3 causes of failure. What I would like to do is have the query show one record instead of three records (for the cases were three causes of failure were identified for a repair)


Current query format

facilityID                                 Failure Cause
       1                                                  Electrical Surge
       1                                                  Mechanical Wear
       1                                                  Corrosion
       2                                                  ......
       2                                                  ......
      ....                                                 ......

Desired query format

FacilityID                                     Failure Cause(s)
 1                                                  Electrical surge, Mechanical wear, Corrosion
 2                                                  .....
....                                                 .....

I was reading about a SQL transpose call but I do not need all fields transposed (only the failure causes transposed and concatenated into one field)



Thanks for any help or resources. This has been a serious limitation for my project.


Test database attached with a query that shows the data I want to transform into the desired format.

 

[vbaInet]

Something like this:

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

 

[jdraw]

Research Access Pivot Table

also Allen Browne function concatenate

Good luck

 

[vbaInet]

Research Access Pivot Table

also Allen Browne function concatenate

Good luck

Too slow there my friend

 

[jdraw]

@vbaInet,

Yes, I hunt and peck when typing --seems the damn keys are hiding.
I think we're pretty much on the same wavelength regarding the OP's situation.
Cheers!

 

[vbaInet]

I think we're pretty much on the same wavelength regarding the OP's situation.
Cheers!

Most definitely!

 

[DevTycoon]

That function looks like just what the doctor ordered. I am closing this thread and if I have issues with function I will post to VBA section. Pivot table looks nice too but reading the VBA code it seems straight forward.

Thanks everyone.