canonquiche
Registered User.
- Local time
- Yesterday, 21:11
- Joined
- Jan 5, 2016
- Messages
- 11
I have a crosstab query that retrieves the last 3 months of data and finds the average for that period.
However, the query returns many rows where the Current Total is zero or blank. How do I hide records for which the entire date range consists of zero/blank values (i.e., all three months are zero/blank)?
I tried "Is Not Null" and <>0 in the criteria for Current Total, but Access said the value field in a crosstab can't accept a criteria...
Here's the SQL:
However, the query returns many rows where the Current Total is zero or blank. How do I hide records for which the entire date range consists of zero/blank values (i.e., all three months are zero/blank)?
I tried "Is Not Null" and <>0 in the criteria for Current Total, but Access said the value field in a crosstab can't accept a criteria...
Here's the SQL:
Code:
TRANSFORM Sum([Current Total]) AS [SumOfCurrent Total]
SELECT [Category], [CLIN], [Description], Avg([Current Total]) AS [3-Month Average]
FROM query_FFP_Charges
WHERE (((([Invoice Month]) Between DateSerial(Year(Date()),Month(Date()),0) And DateSerial(Year(Date()),Month(Date())-3,1))
GROUP BY [Category], [CLIN], [Description]
PIVOT [Invoice Month];
Last edited: