Access Report display contents horizontally not vertically

[rjanzic]

If I have a report that show all values five groups into the database with fields being displayed vertically, how can this be changed so the detail is displayed horizontally. I hope the following best describes this:



The way Access currently sets up its report grouped on a fifth set of variables:

Field 1

Field 2

Field 3

Field 4

Field 5

Field 6a

Field 6b

Field 6c

Etc

The way I would like to have the data displayed:

Field 1

Field 2

Field 3

Field 4

Field 5

Field 6a, Field 6b, Field 6c, etc

How would one go about programming this? Any help would be greatly appreciated.

Regards,

Rob

[June7]

Review:
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html

[rjanzic]

Thanks for these links, but not totally what I am after. I have asked a programmer friend to help out and when I get a solution will see about posting the code.

[QUOTE=June7;142284]Review:
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html[/

[rpeare]

Look at crosstab queries. Basically you're taking row labels and trying to make them column headers which is what crosstab queries are built to do

[June7]

Crosstab might be an option. However, can be frustratingly dynamic in that the fields returned each time might be different. Stabilizing a crosstab to run perpetually in a report can be tricky.

[rpeare]

Absolutely right, only a solution best used if your intended columns never change or change so rarely the maintenance would be low.

[rjanzic]

Absolutely right, only a solution best used if your intended columns never change or change so rarely the maintenance would be low.

Yes, crosstab queries don't really work either as the data is quite variable. There might be 45 field values in one case and then just one in another. Anyway, have looked for the code on the web, but couldn't find it and I think this would be the best way, run through the dB until EOF and have a string variable concatenate all the values into one field. That's the way forward I think and will follow-up with our solution.

Regards,

Rob

[June7]

I provided link to Allen Browne's "concatenate values from related records" tutorial. It offers code to do just what you describe.

Unfortunately, Allen neglects this one little instruction in his tutorial:

Set a VBA reference to: Microsoft DAO 3.x Object Library

From the VBA editor > Tools > References > find and check the library