none
Formatting a custom report RRS feed

  • Question

  • Hi all,

    I have a table in Access 2013 with the following fields:

    • TMDate
    • Customer
    • Product
    • Quantity (This qty is a sum of the transaction quantities)

    Data in these fields looks like this:

    <tfoot></tfoot>
    TMDate Customer Product Qty
    15-02-2016 MOGALAKWENA NORTH NORILOSE 6064 BULK 279900
    09-02-2016 TWO RIVERS PLATINUM (PTY) LTD SENDEP 30D BULK 177280
    08-02-2016 MOTOTOLO JOINT VENTURE NORILOSE 7062 BULK 109720
    08-01-2016 RPM WATERVAL UG2 SENFLOC 2820 BULK 26900
    02-02-2016 DE BEERS CONS MINES KIMBERLEY SENDEP 30E BULK 66000
    12-02-2016 THARISA MINERALS - GENESIS PLANT SENFLOC 9000 BULK 208620
    10-02-2016 DE BEERS CONS.LTD - VOORSPOED SENDEP 30F BULK 86000

    My report layout:

    What I want to achieve in the report, is that if the month part of the date = "02", the quantity must be displayed in the "February" column of the report. If the month is "01", the quantity must be displayed in the "January" column, and so on.

    The fields "Qty" and "TMDate" will be removed when the report is working correctly.

    Does anybody have any advise on how to achieve this?Thanks in advance

    Deon

    Thursday, February 18, 2016 12:18 PM

Answers

  • Use a Crosstab query.   Below should work.

    TRANSFORM Sum([YourTable].Qty) AS FirstOfQty
    SELECT  [YourTable].Customer,  [YourTable].[Product]
    FROM [YourTable]
    GROUP BY  [YourTable].Customer,  [YourTable].[Product]
    PIVOT Format(ItemTagNUM.[TMDate], "yyyy mmm");

    EDIT - changed First to Sum.


    Build a little, test a little


    • Edited by QA Guy CommElec Thursday, February 18, 2016 6:36 PM
    • Marked as answer by Deon SA Wednesday, February 24, 2016 11:33 AM
    Thursday, February 18, 2016 6:15 PM

All replies

  • Good Morning Deon,

    First, it is recommended that you base your Report on a Query of the Data rather than the Tables directly, Second, try to stay away from calculated fields in the Tables, do this in the Query if needed. Most calculations can be performed in the Form or Report directly so you are not storing results in the database.

    As for formatting in the Report you can use the Reports Grouping & Sorting features you can access these from the Ribbon in the Reports Design View: Design \ Grouping & Totals \ Group & Sort.

    You can select the Grouping and the Field to Group on or use an expression. You can have groups break on a page also. Let us know if you need further assistance.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, February 18, 2016 2:43 PM
  • Use a Crosstab query.   Below should work.

    TRANSFORM Sum([YourTable].Qty) AS FirstOfQty
    SELECT  [YourTable].Customer,  [YourTable].[Product]
    FROM [YourTable]
    GROUP BY  [YourTable].Customer,  [YourTable].[Product]
    PIVOT Format(ItemTagNUM.[TMDate], "yyyy mmm");

    EDIT - changed First to Sum.


    Build a little, test a little


    • Edited by QA Guy CommElec Thursday, February 18, 2016 6:36 PM
    • Marked as answer by Deon SA Wednesday, February 24, 2016 11:33 AM
    Thursday, February 18, 2016 6:15 PM
  • Hi Karl,

    Thanks. This took care of the data needed, but now there's another issue:

    I created the report based on this query. All works fine until I change the year from 2016 to 2015. Now I get errors from the report that the fields does not match or is invalid. This is due to the data that has changed in the query. When running 2016, there is data for Jan and Feb, but when I change it to 2015, there is data from Jan through Dec.

    How do I create the report based on the data in the query? Is there perhaps a way to create the report with VBA code?

    Thanks

    Deon

    Friday, February 19, 2016 6:42 AM
  • >>>How do I create the report based on the data in the query? Is there perhaps a way to create the report with VBA code?

    According to your description, you could create dynamic reports that are based on parameter crosstab queries. You can also create reports to match a dynaset that is returned by such a query. Dynamic reports allow your customized reports to show only the most recently modified data. This gets rid of the need for fixed column headings and empty columns.

    For more information, click here to refer about How to create a dynamic crosstab report in Access 2002

    Monday, February 22, 2016 6:59 AM
  • I created the report based on this query. All works fine until I change the year from 2016 to 2015. Now I get errors from the report that the fields does not match or is invalid. This is due to the data that has changed in the query. When running 2016, there is data for Jan and Feb, but when I change it to 2015, there is data from Jan through Dec.

    Post the modified query SQL.

    Build a little, test a little

    Wednesday, February 24, 2016 9:25 PM
  • When you make a Crosstab query and your Column Heading field can vary, you need to define the maximum possibilities in the Column Headings property or PIVOT of the SQL view.

    See below example.

    TRANSFORM Sum(IIf(IsNull([TotalPax])=True,0,[TotalPax])) AS SumOfSales
    SELECT UCase([Country]) AS Expr1, Sum(tblBookingDestinations3.TotalPax) AS SumOfTotalPax
    FROM tblBookingDestinations3
    WHERE (((tblBookingDestinations3.DepYr)=[Forms]![frmBookingDestinationsByMonth]![cboDepYR]))
    GROUP BY UCase([Country])
    PIVOT tblBookingDestinations3.DepMonth In ("1","2","3","4","5","6","7","8","9","10","11","12");


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.




    • Edited by DriveEV Wednesday, February 24, 2016 9:48 PM
    Wednesday, February 24, 2016 9:37 PM