Help on date 1

[hlmnsbuggy]

Crystal rpt 9:
I have a database table as following

Acct# StartedDate ClosedDate Amt$In(out) AmtEffective
1111 1/1/2004 1/1/1900 200 8/20/2004
2222 7/2/2004 8/15/2004 0 8/16/2004
1111 1/1/2004 1/1/1900 -50 8/27/2004
3333 8/27/2004 1/1/1900 100 8/28/2004
......

I need count how many accts started , closed and amount in or out each day. i had hard time to do so since started and close dates repeated with acct#. If I do subreports. it seems it need 3 seperate subreports one for started day, one for close date and one for amt effeective date. Do I have any easy way to do so?
Thank you for your help in advance.

-L

 

[synapsevampire]

No subreports, they're pure eeeeeeeevuuuuuuuhl

Group by the EffectiveDate

Createa a formula called start:

totext({table.acct},0,""+" - "+totext({table.starteddate})

And one called Closed:

totext({table.acct},0,""+" - "+totext({table.Closeddate})

Now create Running Totals of distinctcount for each and in the Evaluate->Use Formulas, as in:

Started:
(table.startedate} = {table.amteffective}

Ended:
(table.Closeddate} = {table.amteffective}

Now you'll get each effective date (presumably all rows), and distinctcounts for any started/closed acct-dates for each.

Then in the Report->Edit Selection Formula use:

So display the Running Totals in the Group Footer, and the amounts in the details. If there';s only one amount per effective date, than you can display all fields in the group footer.

-k

 

[hlmnsbuggy]

Synapsevampire,

You are the man! Thank you for your reply.

-L

 

[hlmnsbuggy]

Synapsevampire,

I noticed it might have one problem. to count the running total of the start date which sometimes is not equal to amteffective date. Amteffective date mostly are a day behind if its weekday.

ThankS again
-L

 

[synapsevampire]

Are you stating that you may not have some effectivedates for the start dates?

If so, this presents a larger problem in that you'll need to fabricate data.

Depending upon your SQL sophistication and access to the database, you might create a Union Query to join your data to which would have something like:

select distinct StartedDate from table
union
select ditinct ClosedDate from table
union
select distinct Effective from table

Now you have a list of all possible dates.

Join your table to this table and you can now use similar theories for constructing the output.

-k

 

[hlmnsbuggy]

I will try it after I do some studying about union qry. Thank you again