Re: Report Group Footer not displaying the total that I want

One option is to use the built in running sum property (data tab) of a
textbox when it is on a report. To get a count, you can set the Control
Source of the textbox to =1 and set its Running Sum property to Over All or
Over Group. If you want to count only if the value of another field is a
certain value, you can use an IIf statement instead of =1, such as
=IIf([txtOtherTextbox]="Hello", 1, 0). This will count the number of records
that have Hello in txtOtherTextbox. In the group footer, place a textbox
with its Control Source pointing to the summing textbox in the detail
section

=txtMySummingTextbox

--
Wayne Morgan
MS Access MVP


"SueB" <slbrick@xxxxxxxxxxx> wrote in message
news:lNpHe.3$sE.222@xxxxxxxxxxxxxxxxxx
> Greetings. I have a report based on the following query (hang in there
> .. it's quite long):
>
> SELECT Year([tblEvents].[eventstartdt]) AS Yr, tblEvents.eventID,
> tblEvents.eventname, tblEvents.eventhost, tblEvents.eventcity,
> tblEvents.eventstate, tblEvents.eventstartdt, tblEvents.eventenddt,
> tblTrials.trialnbr, tblTrials.trialdt, tblTrialClass.trialclassID,
> tblClasses.class, tblScores.score, tblScores.level, [tblPeople].[fname]
> & (" "+[tblPeople].[midinit]+".") & " " & [tblPeople].[lname] & ("
> "+[tblPeople].[suffix]+".") & " (" & [tblPeople].[city] & ", " &
> [tblPeople].[state] & ")" AS judge, tblScores.qualified
> FROM (tblEvents INNER JOIN tblTrials ON tblEvents.eventID =
> tblTrials.eventID) INNER JOIN (((tblPeople INNER JOIN tblJudges ON
> tblPeople.peopleID = tblJudges.peopleID) INNER JOIN (tblClasses INNER
> JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) ON
> tblJudges.judgeID = tblTrialClass.judgeID) INNER JOIN tblScores ON
> tblTrialClass.trialclassID = tblScores.trialclassID) ON
> tblTrials.trialID = tblTrialClass.trialID
> ORDER BY Year([tblEvents].[eventstartdt]), tblEvents.eventstartdt,
> tblTrials.trialnbr, tblClasses.classID, tblScores.level;
>
>
> The report is sorted (ascending) and grouped by:
> Yr
> eventstartdt
> eventname
> trialnbr
> class
>
> There can be many events in a yr
> there can be many trials in an event
> there can be three classes in a trial
>
> I want to display the following in the Yr Footer (year end totals):
>
> # of Events
> # of Trials (total)
> Avg # of Trials in an Event
> Max # of Trials in an Event
>
> The problem: each record in the recordset is basically a "score" that
> was recorded for a dog competing in a trial which is in an event. So
> there are many records with the same eventname/eventID. For my test data
> the number of records is 32. When I used =Count([eventID]) for the
> ControlSource of the textbox for "# of Events" (in the Yr Footer) the
> result was 32 instead of 2 (there are 2 events in my test data).
>
> What I really want is something like =Count(distinct [eventID]) but that
> is not a valid construct. Any ideas how to get the results I want in
> this report? Or do I need a different report (i.e.; a different query)?
>
> Thanks.
>
> Regards,
> SueB
>
> *** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages

  • Re: Forcing Totals into Header
    ... When you create a textbox on the report, look at the data tab under the ... There is a setting there called Running Sum. ...
    (microsoft.public.access.reports)
  • RE: Color Code Records In A Report
    ... Create a new query. ... Click on View> Sorting and Grouping in report design. ... Drag the OrderDate field from the Field List, and drop it into the OrderDate ... Rename the CompanyName textbox to txtCompanyName ...
    (microsoft.public.access.gettingstarted)
  • Can DTS do this?
    ... I have a report which I have to complete every quarter. ... in Excel and is called, ... had a training course in Business Practices, ... FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN ...
    (microsoft.public.sqlserver.dts)
  • Dynamic Stored Procedure
    ... I am trying to build a report application for reporting off of SQL Server. ... FROM Events E INNER JOIN CliCore.dbo.tblClients CC ON E.RegNo = CC.RegNo ... Dim strRecordSource As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access VBA Equivalent to Excel VBA
    ... textbox you want to modify. ... With your report in design view, double-click on the grey section header ... Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) ...
    (microsoft.public.access.modulesdaovba)