Sum Trouble

[helpmeplease]

I am trying to calculate information in my report for employee training. The
report is based on a query that askes for a date range. All training is
displayed for each employee within that range and if it was a mandatory
training outside of the range. The report needs to calculate the completion
hours ONLY if the training occurred during the date range and ignore the
mandatory trainings outside of that time.

Thanks

 

[John W. Vinson]

I am trying to calculate information in my report for employee training. The
report is based on a query that askes for a date range. All training is
displayed for each employee within that range and if it was a mandatory
training outside of the range. The report needs to calculate the completion
hours ONLY if the training occurred during the date range and ignore the
mandatory trainings outside of that time.

Thanks

Correct the error in the query.

If you would like help doing so, please open the query in SQL view and post
the SQL text here. If the fieldnames aren't selfexplanatory please explain
them; it might also help to include some sample data and show what is being
included that you don't want included or vice versa.

 

[KARL DEWEY]

You posed no question for someone to answer.

Post your table structure with field names and datatype. Post sample data.

 

[John Spencer]

I would probably add a calculated field to the query to flag the hours or just
show the hours that should be totaled and then use that in the report to sum
the relevant records.

It would help give you a more definitive answer if you posted the SQL text of
the query you are using to generate the data for the report.

The calculated field could be
AddThis: IIF([TrainingDate] Between #2009-01-01# and
#2009-03-31#,[CompletionHours], Null)

You could use a similar expression in your report and not bother to create the
calculated field. In this case, I would guess that it will be easier to get
the start and end dates in the query.

If you are using parameter prompts in the query, the calculated field would
look like
AddThis: IIF([TrainingDate] Between [Start Date] and [End
Date],[CompletionHours], Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County