Using Minimum Date in Select Formula

[shelby55]

Hi

I am reporting from an Access 2003 database using Crystal 9.0.

I have a table of patient visits (data). I created another table, readmits, from a Crystal report where the current visit shows next visit information. The source is still the data table and the report sorted on chart number (unique identifer) and admit date and then each of the "next" fields would be:

If next({Data.ChartNo}) = {Data.ChartNo} then next({Data.AcctNo}) else ""

etc.

Now I am creating a report showing the original visit but also the subsequent visit(s). Some of the data like diagnoses are from subreports so I have Group1 as the chart number and Group2 is account number (unique to each visit). All Group 2 subreports link on chart number and account number.

I created a field called visit type to denote original versus subsequent visit(s):

if {@admdate} =Minimum ({Data.AdmDate}, {Data.ChartNo}) then "ORIGINAL VISIT "
 else "SUBSEQUENT VISIT "

So now I want to select for certain records where the original CMG value is "100" but only show the chart numbers where there are also subsequent visits. Another wrinkle is that the subsequent visits can only be if their readmit codes = 2 or 3.

I can't use the visit type field to assist with this because I get an error indicating it needs to be evaluated later. How can I do this? Thanks.

 

[lbass]

I don't know what "CMG" is or where the "original" value comes from, so I can't address that, but otherwise, you could do something like:

Create a formula {@readmit23}:

if {table.readmitcode} in [2,3] then
{data.acctno} else
tonumber({@null}) //assumes acctno is a number; if not remove tonumber()

...where {@null} is a new formula that you open and save without entering anything.

Then go to report->selection formula->GROUP and enter:

Distinctcount({@readmit},{data.chartno}) > 1

-LB

 

[shelby55]

Hi LB

Thanks for your response but it won't work...the original visit for which the subsequent visits are determined may not be a readmit 2 or 3 and I want that showing on the report too.

The other issue is that the "data" table is a "one" table and the diagnoses etc. are "many" so most of the information is via subreports. So even if I created a diagnosis subreport for the "original" visit and then for the "subsequent" ones, I'm still left with the issue of evaluating later that isn't working.

Other suggestions?

 

[lbass]

I'm not understanding your data. Are the subreports really relevant to this issue? Please show some sample data and identify the fields. If formulas, show the content. And explain what "CMG" is. Is there an actual "readmit" field?

-LB