none
Create Report from a crosstab query in Access VBA RRS feed

  • Question

  • Hello

    I am hopping someone can help, I need to create a report from a crosstab query, however, the information changes - it is a training plan and employees come and go.

    so i did use the :

    RunCommand acCmdNewObjectAutoReport

    however I am having trouble formatting the report in VB

    can anyone help?

    Wednesday, May 29, 2019 11:01 PM

All replies

  • Dynamic crosstab reports are complicated. See for example:

    http://www.rogersaccesslibrary.com/forum/topic362.html


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, May 30, 2019 10:33 AM
  • I need to create a report from a crosstab query, however, the information changes - it is a training plan and employees come and go.

    From that I assume that the employees are the column headings.  A way in which dynamic crosstab reports can sometimes, but not always, be avoided is the return the column headings, the values at the intersections of rows and columns, and any aggregated values in a report footer by means of subreports with across-then-down column layout.  As the column headings are data, there is then no need to dynamically manipulate the report definition at runtime.

    You'll find examples of such reports in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    Towards the end of the section on 'retrieving data from the database' in this little demo file you'll find a form in which there is the option to return the same data in three different report layouts.  The reports are of payments by customers by date, and the third option returns the data in a layout similar to a crosstab query, with the customer names as the 'column headings', the dates of payment as the 'row headings' and the payments per customer per date at the intersections, leaving these blank where a customer has not made any payment on the date in question.  In the report footer the sums of payments per customer are returned

    The way it works is as follows:

    1.  The parent report returns the dates of payment within a range selected in a form, and the total payments by all customers per date.

    2.  The 'column headings' are returned by a subreport based on a simple query which returns all customer names.

    3.  The total payments per customer are returned by a subreport in the report footer, which is based on a simple aggregating query restricted to the selected date range by referencing the controls in the from as parameters.

    4.  The real work is done in the query behind the subreport in the detail section.  This needs to return, for each date, the number of rows which the report allows as the maximum number of columns, seven in the demo, but this can be any number capable of being accommodated across the page width.  This is done by means of a UNION ALL query in which the first part returns the date, and the customer and payment for that date, if any.  The second part of the query returns the additional rows per date to make up the maximum number of seven, returning the constant 'ZZZ' as the customer to ensure that these sort after the real customer name.  In the report these additional columns are blank.

    I assume that, in your case. the 'row headings' are some sort of training events or similar, so you'd use an Events table or similar in place of my Calendar table of dates.  In place of my Customers1 table you'd use an Employees table or similar.  For the data in the detail section you'd use whatever is analogous to the CustomerPayments table in my demo.  The important thing is that the query behind the subreport in the detail section returns the same number of rows per training event as the maximum number of columns allowed across the page when creating the subreport.



    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Sunday, June 2, 2019 4:43 PM Typo corrected.
    Sunday, June 2, 2019 4:30 PM