New & in a pickle - reports with dynamic number of columns

[Spirals]

Greetings everyone,

I apologise if this is a wall of text. I'll try to keep it brief.

I took on a project recently to move a heavily automated Excel workbook I'd created to Access. The project is reporting on waste data throughout the city.

I have everything set up, but am having great difficulty with dynamic reports. Nearly all of the existing reports in Excel are dynamic - they report on a waste type (e.g. residual) and a variable month range depending on how much data the requester wants to see (e.g. April - October 2014). The Excel tables are arranged like this (this isn't how my schema looks):

Month 1 Month 2 Month 3 Month 4
Residual 500 300 211 344 etc...

I created a dynamic crosstab query which presented the information just as I'd like it. Then I realised that dynamic crosstab queries don't work in reports/forms unless you specify the column headers. Rather than do a ton of inline SQL, I put the result of the crosstab into a recordset, inserted it into a table, and displayed that on the report. Joy of joys, it worked..

Except...when I delete and recreate the table, the report I use to display it doesn't recognise any new columns as it's still using the (now deleted) table for its data, and asks me for them as parameters (monthly values per month). :banghead:

So, good people, what is the best way to get around this please? If I can get this to work, I can finally start churning out my reports. I'm new to Access and frustrated by encountering so many roadblocks with variable column recordsets.

Many Thanks!!

Spirals

 

[plog]

These tables are arranged like this:

That's the issue, you have your tables incorrectly set up. You don't numerate field names (Month1, Month2, etc.). Tables shouldn't have data added horizontally (with more columns), but vertically (with more rows). The data in your tables should be structured as so:

YourTableName
Waste_ID, WasteType, WasteDate

That's it. Then for a full year's worth of residual data, you input 12 rows.

Now, I know your concerned about building a report, but you are a few steps from there. The first step of building a database is setting up the tables correctly. That process is called normalization (https://en.wikipedia.org/wiki/Database_normalization) read up on that, work a few tutorials and then put your data together properly. Then the next step is building the queries and reports to output your data.

 

[Spirals]

Hi Plog,

Sorry for the confusion - the DB is normalised, but the reports and queries based on the data need to be structured that way (months along the top, waste types along the side). I need my reports/forms to accommodate variable numbers of columns. When I said 'tables', I meant how the presentation looks in Excel's tables currently. Apologies. There's nothing to stop me from representing months by rows, but it wouldn't be an improvement over the Excel workbook's presentation to do it that way (it wouldn't look very attractive).

At this point, I'm thinking (and hoping it's not the case) that the only way I can fanangle this is to loop through all the controls on my report and change them? But that will be pretty horrendous, as I'll need roughly 8 of those queries I mentioned per report.

Cheers,

Spirals

 

[plog]

I'd just use my existing Excel reports as a template. If you don't have it already, I'd add a 'data' tab and then have the actual report tabs reference the data on the data tab.

Looking at the desired output, my guess is the reports themselves are pivot tables (or could be), put the data on the data tab, make the pivot tables reference the data tab, create a query to populate data for the data tab and referesh the pivot tables any time you put new data in the data tab.

 

[Spirals]

I'd just use my existing Excel reports as a template. If you don't have it already, I'd add a 'data' tab and then have the actual report tabs reference the data on the data tab.

Looking at the desired output, my guess is the reports themselves are pivot tables (or could be), put the data on the data tab, make the pivot tables reference the data tab, create a query to populate data for the data tab and referesh the pivot tables any time you put new data in the data tab.

Thanks Plog - I was hoping to move away from Excel, even as a front end of sorts. I guess I've picked a really tricky project? But I'm too stubborn to go down that route until I know there's no other option - is there really no means in Access of doing this?

Many Thanks,

Spirals

 

[plog]

Like you said you could produce some god-awful vba to create controls for all necessary report fields. So its possible.


If you could get rid of the dynamic part of the fields (e.g. have it also have 24 fields), then you could use a crosstab as the basis of your report. But as long as you have a dynamic requirement, its going to be a pain in your Access.

 

[pbaldy]

I create a report with the maximum number of columns and set the various control sources and visibility in the open event of the form based on the query. There are also ways to force static column headers I believe. Google "dynamic crosstab report" and you should get numerous methods.