Microsoft Access 2013 How to get Total Requests on Report Footer

I have a report that has a summary of for each request of time spent. All is working great. On the organization header I have the total for that organization. Works. The text box is named [totofreq]. In my report footer I tried to sum([totofreq] but when I run the report I get the Enter Parameter Value for the totofreq. Can someone help me this is the last part of a big report and it should be simple. Thanks in advance for the help.
marlind605Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Ron
marlind605Author Commented:
That name of that field is totalrequests. It totals each header of the organization perfect.
IrogSintaCommented:
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:

=Sum([Requests])

Ron
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

marlind605Author Commented:
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?
IrogSintaCommented:
What determines the requests from the records?  Perhaps if you post the SQL for the recordsource of the report, this will be clearer.
marlind605Author Commented:
I will see but it may be much too big. Let me look at it. Thanks.
IrogSintaCommented:
Just in case you misunderstood, I'm not asking for you to post the whole database.  Just the recordsource of the report itself.

Ron
Gustav BrockCIOCommented:
> 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
marlind605Author Commented:
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.
Gustav BrockCIOCommented:
Running sum? I think it should just be:

    =Sum([YourField])

/gustav
marlind605Author Commented:
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?
Gustav BrockCIOCommented:
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
marlind605Author Commented:
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.
Gustav BrockCIOCommented:
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
marlind605Author Commented:
I am trying to get a generic database that shows my problem ready to post. Hope to have it ready this afternoon.
marlind605Author Commented:
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
Gustav BrockCIOCommented:
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

Open in new window

/gustav
TotalExample.accdb
IrogSintaCommented:
Here's what I did. I added an invisible textbox in the JOBID header with a value of 1 and set its RunningSum to Over All.  I then set your total below to this textbox.

Ron
TotalExample.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Strange. I think I tried that but it failed. Well, I probably did it a bit differently ...

/gustav
marlind605Author Commented:
I did that too and it failed earlier. Trying it on actual db now well let you know what works. Thanks for the replies.
marlind605Author Commented:
/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.
Gustav BrockCIOCommented:
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
marlind605Author Commented:
IrogSinta Thanks. I appreciate all the input. I will be posting another question shortly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.