RE: Crosstab Report

Discussion in 'Microsoft Access Reports' started by Duane Hookom, May 10, 2010.

  1. Duane Hookom

    Duane Hookom Guest

    There is a solution for dynamic crosstab reporting at
    http://www.rogersaccesslibrary.com/forum/cross-tab_topic11.html. This
    solution basically creates an alias for each of the column heading values and
    transforms them into A, B, C, D, E, ...

    If your TrainDocName values are fairly static and limited, you might get by
    with setting the Column Headings property of the crosstab query.

    --
    Duane Hookom
    Microsoft Access MVP


    "gngsquared" wrote:

    > I searched, and tried to utilize the answers I found, but I don’t have the
    > skills to make them work.
    >
    > I have the crosstab query shown below which works fine when the results are
    > in datasheet view:
    >
    > TRANSFORM Max(DetailTbl.DetailID) AS MaxOfDetailID
    > SELECT EmpTbl.EmpNo, EmpTbl.Lname, EmpTbl.Fname
    > FROM TrainDocTbl INNER JOIN (EmpTbl INNER JOIN DetailTbl ON EmpTbl.EmpID =
    > DetailTbl.EmpID) ON TrainDocTbl.TrainDocID = DetailTbl.TrainDocID
    > WHERE (((TrainDocTbl.SelectRec)=On) AND ((EmpTbl.TermDate) Is Null))
    > GROUP BY DetailTbl.EmpID, EmpTbl.EmpNo, EmpTbl.Lname, EmpTbl.Fname
    > ORDER BY EmpTbl.Lname, EmpTbl.Fname, TrainDocTbl.TrainDocName
    > PIVOT TrainDocTbl.TrainDocName;
    >
    > [SelectRec] is a Y/N field. There is a form with a listbox that allows the
    > user to check which qualifications meet the customer’s needs and then the
    > query runs to provide the results. There are currently 15 qualifications to
    > choose from, but I’m sure this number will expand as time goes on.
    >
    > The required qualifications may change and the report needs to change the
    > column headings to reflect this and that is the problem. If I design the
    > report for qualifications A, B, and C when the user asks for E, H, and J the
    > report gags.
    >
    > Additionally, I have formatted the text box on the report to show either YES
    > or – by using the following expression: =IIf([fieldname] Is Not Null,"YES","--
    > ")
    >
    > Although this works, I’m sure there is a better way of doing it.
    > HELP!
    >
    > .
    >
     
    Duane Hookom, May 10, 2010
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jiggs

    Re: Getting an error on crosstab query in the report.

    Jiggs, Jul 12, 2003, in forum: Microsoft Access Reports
    Replies:
    0
    Views:
    464
    Jiggs
    Jul 12, 2003
  2. George W. Jackson

    Crosstab Report

    George W. Jackson, Jul 23, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    597
    Duane Hookom
    Jul 23, 2003
  3. Barbara Waller

    Report totals by day of week - from crosstab report

    Barbara Waller, Jan 6, 2005, in forum: Microsoft Access Reports
    Replies:
    10
    Views:
    470
    Barbara Waller
    Jan 9, 2005
  4. Creating Crosstab Reports from Crosstab Queries

    , Feb 17, 2007, in forum: Microsoft Access Reports
    Replies:
    4
    Views:
    448
    Guest
    Feb 25, 2007
  5. Ben8765
    Replies:
    1
    Views:
    527
    KARL DEWEY
    Oct 15, 2009
Loading...

Share This Page