How to sort field names by the result of a query and create a report that contains grouped results ?

[Gert01]

Hello,

I'm trying to make a local application and I'm stuck.
I'm inputting data into a form ( linked to the table 'questions' ) and based on the data input, I let a query run a calculation ( query1 ).

This query1 creates new field names and the result of the calculations are numbers.

Here's my question :
A) How can I sort the field names based on the result of the query ? -> The query only calculates 1 record at the time ( with 30 columns ), as the application requests input and gives a related output ( output is 50 columns ).
B) I want to make a report from the sorted field names. However, based on the calculation of query1, I want to sort them into 4 groups ( group1, group2, group3, group4 ) which are defined by the value of the result ( like group1 >8, group2 >6, group3 >4, group4<=4 )
I guess it's easier if the field names are converted to column names with the value as the second column, but I don't manage to understand how to do...

Any help would be highly appreciated !

[June7]

Provide example raw data and desired output.

Can't 'sort' fields, sort records. Arrange bound controls on form or report into whatever order you desire.

[Gert01]

Hi,

the result of my query would look like below :

Then based on the score that's calculated in the query, I would like to create a report which ranks the fields in the columns of 'highly recommended', 'recommended',...


Desired report output :

But here I'm stuck, as sorting only works ( as far as I know ) on the individual fields, which in my case only contain 1 value.

[June7]

What is the rule for assigning the descriptive headings? Like: 0-15 is Not Recommended, 16-20 is ...

You show query output. What does the raw data look like? If the query represents raw data, it is not normalized structure.

[Gert01]

I'm using a form with questions to fill a table. The form looks like below :

The table then shapes up like this :

Then based on the table, I made a query which looks like this : ( filled in in the 'Field' cel )

This gives me then a score for each carmaker.

With another query, I convert the score to the recommendation level, you assumed correctly a basic nested Iif function :

BMW score : Iif ([Query1]![BMW]>30;"Highly recommended"; Iif ([Query1]![BMW]>20;"Recommended"; Iif ([Query1]![BMW]>10;"Slightly interesting"; Iif ([Query1]![BMW]<=10;"Not recommended"))))


However I'm not sure if I need to run this query before making the report that's sorting the car brand according to the recommendation level, as I was also trying to get the report structured in a way that all "highly recommended" carmakers would go in a dedicated column based on their score ( over 30 ) and similar for the other levels.

I just hope that my structure is ok ( as I'm a rather novice user of Access... )

[June7]

The table is not a normalized data structure.

I suspect generating that columns by category output will not be easy.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gert01]

Probably a transpose function would work out fine, but I'm not sure how to construct it. ( type of query or using VBA of which I have no knowledge )

Thanks for the quick replies, guess I should dig a bit deeper.

[June7]

That would be a CROSSTAB query but I don't think your data is properly structured for that. Might need an intermediate UNION query then run the CROSSTAB. Gets complicated. Proper data structure makes a big difference.

And yes, VBA writing records to a 'temp' table is an option.