Getting a total of all DSum field results using =SUM()? 2

[Stevehewitt]

Hey.

Firstly, thanks to TonyJollans for getting this DSum to work on the report.

There is a DSum for the Invoice Value for each sale on the report. I have been asked to put a Invoice Value Total for the report, which should be in the report footer.

I was planning to use the =SUM() command but that doesn't work as the fields that I need adding up are not based on a table or query, but a calculated field (DSum) on the report.

Anyone know how to get around this?

Thanks,


Steve.

 

[scriverb]

Hi Steve. There are a couple of ways to handle this situation and you can choose which is easiest for you.

1. In your report you are experienced a common problem where you have performed a report footer DSum on a Detail Section numeric control. This rolls up the sales value field in the Detail Section to an Invoice Total for Sales very nicely. The limitation here is that you cannot use the Calculated Control from the Invoice Footer to rollup into a Report Total because it is a "Calculated" control. You can however use the original Detail Section Sales control to rollup to the Report Footer section. Just use the following as the Control Source in your Report Footer to get your fiture:

=DSum([DetailSectionSalesControlName])

2. You can modify your RecordSource to total up the Sales figures for the report by creating a new query that takes in as its RecordSource your original query. Perform a totals function on the query and select GroupBy for all fields except the Sales figure. Select Sum for the Sales column. Save this query. Now use this query as your Record Source for the report. The column SumOfSales or whatever you want to call it in your query can then be used in the Report Footer as the Control Source and it should be the total of all the Detail Section Sales control figures.

Hopefully, these techniques can help you with your project. If you have other questions please post back.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dhookom]

You can't use:
=DSum([a_control])
I don't recall the thread were you guided to use DSum() in a report but this is rarely if ever required. Bob's second solution is what I generally apply. Calculating the sums in your report's record source is probably the most efficient and reliable.

Another method is to create another text box adjacent to your current DSum() and set its control source to
=[TextBoxContainingTheDsum}
Set its Running Sum to Over All.
This is making lots of assumptions regarding your report.

Duane
MS Access MVP

 

[Stevehewitt]

Hi Bob,

Thanks for the reply.

Im really sorry, but I'm still quite a newbie to Access and VBA. I've given it a go, and looked it up in the help. I don't really understand what you have said - Sorry! Not quite sure on the terminology that you have used.

You say in point 1 (which sounds the easiest!) about a control source for the report footer. Please excuse my ignorance, but I can't find it!!! I did try the expression you used in your post

DSum=([TotalInvoiceValue])

as the control source for the text box in the footer but Access wouldn't let me get as far as even running the report!!! Error message said that it was the wrong number of expressions.

Any chance that you could elaborate?

Thanks,


Steve.

P.S. BTW, have you seen the thread about you in one of the forums? I think it was the Ethics forum about your absence!

 

[scriverb]

I have to apologize here but I meant for the control to use the Sum Function rather than DSum. dhookom is correct in that the DSum should not be used.

=Sum([DetailSectionSalesControlName])


Hopefully, this will work for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Steve, I just noticed the following in your last post:

P.S. BTW, have you seen the thread about you in one of the forums? I think it was the Ethics forum about your absence!

No, I didn't notice it but if you could direct me to the thread# I would appreciate it. Hope it wasn't anything to harsh.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Stevehewitt]

Hey Guys,

scriverb

No, it was a request for your presence as you haven't been posting recently! I'll dig it up in a second. Watch this space.

scriverb and dhookom

Sorry, I didn't explain it very well!

This is the problem, the control source is a DSum as the data I need for this field is a total of an order which is made up from multiple records in a different table, linked together using an order number. Its definatly the right way to go for what I need!

I think this is why the =Sum() won't work. I have used it on identical reports before and have got it to work fine as they were all based on real fields - not calculated ones. I tried =Sum() when you suggested the DSum() method.

Could either of you explain point 2 - I just don't understand?!

Thanks,


Steve.


Steve Hewitt

 

[Stevehewitt]

Stupid signature!

I've looked for it in the advanced search and I can't find it anymore! I guess TT management have taken it off the site. Sorry.
I think people in that thread were just saying that you haven't been around for a while!

 

[scriverb]

The Sum function if used with the unclaculated control from the Detail Section will give you a total Sum of all of the Detail Controls for the report. I believe this is what you wanted. You couldn't get it to work becuase you were trying to Sum or DSum the Group Footer control which was an already calculated control.

If I am not explaining this correctly or I am missing the point please post back again with more info.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Steve:

Stupid signature!

Is this in reference to my sig?

As far as the absense goes I have been away off an on recently with my family to help care for my ailing father-in-law in the VA hospice in Sarasota, FL. Not much time left. Made the trip twice and may go again soon. Have had to sacrifice TT, work, fun, etc for this very important activity.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Stevehewitt]

I'm very sorry to hear that. The thread was about the fact that you were missed. My appologies.

The signature was about mine. The post above has my name twice!!

The actual field in the detail section of the report is generated through a DSum function (long story about why, but its what I need unfortunatly). In the footer of the report I need a total of all of the InvoiceValues which is the field created using a DSum. The problem is that

SUM

doesn't work with the name of the text box, only the control source - which stuffs that one right up!

Well, that was the results of my playing with the

SUM

function anyway - I could be doing something very wrong!!!

Cheers,

Steve.

 

[scriverb]

Steve, I was thinking you were confused about my Bagdad Bob sig. Just having a little fun with a stupid guy on the TV.

As for you problem, why not perform the DSUM in your query as was suggested earlier? Then this column can be the control source in your report and your Sum of that control can be used at the Report Footer. This should solve the problem.

As far as being missed, I do appreciate the comments from whomever they were. I am back and will try to catchup on what has been happening lately.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Stevehewitt]

Wow, a DSum working in a query? I'll try it now and get back to you! Thanks!

Cheers,

Steve.

 

[Stevehewitt]

I can't believe my own blind stupidity!!! Its obvious now! DUH!

What can I say? Thank you so much. Working a charm now! I can't thank you enough.

Many thanks, and its just a shame I can only give 1 star per thread per user - you deserve 10 for the work, worry and money you have saved me.

Cheers once again,

Steve.

P.S. Good to have you back Bob! ;-)

 

[scriverb]

Great!!! Glad that I could help you out. Also, thanks for the Star. They are truly appreciated.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dhookom]

Hi guys,
Bob, glad to see you back. A couple clarifications for the record.
-You can't ever use =Sum([AControl]). It just doesn't ever work. You can only use an expression that involves fields from the record source of your report.
-I would also expect that the DSum() isn't require as a join to a totals query can be much more efficient.
However, if you got it working with DSum() then go with it.

Duane
MS Access MVP

 

[scriverb]

dhookom: You are correct in that the Sum function in the footer needs to refer to the field of the underlying RecordSource. Oooops!!! Thanks for the catch on that one. I usually have my controls the same name as my Control Source so I misspoke myself.

And, yes, a third option would be to create a Totals Query and join to the original RecordSource query to display the totals.

Many ways to skin a cat.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Stevehewitt]

Thanks,

Good tip, worthy of a star! (I'm in a good mood as you guys have made what is to me a breakthough in the project!)

Cheers,


Steve.