summarising a distinct count field 2

[gromboy]

Hi,
I have a database which has chemotherapy episode data in it.
I want to be able to count the number of treatment episodes on a monthly basis, split by the ward or clinic area.

Each patient will attend on a number of occassions over several months or years. Each time they attend I have multiple rows for that days treatment.
I created a cross tab which has appointment date, ward and a count on the patients hospital number. If I do a distinct count using a sample set of data per month I get only 1 result, if I do a count I get too many rows.

For exmaple on a monthly period I should be getting appox 30. A count gives me 120, a distinct count gives 13.

Any ideas anyone ?
Steve

 

[IanWaterman]

suggest you look at a detail report.

Group data in same way as you have in crosstab and place patient number in detail, then filter data so replicate info from one column of your cross tab and one patient.

This will help identify why the counts are performing as they do.

Let us know whet you find.

Ian

 

[lbass]

Please explain the crosstab setup--what are your row, column, and summary fields?

-LB

 

[gromboy]

Hi,
My row is Appointment Date
My column is the ward location
My summary is a distinct count on the patients hospital number. A count also doesnt work.

The distinct count gives 11, the count gives 127. If I go into the front end and count the attendances it comes to 29.

Any idea how I can get this figure ?
Steve

 

[gromboy]

To help with this I've attached a screen shot of the cross tab.
This shows a distinct count by day. But as you can see the total is not correct.
Steve

 

[lbass]

We don't really have enough information here. Ian's suggestion was a good one. I would suggest grouping on ward location and then on appointment date, and then inserting a distinctcount and a count on the patient ID at each group level. Then observe the results to see why the crosstab is returning what it is returning.

Is your row field based on a day interval? Or did you set it to on change of month? If on change of month, a distinctcount would only pick up one visit per month. You might want to concatenate a visit ID with the patient ID and then do a distinctcount on that formula, if this is the case.

-LB

 

[gromboy]

Hi,
I started a new thread by mistake on this subject as I thought my orginal title and first comments werent very clear (sorry), it has more details and a link to the actual report

http://www.tek-tips.com/viewthread.cfm?qid=1499313&page=1

 

[lbass]

Please try my last suggestion.

-LB

 

[gromboy]

Each visit doesnt have a seperate ID.
It does however have a Cycle number and Day number which will be different for each date.
How do I do this using the concatenate command ?
Sorry for being dim
Steve

 

[lbass]

Are these all number fields? Try:

totext(cyclenumber)+totext(daynumber)+totext(patientID)

-LB

 

[gromboy]

I used your formula and did a distinct count, and dit worked a treat...thankyou soooo much