Microsoft Access 2013 How to get Total Requests on Report Footer

I'm assuming totofreq is not bound to your recordsource, so where does totofreq in you header get it's value?

Ron

That name of that field is totalrequests. It totals each header of the organization perfect.

So do you have a field in the recordsource for each individual request (not the organization total)?  If it is called [Requests] for example, then the textbox in both your group header and report footer should have this:


Ron

Each group may have multiple requests. Each request may have multiple records. The report I am working on for the year has 1771 individual entries. The total requests is 292. I have not been able to get the total figure 292 to come out at the report footer.  I did an unbound text box =-1 for each request number and I can get the total I want but I am not able to get the total at the report footer.  Does that help?

What determines the requests from the records?  Perhaps if you post the SQL for the recordsource of the report, this will be clearer.

I will see but it may be much too big. Let me look at it. Thanks.

Just in case you misunderstood, I'm not asking for you to post the whole database.  Just the recordsource of the report itself.

Ron

> On the organization header I have the total for that organization. Works.

I guess you are grouping on Organization.

> .. in my report footer I tried to sum([totofreq]

I guess you wish to sum for all organizations.
That should be easy.
Create a textbox, name it, say, TotalFrequency, and use the same expression as controlsource as for totofreq.

/gustav

Gustav Brock Yes I am grouping on Organization in the header. I just turned on the footer and put the same textbox and it gives me the total for the organization. Perfect Works. I put the same thing in the report footer and I get the number 1 even when I do a runningsum over all or none.  I think it should be as easy as you say but something is going on.

Running sum? I think it should just be:

    =Sum([YourField])

/gustav

It totals up all the records. 1064. Each organization has multiple records. I am getting the total time spent for each organization request. I am checking different fields and still getting the total or 1. Is there a way Can I insert the number via vba?

Well, the report can by itself calculate a sum, so I guess something else is going on.

What is your control source for the textbox?
How do you calculate the time spent?

/gustav

I do a sum of all the time. Each time has a WorkCode. I use an if statement to place it in the proper column. As for the text feel I run several queries to total each organization for that report. I would have to work on cleaning up the sql so that it would be generic but that would be work too. I thought I could say Max([linenumber]) would work but it didn't I use=1 in a text box and it is totaling fine. If I could just get that to come over to the report footer but that is when I get the enter parameter prompt.

If you use =Someexpression([MyField],[OtherField]) for the Group, you will have to use =Sum(Someexpression([MyField],[OtherField])) for the total. And of course, you must sum numbers, not text.

/gustav

I am trying to get a generic database that shows my problem ready to post. Hope to have it ready this afternoon.

Here is the generic database. Total for Rank Find = . Total for trans fax = 27 total for unitouch = 1.It is counting each time record not the total. Any help would be appreciated. Sorry for the delay to post it had to get time to clean up data and make it generic. Thanks.
TotalExample.accdb

I think the easiest method is make the textbox unbound and run a piece of code in the reportfooter:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

    Dim rs      As DAO.Recordset
    Dim SQL     As String
    
    SQL = "Select Count(*) From (Select Distinct JOBID From " & Me.RecordSource & ");"
    Set rs = CurrentDb.OpenRecordset(SQL)
    If rs.RecordCount = 1 Then
        Me!TextTotalRequests.Value = rs(0).Value
    End If
    rs.Close
    
    Set rs = Nothing

End Sub

Select allOpen in new window

/gustav
TotalExample.accdb

Strange. I think I tried that but it failed. Well, I probably did it a bit differently ...

/gustav

I did that too and it failed earlier. Trying it on actual db now well let you know what works. Thanks for the replies.

/gustav when I tried your solution on the real db I got a Run-time error 3061 Too few parameters. Expected 2. It worked before I put your statement. I would like to know what causes this. I do use forms for the user to put the two dates is there something I need to put in for your function to work?  

I am checking IraqSinta solution too.

I would use Ron's method.

For the function to work, it sounds like you are using another query. If so, you must of course supply possible parameters.

/gustav

IrogSinta Thanks. I appreciate all the input. I will be posting another question shortly.