Reports that count or add up everything

[KNap]

Hi



I am self taught (so please be gentle in your explanation) and want to be able to sum or count everything in one sheet.

I am able to do that normally, but in this section there are a few drop downs and check list items. Also I have four fields I want to add up... so I wondered how I would do that?

So the four fields are called Diagnosis/Diagnosis 2/Diagnosis 3/Diagnosis 4
Each field is a drop down list which is the same for each.

I want to add up the number of people that have Anxiety in any of the fields.

Would it be =sum([Diagnosis]="Anxiety"+[Diagnosis 2]="Anxiety"+ [Diagnosis 3]="Anxiety"+[Diagnosis 4]="Anxiety")

Or is there an easier way?

Also I want to count the number of times a tick has been added in a field by multiple clients. So I have a check list of mental health symptoms which each client can tick as many as apply to them. If I wanted to count the number of ticks in "Aggressive/violent towards others" column how would I do that in a report please?

Thanks in advance

[Welshgasman]

Your structure is all wrong.
You should have a table of Diagnosis linked to the PatientID in the Patient table and then just count those with Anxiety or whatever

Same with the tickbox options.

[KNap]

The structure is this

Tables:
Client Info
Housing History
Offending History
Alcohol
Drugs
Physical Health
Mental Health

Everything is linked by client reference. Every client completes each form. On the mental health form there has to be room for four diagnosed issues, so I did the four different fields with the same drop down list.

Does this make more sense?

[Micron]

Can you show a pic of your relationships if you created them? I'm not convinced it is all wrong but that's only because I'm thinking your explanation might only relate to the structure of a form plus I have no idea what a "sheet" is in this case. Maybe it's a table with lookup fields, and that would be a different kettle of fish.
A direct answer might be: try
=sum([Diagnosis]="Anxiety")+Sum([Diagnosis 2]="Anxiety")+Sum([Diagnosis 3]="Anxiety")+Sum([Diagnosis 4]="Anxiety")
but that's a wild guess because of not knowing about the design.