Running sum problem in query! 2

[schredder]

Hello all

in another thread i learned that there is a possibility to have a running sum in a query and that it is possible to get it worked even without an ID-field. I want to have a running sum with dates as unique identifiers. It lookas like this:
Query name: MTA_signal
Fields: date, ticker, signal, close, signalorder (whereas this field "signalorder" i calculate and generate it in the same query (it's a product of signal*close).
Now my runningsum-field looks like this:
Rusum: DSum(&quot;[SignalOrder]&quot;;&quot;MTA_signal&quot;;&quot; [Datum] <= #&quot; & [Datum] & &quot;#&quot
I can't get it working, the datasheet shows #Error in the respective coloumn.

Any help is greatly appreciated.
schredder

 

[BNPMike]

Just looking at it I don't like the use of semi-colons or the &quot; marks in the last part:- &quot; [Datum] <= #&quot; & [Datum] & &quot;#&quot. In fact what does that mean?

 

[schredder]

Hi Mike
as I said, have this from another thread where the pros suggested to use it like this. I tried every possible way of typing ...still no luck.

 

[PaulBricker]

Can you post a link to the other thread. That might help. Whatever, you should be using commas, between the arguments in the DSum() function and not semi colons (as far as I know).



Paul

 

[schredder]

Hi Paul

yes sure: for example
thread701-166033 or
thread701-493981, post from scriverb

The function is not accepted when i'm using commas, maybe this is because i have Access2k or i'm using a german version, think semi colons are ok since the expression is accepted.
I first thought my problem might be the dates, but i have unique dates so they should work as identifiers.

Chris

 

[PaulBricker]

Rusum: DSum(&quot;[SignalOrder]&quot;,&quot;MTA_signal&quot;,&quot; &quot;[Datum] <= #&quot; & [Datum] & &quot;#&quot


Try this and see how it goes.

Paul

 

[RoyVidar]

Can't give any help on the query, I'm afraid, but I addressed the &quot;,&quot; vs &quot;;&quot; here thread702-733634.

If &quot;,&quot; is used as decimalseparator in Germany too, the &quot;;&quot; should work as argument separator.

Roy-Vidar

 

[PaulBricker]

Thanks for that update. I suspected that might be the case but had not investigated it further. Congratulations on TipMaster honors.

Paul

 

[RoyVidar]

heh heh - when living in a &quot;non default&quot; country;-), there are some &quot;quirks&quot; one have to live with... And - thank you!

Roy-Vidar

 

[schredder]

Roy, Paul
Thanks a lot...indeed i have to use &quot;;&quot;. Still, i have no luck with the query. So i tried:
Query name: MTA_signal
Fields: date, ticker, signal, close, signalorder (whereas this field &quot;signalorder&quot; i calculate and generate it in the same query (it's a product of signal*close).

RusumSum(&quot;[SignalOrder]&quot;;&quot;MTA_signal&quot;;&quot;[date] <= #&quot; & [date] & &quot;#&quot

When i run the query, the respective coloumn &quot;Rusum&quot; is filled with &quot;#error&quot;. When i click on one of the fields in the coloumn i get the message &quot;syntax error in date in the query expression '[date] <=#18.2.2000#'.

Thanks in advance for any idea.
Chris

 

[RoyVidar]

Hi again!

As I stated above, I'm not that good on queries, and I seldom use the domain aggregate functions...

Browsing the threads you refer to, the domain aggregate function is used on tables. DA functions might also be used on stored queries, but as I read you, you are trying to use Dsum on the same query you are running. I'm not sure if that's possible, but perhaps PaulBricker knows?

On the last thingie, it's not advicable to use Date as name of fields - and here it also looks like a non US date gives a problem.

Perhaps if you posted the entire sql, some of us might try it out and give some assistance?

HTH Roy-Vidar

 

[PaulBricker]

I'm going to guess that it has to do with the dot(.) in your dates. Probably doesn't see that as date delimiters. Try this. In a new column of your query, on the FieldLine put
MyDate:Format([date],&quot;dd/mm/yyyy&quot

Then in your DSum() function use

RusumSum(&quot;[SignalOrder]&quot;;&quot;MTA_signal&quot;;&quot;[MyDate] <= '&quot; & [MyDate] & &quot;'&quot

A couple things.
1. Make sure that in query design mode, that the MyDate field preceeds (is to the left of) the DSum() function. Queries evaluate from Left to Right so we need to set the format for Date and then use it in the DSum()
2. The Format function converts the values to strings so that's why the date delimiter is not a # but a '. We could use an expression like this MyDate:CDate(Format([date],&quot;dd/mm/yyyy&quot)
and that would leave it as a date datatype so the # delimiters would work.
Let me know how it goes.


Paul

 

[JonFer]

Look here for an alternative method for running sums:

thread701-708836

Using DSUM on each row of your query is inefficient.

 

[schredder]

Thanks both of u guys, fantastic it works. Went for Jon's solution since the dsum function is indeed very slow with a lot of data.
Chris