Access Running Totals Report needs additional criteria

[enigma54]

Access Running Totals Query needs additional criteria

Hi,

I have included a screenshot of the query for reference.

I am trying to create a running totals query in access.

I have successfully managed this following guidelines on Technet (I forget the reference) and have come up with this expression:

ActualRunTotal: DSum("OrderValue","TblTenderEnquiryIndex","DatePart('m',[DateOrdered])<=" & [DateOrder] & "")

Unfortunately my report narrows down the data by market sector which the user selects before running the query.

Currently the expression returns a running total of all Orders in the TblTenderEnquiryIndex table. It does not narrow it by market sector.

I have tried this:

ActualRunTotal: DSum("OrderValue","TblTenderEnquiryIndex","DatePart('m',[DateOrdered])<=" & [DateOrder] & "" And [TblMarketSector].[ID]=[Forms]![FormReports]![MarketSector1])

where MarketSector1 is the combo on the reports form containing the market sectors

The query doesnt run though with the error: 'You tried to execute a query that doesnt include the specified expression (above) as part of an aggregarte function'

Have I got the syntax wrong for the second 'Where' clause in the DSUM or is it not possible to dig into a separate table (separate from the named domain in the DSUM expression) to filter the results

As ever any help on this would be greatly appreciated.

Thanks a lot

Edit: Epic fail! Cant believe I posted this in the wrong forum. Please can a generous mod move it to the Queries section. Thanks and apologies

 

[vbaInet]

It will be much easily done in a report. Are you ultimately doing this to use in a report?

 

[enigma54]

Thanks for the reply. The SQL will ultimately be placed in a sub report shown on a main 'Orders Report'. It should show a summary of running totals of Order Value over the year.

I usually make my SQL in QBE and then paste it into a report when it's working.

Is there an easier way to do it direct on a report without using dsum then?

 

[vbaInet]

Is there an easier way to do it direct on a report without using dsum then?

Yes there is. Do you have a GROUP for market sector on the report itself? Not in the query.

 

[enigma54]

Ok I have Imported Market sector to the main report (not a sub report). I have added a group for MktSector. How do I then calculate running totals on the report. Presumably a formula is required.

Thanks again for the help

 

[vbaInet]

Code is required.

So if Market Sector is on the main report and the field to sum is on the subreport, how do they both tie in? Sounds to me you don't need a subreport in the first place.

 

[enigma54]

No the report and sub report are not directly related.

This is probably something I should have explained before.

I have attached an example report for December as an image.

Basically the main report (the top bit) runs between two set months and by market sector (in this case December-December and aerospace/defence)

This main report then shows an overview of orders in December for The Aerospace/defence mkt sector.

At the bottom is one sub-report. This shows a summary for the year (basically for each order month it shows the budget set, the actual moneys received, a difference of the two and a % difference). You can see in this example that the total for the main report is actuially shown in the sub report for December.

There was also going to be a second sub report (on the bottom right of the report) that showed similar values but a running total for the year so basically Octobers Figures added to Novembers Figues, Novembers Figures added to decembers Figures etc)

The creation of the second sub-Report was the subject of my original post.

 

[vbaInet]

There was also going to be a second sub report (on the bottom right of the report) that showed similar values but a running total for the year so basically Octobers Figures added to Novembers Figues, Novembers Figures added to decembers Figures etc)

The creation of the second sub-Report was the subject of my original post.

Alright, I now see.

In the declarations section of the subreport's module dim a variable for the running sum:

Option Compare Database
Option Explicit

Private runningSum as Currency

In the Format event of the Detail section in the subreport put something like this:

runningSum = runningSum + Me![Actual]
txtRunningSum = runningSum

Where txtRunningSum is the name of the textbox that will display the running sum.

In the Format event of the subreport's Report Header section put this:

runningSum = 0

 

[enigma54]

Excellent. That works really well.

Also you saying that running sums were easy to do on the report level, not the query level allowed me to find this on the internet:

http://office.microsoft.com/en-us/a...running-sum-cumulative-total-HP005187388.aspx

I didnt even realise textboxes have a running sum property!

Thanks for your help though. I spent an epic amount of time on this yesterday getting nowhere!

 

[vbaInet]

Oh yes, there's that too. Completely forgot about that. I would have remembered if I was looking at a db.

Glad we could help!