How to Modify the Recordsource

Access 2002 Report

I  have a report that has multiple detail lines. I have it so that only the top line is visible (which is what I want) BUT when I go to sum, I am getting the total for all of them.

My report is bit more complex but here is an easier example:
Each product (PrA and PrB) has 3 items. The overall ship charge for PrAis 2.00, yet the detail shows 2.00 for each item, and the overall ship charge for PrB is 3.00, yet the detail shows 3.00 for each item.
When I go to total, I want the total to be 5.00, not 15.00.

PrA      Item    ShipChge
             1           2.00  (visible)
             2           2.00  (hidden)
             3           2.00  (hidden)

PrB      Item    ShipChge
             1           3.00  (visible)
             2           3.00  (hidden)
             3           3.00  (hidden)
Can anyone explain to me how to modify the report so I only get the sum of the first line of each item?
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Ultimately I am with fyed.
The source data needs to be fixed.

However, I am very well aware that sometimes you must "Build with the Bricks you are given"

This being said, any workaround that circumvents standard database theory will be filled with caveats
(You must have ..., It will only work if...)
Just be aware of this, this "Requirement" will always require a workaround like this
Then when/if the data is fixed (or worse, modified more) this workaround may no longer work and even may end up causing more problems than it solves...

For example, in your report, how are you determining the "First" value in the group?
If they are all the same, then it is easier to get the "last" value.

Here we go...
Instead of getting the sum in the group footer( =sum(YourField) ), just get the value ( =YourField )
(This will, in effect, get the "Last Value", but in your case, since you state that they are all the same, it is the same as getting the first value)

Then copy this group footer control and rename it to something like "txtYourFieldRunSum"
Then set the running sum property of this copied control to: Over All
Set the visible property of this control to: No
Then in the Grand Total (Report Footer) simply have a control that uses this copied textbox as its controlsource

Sample attached

(Obviously You will have to "Hide" all but the first record in the group by whatever technique you employed previously)

Let us know what you think of this approach.

Dale FyeCommented:
The fact that you are you getting multiple items implies that you probably have one or more fields in your query that are not needed for this particular report.

What is the SQL string that you have for the report?
wgraphicsAuthor Commented:
Hi fyed,
Not that this means anything really, but the query is quite a monster with custom tables, tables from weekly downloads from outside sources and tables from company software.
I'll do my best at explaining, bear with me-
The orders (Products)have multiple line items that the system adds charges to (each line) and we are responsible for making it show/total only the first line. When the data gets dumped down into from the web, it comes in with the shipping charges attached to each line.

So I guess what I am saying is- by the time we get it- the shipping charges are already attached to each line item, and whether we like it or not, have to figure out how to show the charge only once.

My above example showed only 3 lines but some of our orders have 10 lines, some only 1 line, 6 lines, it varies depending on the order.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

wgraphicsAuthor Commented:
Your approach is working great- except my grand total keep showing up as 0. (Not sure if this is relevant, but the very last order happens to have a shipping charge of zero).
The other day I did a similar runsum on a different column, which was successful. I attempted it on this shipping charge column and got the same result- grand total of zero.
I do have running sum set to Overall.
I will continue to work on this 0.00 grand total issue.
wgraphicsAuthor Commented:
Does runsum resets at 0?
If so, any suggestions for a workaround for when that happens?
Thank you!
wgraphicsAuthor Commented:
Jeff and fyed thanks for your time. The runsum function works great on all my other columns, but it just doesn't want to work with this summary.
The summary you gave as an example looks good. For some reason though, those ship charges do not want to runsum and I cannot get a total at the bottom either.
I am going to close out this question and perhaps I will revisit again next week. I am out of the office the rest of the week. Anyway, as always, your help is very much appreciated.
Jeffrey CoachmanMIS LiasonCommented:
Post a sample of your database and I can take a look
wgraphicsAuthor Commented:
Thanks Jeff- I will try to do that. Have meetings and such so may not get to it today. Thanks!
wgraphicsAuthor Commented:
Ok I was able to do it quick. I just added a sample to the mdb you made previous. Just want one shipping charge per orderID. For example order 2493-1809 has 46.9 ten times- only want to see it once and only want to add it once in a runsum. Thanks-
Jeffrey CoachmanMIS LiasonCommented:
<Just want one shipping charge per orderID.>
Then literally move both fields from the detail section into the group header.


