Running totals: Custom Reset via Formula

Hi All,

I have attached a screenshot to help make up for my inability to describe the situation I'm dealing with here.

I have three groups within a report that currently use distinct Running Totals fields that Reset at the group levels that I assigned. I am attempting to create a single Running Totals field that will reset depending on which Group is being calculated at the moment so that I don't have to have a separate Running Totals object for each and every group. I'm not sure how to do this or write this formula as I'm new to Crystal Reports and am used to SQL Reporting Services where this evaluation is automatically done for you (I was spoiled I guess).

For example, if the Running Totals field control is in Group #1, I want it to reset at Group #1, and Group#2 to reset at Group #2.

So basically I'm attempting to use a formula to create a Reset point (view screenshot for detail) that is determined by which group the data is being calculated in. Is this possible? I realize that it is possible by simply creating a new running total object for each group, but this seems completely redundant and overly time consuming for larger reports where there are multiple groups.

I'm developing the report with Crystal Reports within Visual Studio 2005 if that helps any.

Please let me know if I can attach a screen shot or give more information to make my task clear.

Thanks!Either this will answer your question or I've totaly missed the point! (again..ahem)!

Enter a Running Total against the required field. In the Evaluate section select On Change of Group and select your relevant group header.

Very simple or completely wrong! I'll let you make your mind up!

Take it easy

Rob|||Either this will answer your question or I've totaly missed the point! (again..ahem)!

Enter a Running Total against the required field. In the Evaluate section select On Change of Group and select your relevant group header.

Very simple or completely wrong! I'll let you make your mind up!

Take it easy

Rob

Thanks for giving it a shot, Rob. =]

Yeah, I knew about the On Change of Group. I'm able to create a new running totals field for each individual group, but that's what I want to avoid. Instead of selecting the exact group, I'm trying to find a way to code it so that the field examines which group it is currently being evaluated in so that I don't have to create a running totals field for each group as I have a lot of fields that I'm getting the sum for in each group. This would cut my work by a third (or 1/4th for some reports as there will be 4 groups with running totals).|||Actually, a running total has to be placed in an appropreate group footer.
If it has to be reset on change of group#1, it has to be placed in GF1.
If it has to be reset on change of group#2, it has to be placed in GF2 but not in any other one.
How are you planning to solve that problem?|||Try to solve your problem with a new table, view or query where the subtotals are included
I use Crystal in all my projects but I know that it has limits ( or myself ) and always try to make my reports as simple as I can by resolving almost all the stuff at programming side

Running SQL query in HUGE database

hi All, I am currently dealing with a number of tables each with over 40,000 records. I have these tables in Access and i'm planning to do some SQL query on them.

The problem is that due to then large table size, the query is running extremely slow and I'm not sure if using Access SQL is the most viable option.

I have something like:
there are three tables, T1 , T2 and T3.

T1: T2 T3

ID Value ID Value ID Value
1 100 2 5 3 1
2 200 3 5 4 1
3 300 4 5
4 400

My job is to add up all the corresponding values in the three tables and come out with something like this :

Results
ID Value
1 100
2 205
3 306
4 406

So you see the three tables have different number of records and If a record in T1 is not found in T2 or T3, I still want to keep the original value in T1. And for each table there are some 10,000 records!!!

Any advice on how to go about doing this? Some other alternatives I cuold think of is to copy the three tables to Excel and use formula, but in reality I have large number of such files so doing it manually is very time consuming.

Thanks!Your query would be:

select id, sum(value)
from
( select id, value from t1
union all
select id, value from t2
union all
select id, value from t3
)
group by id
order by id;

Whether this is too much data for Access to handle, I don't know. It is certainly a pretty small amount of data for a DBMS such as SQL Server or Oracle.|||thanks I just tried it and it works very well.

however I forgot to add a point that T2 and T3 might contain records that do not exist in T1 (say Id=5)
but I ONLY want records that exist in T1.

What should I do?
Thanks!|||In that case, perhaps an outer join is more appropriate?

select t1.id, t1.value+coalesce(t2.value,0)+coalesce(t3.value,0)
from t1
left outer join t2 on t2.id = t1.id
left outer join t3 on t3.id = t1.id;