Add columns to a report 2

[TimTDP]

I want to create a report that can automatically allow for multiple columns at run-time.

For example, a product can have multiple delivery areas (one to many relationship), with each delivery area having its own costings.

At design time I do not know how many delivery areas each product has.

How can I create a report that automatically includes all Area's, as a seperate "Column", with the relevant costings shown below as rows?

 

[HandsOnAccess]

An alternate solution is to use Group By Clause on Delivery Area.

Since the number of delivery areas for each product is unknown, Creating a report that automatically includes all areas as a seperate "Column" will only make it unpredictable and complicated. Also it is quiet difficult to test such report.

By using a Group By Clause, a stable report can be generated.

Hope this helps you...

Regards.

 

[TimTDP]

Thanks for the reply

Not the answer I was hoping for, but it took me out of that thought process and into another which gave me a better report!

 

[danvlas]

If the max number of areas is known and/or total number of returned columns is less than 254, a crosstab query could retrieve the records and the report can show only the columns that are returned. All others should be hidden by default (but should exist on the report).

Access 2003 provides the .Recordset property for the report so you can use it to determine the columns.
In previous versions you need to find a way to determine how many columns are returned before displaying the report.

I agree that organizing reports vertically is easier and much more flexible, but sometimes management only asks for compact reports showing everything on one page.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html