dkmoreland
Registered User.
- Local time
- Today, 12:30
- Joined
- Dec 6, 2017
- Messages
- 129
I am trying to create a report that shows monthly totals and YTD totals. I also need to show monthly totals of jobs that were not approved.
Here's initial query:
From here, I am using a crosstab query to give me my monthly breakdown:
That all works fine on the initial report. I need to add a supreport that gives monthly and YTD totals on records in the initial query where Approved = "no". Here's my query for that:
Here's the query that counts the number of records that were not approved:
I said all of that to ask this question - the subreport data does not have data for every month in the year - for example, there are no records for December. I need to leave a place for December data for next year - right now I'm getting an error that the field doesn't exist. It doesn't, since it is a calculated field that is returning no value.
Any advice on the best way to handle this? I need it to just show zero for the months where there is no value returned from the calculation.
I appreciate and all insight. The attached screen shots should provide a visual illustration of what I am doing.
Here's initial query:
Code:
SELECT [Carrier Release].[Job Number], [Carrier Release].Supplier, [Carrier Release].[Date Eval], [Carrier Release].Approved, [Carrier Release].[NCR Num], Year([Carrier Release].[Date Eval]) AS [year], Format([Date Eval],"mmm") AS [Month]
FROM [Carrier Release];
From here, I am using a crosstab query to give me my monthly breakdown:
Code:
TRANSFORM Count([Carrier Release Query].[Job Number]) AS [CountOfJob Number]
SELECT [Carrier Release Query].Supplier, Count([Carrier Release Query].[Job Number]) AS Total
FROM [Carrier Release Query]
GROUP BY [Carrier Release Query].Supplier
PIVOT [Carrier Release Query].Month;
That all works fine on the initial report. I need to add a supreport that gives monthly and YTD totals on records in the initial query where Approved = "no". Here's my query for that:
Code:
SELECT [Carrier Release Query].[Job Number], [Carrier Release Query].[Date Eval], [Carrier Release Query].Supplier, [Carrier Release Query].Month, [Carrier Release Query].Approved
FROM [Carrier Release Query]
WHERE ((([Carrier Release Query].Approved)="no"));
Here's the query that counts the number of records that were not approved:
Code:
SELECT Count([Carrier jobs with NCRs].[Approved]) AS CountOfApproved, [Carrier jobs with NCRs].[Supplier], [Carrier jobs with NCRs].[Month]
FROM [Carrier jobs with NCRs]
GROUP BY [Carrier jobs with NCRs].[Supplier], [Carrier jobs with NCRs].[Month];
I said all of that to ask this question - the subreport data does not have data for every month in the year - for example, there are no records for December. I need to leave a place for December data for next year - right now I'm getting an error that the field doesn't exist. It doesn't, since it is a calculated field that is returning no value.
Any advice on the best way to handle this? I need it to just show zero for the months where there is no value returned from the calculation.
I appreciate and all insight. The attached screen shots should provide a visual illustration of what I am doing.