Summary of Report - Count? DCount? (1 Viewer)

Hi, everyone.

I'm working on creating a report that has a pretty detailed summary at the report footer.

In the detail section, all the appellate cases for a certain month (inputted by user) are displayed, including the type/level of case, the issue of the case, the decision (outcome), how long from the filing of the appeal to the decision, and date information.

What I'd like to have in the report footer is a count:
How many total cases?
How many of each decision type: (A, B, C, D, F, G, or H)?
How many of each issue code? (There are lots of these...)
How many cases filed by Employer? Employee? Both?
How many cases at a level 1? 2? 3? 4?
How many at a level 2 had decision A? decision D?
How many took between 0-30 days? 31-45? 46-90? >90?

Anyway, all this information is in the report detail section, but I can't figure out how to count all this stuff in one report. I've looked up DCount in help, but I don't think I'm using it correctly.

Any help is, as always, greatly appreciated.

-Sara

Sara,

One way to do this is to use the OnFormat event for the report
footer. You can make unbound textboxes, place them in the
footer, and populate them with the results from various
DLookup calls.

Another way would be to put your unbound textboxes in the
report footer, init them to 0, and use the Detail_OnPrint event
to increment them.

hth,
Wayne

Do a totals query for each set of data that you want to count. For example:

Select DecisionType, Count(*) As DecisionCount
From YourTable
Group By DecisionType;

Then make subreports for each count group and add them to the report footer. You can make the subreports multi-columned if that makes them look better or make crosstabs of some if there won't be too many columns.

The DSum() method is a lot more work for this type of function plus it is extremely inefficient. For example, if you had 10 codes and you wanted totals for them, you could do one totals query or 10 DSum()'s. Keep in mind that each DSum() needs to run the same query. The DSum() method also requires changes to the report if the set of code values changes whereas the totals query method does not.

Thanks, both of you, for your replies!

Pat,

I'm working on your suggestion of creating subforms, but I think I'm getting problems because of parameters I've set up in the query the main form is based on. Let me try to better explain:

There are a number of Appeals Chairmen (and Chairwomen). Right now there are 8. The main report runs for one of these people. (I currently have it running from a form, where the Chairperson's name is selected, and then a command button brings up the report preview pane.)

Also, the user is prompted for a year of report, then a month of report, from which the query pulls its results. (The month and year are based on the Decision Date of the appeal.)

The basic purpose of the report is to see how many cases each chairman has in a given month, and how timely they complete the appeals process. The report footer should also show how many cases they had total, and then totals as I described in my initial post.

The problem is... if I close the form the report gets its information from, I get prompted for [forms]![frmChairmen]![List0]. Also, I get prompted for the month and year at least four or five times, just for the first subform I'm trying to add.

I'm using the subform wizard to create a subform from a query.

My query for the subform is based on the same query the form was made from. (This may be my problem? But if I don't do this, then it gives me totals from the whole db, which don't match up to the report...)

I can post more detail if needed... I never know what's enough, or what's too much...

Anyway, any thoughts? From anyone?

Thanks again.

--Sara

PS - Don't know if this makes a difference, but when I run the report without the subreport, it only prompts me once, and when I run the Subreport without the report, it too only prompts me once.

Solution or Cheap Work-Around?

After doing a lot of toying with the thing, I finally tried this:

1) I added ReportMonth and ReportYear fields to my form where the report was run.

2) I changed the criteria in the query to [forms]![frmChairmen]![ReportMonth] and ...[ReportYear]

3) I changed my form to not be a pop-up, so it fades into the background.

So now it doesn't ask me a million times for the dates, but is it bad that it ever did and should I still focus on fixing that?

Anyway, that's all I wanted to know. Thanks again.

--Sara

Reports run their recordsource queries multiple times. So, if you close the form that you are referencing to get your criteria, Access needs to keep asking you for the information since it can't get it from a closed report. So, you happened on the correct solution of keeping the report open.

So, do u make it already, Sara? Seem i look the same problem as u but i can't get help.... so if u know how pls tell me ... Thanks!!!!

My problem in this link:-
http://www.access-programmers.co.uk/forums/showthread.php?goto=newpost&t=114057