Sum report field info (1 Viewer)

[gguy]

I have a report that is based on a query.

The report shows total applications per county, total records per county and total dollars.

The problem is that if I want to show only the total application per county I have to use a separate query and group by the app number. This works beatifully for a report that has all 16 counties and a total of all apps for the 16 counties.

But,

The user wants to be able choose 2 or 3 counties and show the same report for those counties.

So, I can get the report to show all the field for the chosen counties but the and I can show the total apps from the second query for the chosen counties. But I can't sum the total apps from the chosen counties from the query. It just errors.......I think because the 2nd query is not the active query.

So, my thinking is that I ought to be able to add the chosen counties app total right from the report since they are accurate. Is there a way to do this? Thanks, Greg

 

[vbaInet]

For this kind of situation it would be much clearer if you can post a stripped down version of your db indicating which report/query/form we should look at.

 

[gguy]

Let me try again,

Is there a way to show a total in the footer of a report that is based on values in the report and not from the underlying query?

Whether it has one record or twenty, I want the total to be for a column. Thanks,

 

[vbaInet]

It is possible but you would have to code it. If you're performing a calculation why not put it in the underlying recordsource and perform the usual SUM()?

 

[boblarson]

Let me try again,

Is there a way to show a total in the footer of a report that is based on values in the report and not from the underlying query?

Whether it has one record or twenty, I want the total to be for a column. Thanks,

You would put a text box on the Report FOOTER with this:

=Sum(Nz([YourFieldName],0))

Making sure that the text box is not named the same as any field (or else you'll get #NAME as an error).

 

[gguy]

Thanks Bob but I have been trying to do the sum() and it keeps erroring out on me. I didn't use the nz() function so I will try that.

As for going to the underlying recordsource the problem is that the values that need to be totaled are coming from an external query. The reason for this is that I don't need the record count but a count of unique applications.

where as there may be 30 records, when I do the group by application_number in my external query I get 6 apps. I need the 6 not the 30. When I try to sum() the external query or secondary query I get an error.

 

[boblarson]

Then you would want DSUM instead

 

[gguy]

I got the dlookup/dsum to work on the secondary query and it shows the correct totals in the detail area of the report.

When I put the dsum in the footer it show the total for the last county chosen.

If I remove the county criteria the dsum show a total for all counties.

There are 16 counties and if I choose 3 counties I want to show the total applications recieved from those three counties. Not the last county of the three chosen. Thanks,

 

[boblarson]

I got the dlookup/dsum to work on the secondary query and it shows the correct totals in the detail area of the report.

When I put the dsum in the footer it show the total for the last county chosen.

If I remove the county criteria the dsum show a total for all counties.

There are 16 counties and if I choose 3 counties I want to show the total applications recieved from those three counties. Not the last county of the three chosen. Thanks,

Okay, you don't use DSUM in the report footer. You just sum the field which you created in your query for Sum.

So, it would be

=Sum(Nz([FieldName],0))

as I said before. This is so that it ONLY adds up what you have on your report. So if the report is limited to 3 counties, then it should only show the sum for those counties PROVIDED that you have the sum for each county in your QUERY.

 

[gguy]

The name of the field is [text95] and I have =Sum(Nz([text95],0)) in the control source of a field in the footer.

What I get is a pop up when entering the form that say "Enter parameter value" for [text95] instead of totaling the 3 values for the counties chosen.

Yes, I just want a total for what is showing on the report. Thanks,

 

[vbaInet]

Bob I think he's trying to sum a textbox which performs calculation in it which was why I suggested he does the calculation in the underlying record source.

Or you have to add up in code in the section's Detail or Print event.

 

[boblarson]

Bob I think he's trying to sum a textbox which performs calculation in it which was why I suggested he does the calculation in the underlying record source.

That's why I Also said that. Access doesn't like referring to controls using aggregates on reports for some reason so I would suggest doing the calculation in the underlying query (as we have both suggested but have apparently been ignored) and then using the Sum in the footer would work fine.

 

[vbaInet]

That's why I Also said that. Access doesn't like referring to controls using aggregates on reports for some reason so I would suggest doing the calculation in the underlying query (as we have both suggested but have apparently been ignored) and then using the Sum in the footer would work fine.

You sure did. And it seems we're being ignored

 

[gguy]

Not ignored, rather, just my lack of experience.

I thought I was doing what you asked with the dsum on the secondary or non-linked query. That query gives me the totals for application per county. Now I may not be understanding or just thick headed but it seems my dsum will only give me a total for all records or the last record chosen. Not a total of whatever counties I choose of my 16 counties.

Now if we are talking about the linked query(active query) as the underlying record source then the problem is that I am already doing several things with it and any additional modification to that query would mess up other totals in the report.

So, I am a bit confused.

 

[vbaInet]

Show us two things:

1. The sql statement of your report's record source.
2. The calculation you're performing. Just copy and paste the exact expression from the control source

 

[boblarson]

If possible, upload a copy of your database. If necessary substitute some bogus data so we can see what's happening and it might get fixed a spot faster.

 

[gguy]

That took a bit of doing but there is the stripped database.

I have been calling NRDs counties, thought it would be less confusing.

On the report menu choose the sixth report "NBSP Summary by NRD"

By the way, all info in this database is public information.

On the list picker do "view all nrds" first to see the report. That one is good.

Go back and choose Litte Blue, Lower loup and Nemaha and then choose "view selection".

The # applications should be 282 but shows as 858. That is the problem I am trying to solve. Thanks,

 

[boblarson]

I only see 5 reports and there isn't any by the name you gave.

 

[boblarson]

I only see 5 reports and there isn't any by the name you gave.

Did you mean 6th QUERY? And then what report?

 

[gguy]

On the report menu choose the sixth report "NBSP Summary by NRD"


You should be looking at the "Buffer Strip Reports Menu"

Choose the 6th report, "NBSP Summary by NRD"

That will take you to a form where you can choose NRDs from the list.

Choose some NRDs and then click "view selection"

That will take you to the report that I am having problems with.

The problem is in the total for the "# Applications" column.

Thankls,