none
Dynamic Columns in Detail Section RRS feed

  • Question

  • I need to have 5 columns on the report and those columns will vary in order based on 5 values from another query and the order they are returned in. So for instance the first query gives me B, C, D, A, E for the first class. The second class might return A, B, D, E, C. and so on based on the classes. The report will be ran for one class at a time. Based on these rows I need take data from other tables and then display data. So for B I would query another table and get the value 1 for instance, and then for C, D, A, and E. For each letter I need to display the value returned from the query. The query for the detail section will have 3 rows. Each of the 3 rows has values that are used along with the letter to get the number I need to display. What is the best way to accomplish this?

    Dale

     
    Thursday, July 30, 2015 8:34 PM

Answers

  • I need to have 5 columns on the report and those columns will vary in order based on 5 values from another query and the order they are returned in. So for instance the first query gives me B, C, D, A, E for the first class. The second class might return A, B, D, E, C. and so on based on the classes. The report will be ran for one class at a time. Based on these rows I need take data from other tables and then display data. So for B I would query another table and get the value 1 for instance, and then for C, D, A, and E. For each letter I need to display the value returned from the query. The query for the detail section will have 3 rows. Each of the 3 rows has values that are used along with the letter to get the number I need to display. What is the best way to accomplish this?

    Hi Dale,

    I have no experience with Access reports, but quite a lot with dynamical forms. However, I expect that the reports behave pretty much the same.

    In the Open or Load event of the form (report), you can change the ControlSource of any control to a different value, as you can change the Caption of the column header, if you have that, in the order that is determined by the "another query".

    Imb.

    • Marked as answer by Dalester Saturday, August 1, 2015 4:00 PM
    Saturday, August 1, 2015 10:45 AM

