How do I count an occurance? 1

[ramblinrose]

I have a report that shows Clients with Hospital visits. The report is grouped by Admit Date. In the details section I have client IDs. I need to have a field that shows previous Admits which would be an accumulative count of the Client ID. A client could be on the report more than once, but not in succession. How would I show that amount? The database is an access db.

Example:

Group: Admit Date (Suppressed)
Details: Client ID Previous Hospitalizations Admit Date

 

[Madawc]

Do a Distinct Count on client IDs, which covers just this case.

If you're not already familiar with Crystal totals, the basics are explained at faq767-6524. A summary total would be best for what you seem to need.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

If you need the previous hospitalizations up to the current admission date, then the problem is that you would have to use a running total that was specific to a particular client where the running total used a distinctcount of admission dates and the evalution formula was like:

{table.client} = 123

Reset never. This probably isn't feasible. Another way you could do this is to insert a group#2 on {table.clientID} and then insert a subreport in the group header or footer for this new group that is linked to the main report on client ID and on admission date. Then go into the subreport record selection formula and change it so that it reads:

{table.admissiondate} < {?pm-table.admissiondate} and
{table.clientID} = {?pm-table.clientID}

The subreport should contain the admissiondate field in the detail section. Insert a distinctcount on it at the grand total level and then suppress the detail and all other sections of the subreport except the report footer.

-LB

 

[ramblinrose]

Thank you Ibass and Madawc for your response.

Ibass, your comment worked perfectly. I had already tried the subreport, but did not change the subreport record selection formula to {table.admissiondate} < {?pm-table.admissiondate}.

Madawc, A distinct count would not work, because there are multiple clients on a single report.