Hi all,
First, I'd like to say thanks to Jon K, Cosmo75, and everyone else who has posted on this topic. All the previous threads about running sums have been very helpful to me.
I have been trying to use the optimized Running Sum by date from Cosmo75's sample database for Acc97 (http://www.access-programmers.co.uk/forums/showthread.php?t=63646), but I can't seem to make it work for my data.
A few questions:
First, do I need the autonumber (or similar) field in my table to make this work?
Also, for the field:
RunningSum: (Select Sum(Value) from [tblData] where [Group] =a.Group and [DataDate] <=a.DataDate)
my data comes from two tables where Value and DataDate come from the same table, while Group comes from a separate table linked to the first (one-to-many). I've tried both using the tables in the query containing the running sum and creating a query to combine the fields and then calculating the running sum from that. Neither seems to work. Creating a temporary table is not really an option because I'm dealing with an ODBC db that contains over 600k entries, and I may be pulling up as many as 15k of them at a time. The system is already slow enough; I'm concerned that creating a table would just use up way too many resources.
Lastly, the dates in my original tables include both date and time, but I want to group by date only. I'm not sure at what point I should be doing this grouping, and I don't know whether to use Format() or DatePart() or whatever.
My final goal is to take my data and create a report containing a cumulative graph over time.
If it's not clear what I'm asking, let me know and I'll throw together a sample db to illustrate my problem. I figure it's something pretty basic, but I've been working on it for a few days and can't seem to get anywhere...
Thanks again,
~ Nuke ~
First, I'd like to say thanks to Jon K, Cosmo75, and everyone else who has posted on this topic. All the previous threads about running sums have been very helpful to me.
I have been trying to use the optimized Running Sum by date from Cosmo75's sample database for Acc97 (http://www.access-programmers.co.uk/forums/showthread.php?t=63646), but I can't seem to make it work for my data.
A few questions:
First, do I need the autonumber (or similar) field in my table to make this work?
Also, for the field:
RunningSum: (Select Sum(Value) from [tblData] where [Group] =a.Group and [DataDate] <=a.DataDate)
my data comes from two tables where Value and DataDate come from the same table, while Group comes from a separate table linked to the first (one-to-many). I've tried both using the tables in the query containing the running sum and creating a query to combine the fields and then calculating the running sum from that. Neither seems to work. Creating a temporary table is not really an option because I'm dealing with an ODBC db that contains over 600k entries, and I may be pulling up as many as 15k of them at a time. The system is already slow enough; I'm concerned that creating a table would just use up way too many resources.
Lastly, the dates in my original tables include both date and time, but I want to group by date only. I'm not sure at what point I should be doing this grouping, and I don't know whether to use Format() or DatePart() or whatever.
My final goal is to take my data and create a report containing a cumulative graph over time.
If it's not clear what I'm asking, let me know and I'll throw together a sample db to illustrate my problem. I figure it's something pretty basic, but I've been working on it for a few days and can't seem to get anywhere...
Thanks again,
~ Nuke ~
