Couple of small count problems in Report

[krowe]

Hi

I have a couple of minor problems in a report.

I have 2 fields for carers (PrimaryCarer and SecondCarer) I want an unbound field to say how many carers there are per patient (there is only ever one or two)

I have used this expression

=IIf(Len([SecondCarerName]=0),1,2)

I have also tried IIF(isnull([SecondCarerName]),1,2)

but neither of these will work.


The second problem is have have tried to get sub totals for each group and grand totals in the report footer.

A couple of my grand totals do not add up right (Total Patients and Total Carers). Im not sure if the sub totals are affecting the grand totals???

I have attached the file as this is a bit harder to explain.

I would be grateful for any help.

Thanks

Kev

 

[boblarson]

Your table structure has something to do with it. You erroneously store the Primary and Secondary Care Provider names in the same record. This should be actually another table

tblPatientCareProviders
PatientID
CareProviderID

And then they can have as many as needed and then displayed using a SUBREPORT and using a SubForm when entering data.

Also, including Siblings in the report's record source is a problem. They too should be in a SUBREPORT.

 

[krowe]

Thanks Bob,
I think subreport for the siblings is way to go, i was just trying to be lazy by using groupings.

As for carers, a patient can only have 1 or 2 carers to the party, (not 0, they must be accompanied, and not more than 2) which is why I put them in the table as I did. I wouldn't know where to start with that kind of validation on the carer subreport.

I will redo the siblings part of the report. Is there a way to count the carers without having to split the table?

Thanks

Kev

 

[boblarson]

Is there a way to count the carers without having to split the table?

Thanks

Kev

Add this to the query:

CarerCount: IIf([PrimaryCarerName] Is Not Null,1,0)+IIf([SecondCarerName] Is Not Null,1,0)

And then use that field for your totals.

 

[krowe]

Hi

Thanks for that, i'm almost done. I have managed to get the sibling count outside of the subreport, but get an error when it = 0

also i cant generate the grand total for the sibling count in the report footer.

I have attached where i'm up to so far rptPatient2 is the report that i'm working on.

thanks again

Kev

 

[boblarson]

Hi

Thanks for that, i'm almost done. I have managed to get the sibling count outside of the subreport, but get an error when it = 0

also i cant generate the grand total for the sibling count in the report footer.

I have attached where i'm up to so far rptPatient2 is the report that i'm working on.

thanks again

Kev

Okay, take a look at the revised sample. Let me know if you have questions.

 

[krowe]

thats great, DCount is a new function to me, kind of a count/where.

Thanks