Solved Problems with crosstab (1 Viewer)

JLB1

New member
Local time
Today, 14:55
Joined
May 6, 2021
Messages
6
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?
 

JLB1

New member
Local time
Today, 14:55
Joined
May 6, 2021
Messages
6
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");
 

Attachments

  • Months With Data Zeros.png
    Months With Data Zeros.png
    9.4 KB · Views: 262
  • Zeros.PNG
    Zeros.PNG
    8.5 KB · Views: 258

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:55
Joined
Feb 19, 2002
Messages
42,976
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

New member
Local time
Today, 14:55
Joined
May 6, 2021
Messages
6
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

Super Moderator
Staff member
Local time
Today, 17:55
Joined
Feb 19, 2002
Messages
42,976
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.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:55
Joined
May 7, 2009
Messages
19,169
can you use SubQuery?
 

Attachments

  • horse.accdb
    392 KB · Views: 284

JLB1

New member
Local time
Today, 14:55
Joined
May 6, 2021
Messages
6
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.
 

Users who are viewing this thread

Top Bottom