Reduction of report pages.

It will be interesting to see your code! However I must say I just had a quick look at the database sample and having F1 F2 F3 as columns like you have, if they are all the same thing, then they should really be in a separate table and not horizontal but vertical. Have a look at this post:- Excel in Access for an explanation and a free tool to help you convert them into the correct format.

The attached database has the code that I believe will provide the output you want. I ask you, and any other forum member that are willing, to test this while I document the code better and create instructions on what you will need to do to adapt this to you situation.

The database has a form (Data Input Form) with which you can enter test data. This form also has a button to run the report.

I expect to complete the documentation and instruction by tomorrow.

Please let me know if this does what you want. Also if you could tell me what the data types (text, numeric, date) are in your system it would help me write the instructions.

@Uncle Gizmo, Other than location and year we have no idea what the data is. The test data I put in doesn't mean anything. Without knowing what this is I don't think we can recommend restructuring.

The attached folder contains updated code and the instructions on how to install it in your database. All of this assumes that all of your data types for this report are text. If they are not you might run into problems. Please let me know if you do.

Thank you, this does appear to do what I am looking for I'll work on adapting this and get back to you later if I have further questions.

CybranACU said:

If i could do this i could reduce the report from outputing say ~ 3300 (all locations for each year shown) pages to `330 pages hanged).

Click to expand...

Given the size of this report there's something you should be aware of that I forgot to mention. The code I provided creates a table each time the report is run. I couldn't find anyway around this. This means the size of your database will increase by the size of that table each time the report is run. To recoup that space you will need to run compact and repair. There's an option in the Current Database section of options to "Compact of Close". You might want to consider that option. Alternatively you could put this table in a separate linked database and have that database compacted as part of the code that runs the report. If you want to do this and need help setting it up, let me know.

Thanks, im probably going to have it compact on close, unless theres some reason this is a bad thing? Also i did have a question about the non-text data types, currently it pulls the data in this order, checkbox, number, text, and then repeats. Im thinking about leaving the checkbox out but would rather leave it in. How would I include these with this code?

I've modified the attached database so that the datatypes of the other data are Yes/No, Number, and Text so that it more closely resembles your system. You can see from the INSERT statement in this code how will need to create this SQL. The text fields in this statement are wrapped in the SQE function and have single quotes on both sides of them while the number and yes/no fields do not.

If you have problems creating this statement you can give me a list of the fields and their types or upload a portion of your database that has the input to this report and I will do this for you.