Count records conditionally

[cantona]

HELP!!

I have a report, which contains the following fields;
Surname,forename,current placement, and appropriate test (..this has a value of either appropriate or Inappropriate)

The report is grouped by current placement, the details section is supressed, leaving me with just the different placements listed in the group footer. I have added a count to the report, which just counts the number of people in each placement category, which seems to work ok, using the following;

Count([Person_ID])

However, i need to split this count up in each group to show, the number of people in appropriate placements and those in inappropriate placements. How do i go about this?

Ive tried using the following, but the formula, doesnt reset with each group, it just produces a grand total.

=DCount("[Person_ID]","Carer Query","[AppropriateTest] = 'Appropriate'&quot

can anyone help??

 

[danvlas]

=DCount("[Person_ID]","Carer Query","[AppropriateTest] = 'Appropriate' And [Person_ID] = " & [Person_ID])

should do...

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[cantona]

Thanks for your help. Ive placed the formula onto the report in the group footer, but the results are a little strange. It doesnt appear to be counting each Person_ID within the group. Could you perhaps explain what the formula is doing, in case ive missed something out? im new to access, so not quite sure exactly what the formula is doing at the end.!

 

[danvlas]

Sorry, it was stupid...I didn't read what I wrote...

=DCount("[Person_ID]","Carer Query","[AppropriateTest] = 'Appropriate' And [FieldInGroupFooter] = " & [FieldInGroupFooter])

You need two conditions in the DCount: 'Appropriate'/'Inappropriate' and the value in the group footer.

However, i don't understand why you don't add a grouping level for Appropriate/Inappropriate, then the simple Count will do. That would run faster and cleaner...

Sorry again (hope you won't shoot me, will you?)




Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[cantona]

Sorry to be a pain!!
But ive tried your suggestion and keep getting #Error !!
I know this probably sounds really thick of me, but im not sure what to replace [FieldInGroupFooter] with? Should this be the name of the field i have used to group the data?

 

[danvlas]

Exactly...

But try the additional grouping...it's better IMO...

Gotta go now (it's evening here)



Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[cantona]

HELP!
still having problems with this, id like to use the DCount option, because id like all the results to appear on one row as outlined below;

Grouped by [placement_details.placement_type]

Placement type total Appropriate InAppropriate

Residential 10 6 4

any further suggestions, ive tried the following and placed it in group footer

=DCount("[Person_ID]","Carer Query","[AppropriateTest] = 'Appropriate' And [FieldInGroupFooter] = " & [FieldInGroupFooter])

keep getting an #error message!!

can anyone help?!!