Summarize a query into a report

[RAM73]

I need to summarize a query into a report. I have a query that lists locations that had certain issues on a given day. IE Down, files missing etc... I need to generate a report that show for instance 5 sites were down, 3 sites were missing files, 7 sites lost power and so on. Is there any way to do this? Thanks in advance for any help.

 

[randysmid]

Hi,
Yes, this can be done easily. Create the report in the usual way, using the query as the recordsource. Then, set up Sorting and Grouping for your issues field. Add a Group Footer, and drag the issue into it. Add a text box, and then set the control source to "Count([issues])". Then, remove the detail section, so that all of the individual records will not show.

As a strategy, you may want to create a form where the user can enter the date to be checked. This will be used as the criteria for your date field within the query.

You can also optionally set the report to display "Show Detail", or "Show Summary Only" (with a listbox or radio button control). With this last option, you can change the Open Event of the report to "turn-off" (or "turn-on) the visibility of the Detail section. Obviously, if this is a serious option for you, please don't delete the detail section as I mentioned in the opening paragraph. Here is a sample of the code (in the Open Event of the report) that will make it happen:
If Forms!frmIssues!lstOption = "Show Detail" then
Me.Detail.Visible = True
else
Me.Detail.Visible = False
End if


HTH,
Randy Smith
California Teachers Association