All replies

  • Try using Excel display to show your desired results from raw data example.

    Build a little, test a little

    Thursday, July 30, 2015 8:55 PM
  • I am not really sure what you mean by excel display but each of the 5 columns are not in a fixed order. The are ordered based on a query and calculation in it. That is why each class possibly could be in a different order. So its not like I can do one query to get the results unless you know how arrange columns in a query based on another query  and I do not know how to that.

    Dale

    Thursday, July 30, 2015 9:41 PM
  • Hi Dale,

    You need to use unbound controls on your report and populate these via VBA Code at runtime. You will also need to set the Label Captions to match the fields you display.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Thursday, July 30, 2015 9:56 PM
  • How do I do that in the detail section so I can run a query based on each row in the detail section? That is what I thought but I did not know how to get vba code to run on each row for the detail section.

    Dale

    Thursday, July 30, 2015 9:58 PM
  • Post some raw data with table and field names.   Then using Excel post and example of what that data would look like with one set of columns and repeat with more example data and output that would be different.

    Make the data be diverse enough so I can follow what is put where and why column order is as displayed (low to high, alpha, or what). 


    Build a little, test a little

    Thursday, July 30, 2015 10:04 PM
  • Hi Dale,

    It needs some quite complex code. i just did a quick google and found this one that may help you.

    http://bytes.com/topic/access/insights/696050-create-dynamic-report-using-vba 

    See the section titled Dynamic report designed to be used with Crosstab Queries

    Good luck


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Thursday, July 30, 2015 10:31 PM
  • Here I some data. The letters A,B,C,D,E are column headings and retrieved by the first query. The second query will get class nbr, horse number and placing. The dynamic piece is the numbers below the letters which is based on the letter, class number and horse number.

    Class Nbr H Number Placing B C D A E
    1 231 1 1 1 2 2 3
    1 432 2 2 3 3 3 2
    1 321 3 3 2 1 1 1
                   
          A C D B E
    2 121 1 1 2 2 1 3
    2 132 2 3 3 3 3 4
    2 230 3 2 4 5 6 5
                   
          C B A E D
    3 134 1 1 2 2 3 4
    3 135 2 2 1 3 2 5
    3 145 3 3 3 1 4 6

    Thursday, July 30, 2015 10:41 PM
  • The letters A,B,C,D,E are column headings and retrieved by the first query. The second query will get class nbr, horse number and placing. The dynamic piece is the numbers below the letters which is based on the letter, class number and horse number.

    Post the SQL of first and second query.   Post some raw data with table and field names.


    Build a little, test a little

    Thursday, July 30, 2015 10:51 PM
  • Here is data from the first query

    <tfoot></tfoot>
    DeviationOrder
    Class Nbr FinalPlacing SumOfplacing Expr1 judgeid Code
    197 6 7 -1 Higuera B
    197 6 7 -1 Miranda C
    197 6 7 -1 Moreno D
    197 6 7 -1 Pastrana E
    197 6 0 -6 Torres A

    This is the second query

    <tfoot></tfoot>
    DeviationHorse
    HorseID Placing Class Nbr
    Dulce Arcangel de United 1 197
    Vivaldi de Besilu 2 197
    Novedoso de La Seda 3 197

    This is data that would be dynamic based on the first two

    <tfoot></tfoot>
    DeviationPlaces
    entryblankid horseid EBEntryScore.ClassID EBEntryScore.JudgeID score placing Cut phaseid ClassJudges.ClassID JudgesID Code Call
    2 Evangelio de Victoria 197 Torres . 0 No 2 197 Torres A No
    2 Evangelio de Victoria 197 Higuera . 3 No 6 197 Higuera B Yes
    2 Evangelio de Victoria 197 Moreno . 3 No 6 197 Moreno D No
    2 Evangelio de Victoria 197 Miranda . 3 No 6 197 Miranda C No
    2 Evangelio de Victoria 197 Pastrana . 0 No 6 197 Pastrana E No
    49 Dulce Arcangel de United 197 Torres . 0 No 2 197 Torres A No
    49 Dulce Arcangel de United 197 Higuera . 1 No 6 197 Higuera B Yes
    49 Dulce Arcangel de United 197 Moreno . 1 No 6 197 Moreno D No
    49 Dulce Arcangel de United 197 Miranda . 1 No 6 197 Miranda C No
    49 Dulce Arcangel de United 197 Pastrana . 1 No 6 197 Pastrana E No
    65 Desvelo de Don Martin 197 Torres . 0 Yes 2 197 Torres A No
    65 Desvelo de Don Martin 197 Higuera . 0 Yes 6 197 Higuera B Yes
    65 Desvelo de Don Martin 197 Moreno . 0 Yes 6 197 Moreno D No
    65 Desvelo de Don Martin 197 Miranda . 0 Yes 6 197 Miranda C No
    65 Desvelo de Don Martin 197 Pastrana . 0 Yes 6 197 Pastrana E No
    109 Vivaldi de Besilu 197 Torres . 0 No 2 197 Torres A No
    109 Vivaldi de Besilu 197 Higuera . 2 No 6 197 Higuera B Yes
    109 Vivaldi de Besilu 197 Moreno . 2 No 6 197 Moreno D No
    109 Vivaldi de Besilu 197 Miranda . 2 No 6 197 Miranda C No
    109 Vivaldi de Besilu 197 Pastrana . 2 No 6 197 Pastrana E No
    118 Atrapasuenos de Armoagro 197 Torres . 0 Yes 2 197 Torres A No
    118 Atrapasuenos de Armoagro 197 Higuera . 0 Yes 6 197 Higuera B Yes
    118 Atrapasuenos de Armoagro 197 Moreno . 0 Yes 6 197 Moreno D No
    118 Atrapasuenos de Armoagro 197 Miranda . 0 Yes 6 197 Miranda C No
    118 Atrapasuenos de Armoagro 197 Pastrana . 0 Yes 6 197 Pastrana E No
    124 Gamin Elegante 197 Torres . 0 Yes 2 197 Torres A No
    124 Gamin Elegante 197 Higuera . 0 Yes 6 197 Higuera B Yes
    124 Gamin Elegante 197 Moreno . 6 Yes 6 197 Moreno D No
    124 Gamin Elegante 197 Miranda . 6 Yes 6 197 Miranda C No
    124 Gamin Elegante 197 Pastrana . 0 Yes 6 197 Pastrana E No
    167 Novedoso de La Seda 197 Torres . 0 No 2 197 Torres A No
    167 Novedoso de La Seda 197 Higuera . 4 No 6 197 Higuera B Yes
    167 Novedoso de La Seda 197 Moreno . 4 No 6 197 Moreno D No
    167 Novedoso de La Seda 197 Miranda . 4 No 6 197 Miranda C No
    167 Novedoso de La Seda 197 Pastrana . 4 No 6 197 Pastrana E No
    186 Quinto Elemento de Paso Hondo 197 Torres . 0 Yes 2 197 Torres A No
    186 Quinto Elemento de Paso Hondo 197 Higuera . 0 Yes 6 197 Higuera B Yes
    186 Quinto Elemento de Paso Hondo 197 Moreno . 0 Yes 6 197 Moreno D No
    186 Quinto Elemento de Paso Hondo 197 Miranda . 0 Yes 6 197 Miranda C No
    186 Quinto Elemento de Paso Hondo 197 Pastrana . 0 Yes 6 197 Pastrana E No

    Dale

    Friday, July 31, 2015 12:57 AM
  • Post the SQL of the query.

    Build a little, test a little

    Friday, July 31, 2015 1:08 AM
  • First query

    SELECT EBEntry.ClassID, Sum(EBEntry.Placing) AS FinalPlacing, Sum(EBEntryScore.placing) AS SumOfplacing, Sum(IIf(ebentry.placing-EBEntryScore.placing<0,ebentry.placing-ebentryscore.placing,(ebentry.placing-ebentryscore.placing)*-1)) AS Expr1, EBEntryScore.judgeid, ClassJudges.Code
    FROM (EBEntry INNER JOIN CurrentClass ON EBEntry.ClassID = CurrentClass.ClassID) INNER JOIN (EBEntryScore INNER JOIN ClassJudges ON (EBEntryScore.classid = ClassJudges.ClassID) AND (EBEntryScore.judgeid = ClassJudges.JudgeID)) ON (EBEntry.ClassID = EBEntryScore.classid) AND (EBEntry.HorseID = EBEntryScore.horseid) AND (EBEntry.EntryBlankID = EBEntryScore.entryblankid)
    WHERE (((EBEntry.Placing)<=3 And (EBEntry.Placing)>0))
    GROUP BY EBEntry.ClassID, EBEntryScore.judgeid, ClassJudges.Code
    ORDER BY EBEntry.ClassID, Sum(IIf(ebentry.placing-EBEntryScore.placing<0,ebentry.placing-ebentryscore.placing,(ebentry.placing-ebentryscore.placing)*-1)) DESC , ClassJudges.Code;

    Second Query

    SELECT EBEntry.HorseID, EBEntry.Placing, EBEntry.ClassID
    FROM CurrentClass INNER JOIN EBEntry ON CurrentClass.ClassID = EBEntry.ClassID
    WHERE (((EBEntry.Placing)<=3 And (EBEntry.Placing)>0))
    ORDER BY EBEntry.Placing;

    Third Query

    select * from ebentryscore inner join classjudges on classjudges.classid = ebentryscore.classid and classjudges.judgeid = ebentryscore.judgeid where classid = " classid from 2nd query " and classjudges.code = " code from first query " and ebentryscore.horseid = " horse id from second query

    Friday, July 31, 2015 11:36 PM
  • I need to have 5 columns on the report and those columns will vary in order based on 5 values from another query and the order they are returned in. So for instance the first query gives me B, C, D, A, E for the first class. The second class might return A, B, D, E, C. and so on based on the classes. The report will be ran for one class at a time. Based on these rows I need take data from other tables and then display data. So for B I would query another table and get the value 1 for instance, and then for C, D, A, and E. For each letter I need to display the value returned from the query. The query for the detail section will have 3 rows. Each of the 3 rows has values that are used along with the letter to get the number I need to display. What is the best way to accomplish this?

    Hi Dale,

    I have no experience with Access reports, but quite a lot with dynamical forms. However, I expect that the reports behave pretty much the same.

    In the Open or Load event of the form (report), you can change the ControlSource of any control to a different value, as you can change the Caption of the column header, if you have that, in the order that is determined by the "another query".

    Imb.

    • Marked as answer by Dalester Saturday, August 1, 2015 4:00 PM
    Saturday, August 1, 2015 10:45 AM
  • That will work I was hoping for an easier way so I did not have to do so much coding behind the report as I will end up with around 30 fields I will have to fill out manually on the report.

    Dale

    Saturday, August 1, 2015 4:01 PM
  • That will work I was hoping for an easier way so I did not have to do so much coding behind the report as I will end up with around 30 fields I will have to fill out manually on the report.

    Hi Dale,

    I must admit that you have to use code. That just makes it special, so you can solve your problem!

    I do not know your problem in more details , but I believe there is a couple of alternative ways to go. Instead of doing all the processing within the report, you can run the "another query" in a form, store the sorted data in a (temporary) table, then open the report from that form, and use this table inside the report to fill in the ControlSources ans Captions.

    Just one idea.

    Imb.

    Saturday, August 1, 2015 6:12 PM