How to Put Criteria in Crosstab Query Value Field

[RAJESHKUMAR R]

Dear Friends,

In the attached database I have a Crosstab Query Named as “Result”!



Right Now, it is giving a complete list of items. Instead, I would want result table to display, where filed value is Not Equal to Zero (i.e. Positive Value or Negative Value).

And, where the field value is blank or Zero respective PN or Month should not be appeared in the Result Table!

Kindly help me with the same!

Thanks & Regards,
Rajeshkumar R

[isladogs]

Create an aggregate query where you group by product and YrMo and do a sum of Qty with criteria <>0.
Save it and then create your crosstab based on that

Note that the order will be alphabetical as YrMo is a text string. This means Apr-18 will be the first column
There are ways around that if you want the data in calendar month order
You can't specify column headers unless you are happy for all columns to appear even if blank.
Another solution is to use a numerical version of YrMo e.g Apr-18=1804.

[June7]

Continued from https://www.accessforums.net/showthread.php?t=70538

Suggestion was to calculate YYYY-MM, not MMM-YY.

Can't exclude PN from listing in CROSSTAB if it has data for any date. A month will not have a column if there is no PN for that date. The nature of CROSSTAB is to have blank cells. The zeros in the CROSSTAB are the result of calculating records with positive and negative values. The net result is 0. It is real data.

Consider all-in-one sql:

TRANSFORM First(Query1.SumOfQty) AS FirstOfSumOfQty
SELECT Query1.Product
FROM (SELECT Q1.Product, Q1.YrMo, Sum(Q1.Qty) AS SumOfQty
FROM (SELECT Product, Qty, CRD, Format([CRD], "MMM-YY") AS YrMo, "FC" AS Cat FROM Forecast
UNION ALL SELECT Product, Qty*-1, CRD, Format([CRD], "MMM-YY") , "FO" FROM Firmorder) AS Q1
GROUP BY Q1.Product, Q1.YrMo
HAVING (((Sum(Q1.Qty))<>0))) AS
Query1
GROUP BY Query1.Product
PIVOT Query1.YrMo;