Formatting a custom report

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

>>>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

Answer 5

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

Answer 6

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.