Subtotals/Sumif

[chris.leng]

Hi,

I'm trying to add subtotals onto my query in an additional column,

The Criteria are other values from the current record and I'd like to add values from the whole query,

example:

very basic table with Dates and Quantites, I would like a total of all the quantites on and before the date of the current record summing and putting into the next column along:

date ------ qty----- total
5/2/2009 ---- 6 ------ 6
9/9/2009 ---- 5 ------ 11
3/9/2010 ---- 3 ------ 14
6/4/2010 ---- 3 ------ 17
7/3/2010 ---- 6 ------ 23
9/7/2010 ---- 3 ------ 26
1/6/2010 ---- 7 ------ 34
4/6/2011 ---- 2 ------ 36
5/4/2011 ---- 3 ------ 39

in the real world it wont be sorted so neatly, and there will be additional columns and criteria (eg, Type and Color)

 

[pbaldy]

What you're trying to do is typically called running sum or running totals, so try searching on that. It's typically done with either a DSum() or a subquery, either one using criteria that get records through the date of the current record.

 

[chris.leng]

Ahh, thanks for the tip on searches,

i tried the dsum method,

i set up a functon, used 2 arguements, but had trouble making it work, code:

Function sumfunction1(vardate As Date, var1 As Long) As Long

DSum("field2", "table", "field1 > " & var1 & " And date > #" & vardate & "#")

End function


and the query:
expr1: sumfunction1 ([somedatefield],[somefield]

 

[pbaldy]

Not sure of your logic, but right off you don't have the function returning anything. It would be like this:

sumfunction1 = DSum(...)

 

[chris.leng]

yea, i'd done that, but it didn't seem to accept the inputs from the query?

 

[pbaldy]

A couple of thoughts. Not sure about the first criteria, but based on your description you'd want <= for the date field. Is "Date" really the name of your field? If so, not a good idea. I'd change it, as it can conflict with the Date() function. If you can't, you can try bracketting it:

..." And [date] > #"...

 

[chris.leng]

ok, i'll try that, also when i run the query and the function si wrong how do i stop it trying to run the broken function 5000 times? non of the normal exit stuff works

now its saying undefined function, but it is, wtf? :/ - oh desgin mode seems to solve the first issue.

anyway, when i think its all working the query stops drawing properly, only the top right cell displays?



now:

Function terriersum(terdate, terijn)

terriersum = DSum("qty", "ssched", "ijn > " & terijn & " And [date] > #" & terdate & "#")

End Function


is coming back missing somethin.....

 

[chris.leng]

yay, failed with the whole query stopping thing and access crashed out, whys is it so bad

right:
Function terriersum(terdate, terijn)


terriersum = DSum("qty", "ssched", "ijn > '" & terijn & "' And [date] > #" & terdate & "#")

End Function

something about a reserved error?
i'm kinda stuck wtih "Date" cos it doesn't seem to want to let me use the query itself as the domain, so i'm using one of the tables (linked tables, i can't edit them)

also any thoughts on stopping the query running a full cycle if the vba is wrong and spitting up errors every cycle??
arrrrrrrrrrrrrrrgh!!!!

i've got some "stupid object or whatever not defined" that wont go away, escape/control break etc dont end it, how do i close the query?

what the hell is so hard about this? i've done tonns of queries/vba before, never had anywhere near this much fucking hassle, esp not over about 100 characters of code

 

[chris.leng]

test 12: terriersum([wwo].[date],[wwo].[out])



Function terriersum(terdate, terijn)

terriersum = DSum("qty", "ssched", "ijn > " & terijn & " And [date] > #" & terdate & "#")

End Function


why does that go nuts?!?

 

[chris.leng]

first error - syntax error in:
'ijn = " and date =#'

dunno where that comes from, the code still looks like what i just posted?

 

[chris.leng]

can anyone see/explain where i'm going wrong? its becoming very frustrating

 

[pbaldy]

The error in post 10 would imply that one or both of the values being passed to the function are Null. Are Nulls possible? Can you post the db?

 

[chris.leng]

I'm afraid not, i'll see what i can do regards trimming down data tomorrow, how do i do a field with spaces within that dsum? (ie replace date with "delivery date"?)

also what exactly is the difference between "" and [] within things? i assumed [] was field and "" was... stuff? bit confusing... i know ' ' is sometimes numbers, also nothing can be numbers, and # # is date? how am i doing?

 

[pbaldy]

This should help with the syntax:

http://www.mvps.org/access/general/gen0018.htm

The brackets are required when you have spaces in your names, or use reserved words.

 

[chris.leng]

putting it into the query instead of calling it as a function has worked wonders, and yea, greater use of square brackets solved a fair few problems

happy days now, lol