Solved Problems with crosstab

[JLB1]

Hi. I have created a monthly crosstab query that has column headings defined for the current year, and what I need is to show zeros where there is no data for the previous months, but null/blank values for future months and I'm not sure this is possible. Any suggestions?

 

[pbaldy]

See if this helps:

Microsoft Access tips: Crosstab query tips

Crosstab query tips for Microsoft Access

allenbrowne.com

 

[JLB1]

Thank you for your reply.
Unfortunately, the methods in that article (and everything else I could find) replaces all null values with zeros, even for future months with no data. I've attached two screenshots, "Zeros" shows the results using a formula IIf(IsNull(Count([WIN])),0,Count([WIN]))) for the value, and "Months With Data Zeros" is how I need it to look.

SQL:
TRANSFORM IIf(IsNull(Count([WIN])),0,Count([WIN])) AS Expr1
SELECT [Hollywood Park Results].[Horse Name], Count([Hollywood Park Results].WIN) AS [Total Of WIN]
FROM [Hollywood Park Results]
GROUP BY [Hollywood Park Results].[Horse Name]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[JLB1]

This is actually just sample data, the real data is counting a text field, not a number. But the problem is everything I try either changes every null value to a zero or none of them. I can't seem to get to where future months are null, but null values in the current/previous months (where there is data) are zeros. I have the date criteria set to pull from Jan 1 to the last day of the previous month of the current year - Between DateValue("01/01/" & Year(Date())) And DateSerial(Year(Date()),Month(Date()),0)

 

[JLB1]

Thank you, I'll give that a try.

 

[arnelgp]

can you use SubQuery?

 

[JLB1]

I think arnelgp's subquery idea will work, but it may run pretty slow. I think I might just dump the query results into a table and run some code to remove the uneeded zeros and link my Excel template to the table. =)

Thank you for all the help.