Cannot calculate monthly totals

[gromboy]

Hi,
I have a db which records chemotherapy appointment data.
I want to count the number of days a patient attends for treatment on a monthly basis. The database contains several rows for each days treatment and they can vary. Each row will contain the patients number, the appointment date, drug names etc etc A patient may have more than one appointment during a month.
If I do a distinct count I get the number of patients (not the number of times they have attended), if I do a count I get all rows for all days.
For example for Aug 2006 distinct count gives 12, and a count gives 256. The total episodes when I look in the front end is 29.
How can I get an accurate monthly total.
I would appreciate any help with this
Steve

 

[IanWaterman]

Asking the question again will not change the answer ;-). In your earlier post I suggested you did some investigation of the data using 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

 

[gromboy]

Hi,
I reposted as I thought I handnt made myself very clear..
I've done what you've suggested and using a distinct count on appointment date, and dropping it into the Patients number group I gives the correct figure. If I do this in the cross tab it doesnt give the right figure as it is counting on a monthly basis.
I've attached the report so you can see
Steve