Make a query like this:

SELECT EOM_LineItemReport.[Order ID] AS [EOM_LineItemReport_Order ID], EOM_OrdersImport.Shipping
FROM EOM_LineItemReport INNER JOIN EOM_OrdersImport ON EOM_LineItemReport.[Order ID] = EOM_OrdersImport.[Order ID]
GROUP BY EOM_LineItemReport.[Order ID], EOM_OrdersImport.Shipping;

... and just use the wizard to create a basic report (No Grouping needed)

This will all depend on what you want to do with Order 2493-1900, which has a value of 0 and 16.49
(This is not consistent with your original post that *All* vales will be the same)
wgraphicsAuthor Commented:
Hi Jeff,
My fault- Order 2493-1899 and 2493-1900 are "new" orders that should not be in there. I should have filtered it to show "Completed" orders only. Once these orders are fulfilled, the shipping charges willl turn from 0 to a cost.

I am able to get the one shipping charge per order (from your instructions above in comment #3). What I cannot get it to do though, is runsum and total at the bottom. When I set it up, the runsum function doesn't appear to work and I get an incorrect total as well.

I have 2 other columns in the original report, though, that I am able to runsum and total fine.

Can you think of anything that is prohibiting me from runsumming and totaling this column? I attached the file again, with an example of what it looks like when I try to runsum/total it.
Thanks again, Meg
Jeffrey CoachmanMIS LiasonCommented:
1. Now can you see why we all stated in the beginning that source data like this is a nightmare to work with...

2. You have a lot of fields in your table defined as "Text" when they should be defined explicitly as other types.
This is why your running sums are not working... The Shipping field is defined as *Text* in the source table(s), when it should be defines as "Currency"

So if you fix this basic flaw, and use the SQL I posted, this all becomes simple, ...and only  =Sum(Shipping) is need to get the Grand Total.

But you *Really* need to go back and re-evaluate your table design here.
There are a lot of other issues there that that will cause you more frustration in the future if left un-addressed...

Sample attached


wgraphicsAuthor Commented:
Jeff THANK YOU! Yes that worked.
I appreciate the time you took and I very much appreciate your input and expertise regarding the table design. I look into it and will be sure to mention this to those who are requesting the reports. Have a good week. Meg
Jeffrey CoachmanMIS LiasonCommented:

No sweat,...
I enjoy working with people who can work independently.

Just to recap what was done....:

The query does the grouping...
This means that in the report, each "Group" is now a "Record".
Thus no Grouping was needed in the report.

With the fields set to the correct datatype, it is a simple matter to get the running sum, or just the grand total.

But again, the kicker here is that:
1. The other datatypes must also be corrected (ex.: Date fields will also cause you grief if they are not true dates)
2. If the data is ever "Normalized" the report and query I used here will be irrelevant, and you will have to recreate the report, grouping, sums, Totals, etc form scratch with the new data.

Some final notes,
1.  ...technically, the running sum (and the Grand total) is based on the "Field Name", not the "control name" as you were trying to use.
2. Investigate adopting a consistent "Naming convention":

Glad I was able to help


wgraphicsAuthor Commented:
haha yes, one woman show here...

"The query does the grouping...This means that in the report, each "Group" is now a "Record".
Thus no Grouping was needed in the report." Ah, I see....thanks for the explanation.

re: dates- yes I changed them when I changed the currency. I am going to go through more tables tomorrow and double check datatypes.

It is quite a messy monster that is piece mealed together over the years and I am the lucky one that gets to "take it over".

Thanks for the links. I hope to read them in the AM with my morning coffee- now that I can stop stressing about that ship calc (for the time being anyway!) Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.