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");

 

[Pat Hartman]

In the table, does Win have a default of 0 or null? Try making the default null. Then rather than using Count, try Sum. That will add the wins (ones)

Otherwise, you can create a query and base the crosstab on the query. The query can change all the future 0's to null. Then use the new query in the crosstab rather than the table.

 

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

 

[Pat Hartman]

FYI, Count() and dCount() count the number of non-null values in the specified field. Using * instead gives you a domain count instead.

If you do the count in a query. You can use another query to change the Future 0 counts to null. Then use that query as the source for the crosstab and instead of count, use first() as the function.

 

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