I have a table in an access 2010 dB that stores metrics that are reported monthly. I use multiple append queries that sum values or count records in other tables to populate the monthly metric table. I use a macro to run the individual queries and a form to collect the end date for the report month. I want to insert records with "0" values when the aggregate queries return no records.
An example aggregate query is
Code:
INSERT INTO NewMonthlyMetric ( MetricYear, [Metric Month],Slide, Chart, Metric, MetricValue, MetricEndDate )
SELECT DatePart("yyyy",[Forms]![MonthlyAppend]![EndDate]) AS [Metric Year], Month([Forms]![Monthly Append]![EndDate])AS [Metric Month], "CCB Actions" AS Slide, "Budget Bullets"AS Chart, "Budget Increase" AS Metric, Sum([Change RequestIntake].[Estimated Cost]) AS [SumOfEstimated Cost], [Forms]![MonthlyAppend]![EndDate] AS Expr1
FROM [Change Request Intake]
WHERE ((([Change Request Intake].[Change Request Status])Not Like "On Hold for Additional Information") AND (([Change RequestIntake].[For Admin Only]) Is Null) AND (([Change Request Intake].[CategoryBudget]) Is Not Null) AND (([Change Request Intake].[Decision FinalDate])>DateAdd('d',-DatePart('d',[Forms]![MonthlyAppend]![EndDate]),[Forms]![Monthly Append]![EndDate])) AND (([Change RequestIntake]![Date Submitted])<=[Forms]![Monthly Append]![EndDate]))
HAVING (((Sum([Change Request Intake].[EstimatedCost]))<0));
Is the best way to accomplish this to use if statements for each append query in the macro? I'm hoping there's an easier way since this would mean there would be two append queries for each aggregate query: one for the aggregate append and one for the "0" append.
Thanks
Paul