Creating a report based on crosstab query , variable columns (1 Viewer)

[cewolf]

Greetings
i have been searching for a solution for hours ..but with no luck , i would really appreciate a help

i have a crosstab query that will produce different results based on user's input ....
so input A will give a query with 3 headings Q1, Q2, Q3
input B will give a query with 5 headings Q12, Q13, Q14, Q16, Q17
input C will give a query with 2 headings Q5, Q6
and so on
i dont want to create a report for every possible option since i have a LOT of them also the result (headings) of each option will change with time ..

how can i generate a report that can adjust to the different numbers of columns , fields names and labels

i understand that the only way to achieve that is via Code, but my coding skills are primitive ... so please try to be more detailed

thank you very much in advance

 

[Uncle Gizmo]

I think you are falling into the same trap most people do when they use MS Access, and that is using it like an Excel spreadsheet. Now, I don't know enough about your database to make that judgement but from what I can see from your questions so far, then I am on high alert for it being this sort of problem. The big red flag is always when you ask the question how can I add or Take Away columns. It's always difficult to add or Take Away columns but it's always easy to add or takeaway Rows. Get my drift?

Unfortunately when someone asks this question, it usually points to a structural problem and by way of an answer most people will Direct you to go back and reconsider the way you have constructed your tables in the first place. It's not something most people want to hear! They just want to carry on as they are and make it work, and indeed you may well have some success. However from my own experience you will eventually get to the bottom of the hole you are digging yourself.

I posted an article on my website here:- "Excel in Access" https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access

There is a written example with pictures and a video example showing a slightly different starting point. There is also a free tool to help you with the conversion process.

 

[cewolf]

Thanks for your reply

i am very aware of what are you saying , but sadly the data base HAS to be this way ...
the idea is to keep it a user friendly as much as possible since end user will have no idea how to create Queries and or reports ..
so i have to give the user the ability to define his own questions (records) that will become the headers of the crosstab Query later ..
i need a one report that fits all , since many studios in our company will be using the database and they have their own softwares and their own Q's, they don't know how to create reports

i came with a way around it , i will limit the Qs to 25 only
each Question will start with the code, Q1, Q2, Q3 ...
so for 3Ds max you will have Questions as
Q1 Modeling
Q2 Texturing
Q3 Animating
and for Photoshop
Q1 Using layers
Q2 Filters
Q3 Blending modes

and so on ...
on the crosstab Query i will group headings based on Left([Qustion],3)
so all the headings will be Q1, Q2, Q3....Q25 regardless of software
on the report the will be filtered by software only the questions related to that software will show, i will use a DLookup function for the headers of the report so the user will know what is Q1 Q2 Q3...Q25 means ...

a little dodgy but it would work ...
but i am still hopping that some one would be able to give me a better solution
thanks

 

[Gasman]

Try googling for 'Dynamic Crosstab Access'
I looked at dynamic crostab a few years back when I first started creating a database for work colleagues , but luckily did not have the need to use it before I left.

Does this thread help at all.

JHB posted a working solution I think?

https://access-programmers.co.uk/forums/showthread.php?t=242545

 

[Uncle Gizmo]

I have this idea at the back of my mind that doing questionnaires in MS Access is difficult. I got this idea from reading a couple of posts a few years back. I have no idea of the validity of the notion.

The nearest I have got to do anything like a questionnaire is a checklist. My original checklist was a simple tick box checklist which evolved into a way of showing multiple checkbox lists. It evolved again when I altered it slightly to allow text entries. I am considering improving it to allow the use of a combobox to provide multiple answer selection. The method I'm thinking of using is one I saw years ago in "Smart Access" (I think). It's where you have an invisible combobox which is made visible to gather the data however I've never been a fan of this interface design. I am a bit reluctant to pursue it. If it's any help I did a comprehensive blog on creating checklists on my website here. It includes text and videos...

https://sites.google.com/site/msaccess457966vmfjg/advanced-techniques/check-lists

 

[Gasman]

This thread shows you how to set the captions and sources in the report?

https://www.experts-exchange.com/questions/28370681/Ms-Access-Crosstab-Report-with-variable-number-of-columns.html

Also a good link to show you how to do it without any VBA.

I think it was this method I was looking at a few years back.