Report with variable Columns

[TimTDP]

I have two tables, connected via a one to many relationship. Let's call them tblOne and tblMany.
I need to create a report which will group on each record on tblOne. However in the detail section I need each record in tblMany to show as a seperate column. The problem is that I never know how many records there will be in tblMany related to tblOne.

Any ideas?

 

[Ed2020]

Tim,

You need to show each record in tblMany as a column (not a row)?

It sounds like you need a crosstab query of some sort and then base the report on that.

Unfortunately MS Access is a little quirky when basing reports on a crosstab query as it will only add as many columns as you require for the current dataset (e.g. the data you have in the system at the time you actually create the report).

There are two solutions (that I am aware of) for this if you have to use an Access report:

1. Set the crosstab properties to explicitly name the column titles so the report will always display them. This probably won't work for you as you say you don't know how many columns there are each time you run it. Do you perhaps know the total number of columns though?

2. Create a VB routine that will generate a new report with the correct columns on the fly every time you run it. This is, unfortunately, quite a lot of work. On the other hand it impresses other developers!

Alternatively you could create the query and export it as an Excel file and then just create a VB routine to apply formatting to the Excel file, using Automation. The VB routine could open the file and display it once formatting has been completed.

Ed Metcalfe.

Please do not feed the trolls.....

 

[TimTDP]

Hi Ed

Many thanks.

In soltion 1, I can count the number of records, at report run time, which will give me the total number of columns