Counting Multiple Tick Boxes in a Queery

[illingworth22]

Firstly I am a complete Access novice!
I have created an Access database for the Mortuary in the hospital where I work. The database has several tables, the main being the register of all patients who have passed away. I have several fields in the main table i.e. name date of birth and date of death and ward where patient passed away ect.
Here is where I run into my problem, when a patient dies they sometimes are referred to the coroner, I have a Tick Box for this and if the coroner orders a Post Mortem I have another Tick Box.
Now when I run a query all patients who were “referred to coroner” (using -1 in the Criteria part of the Refered to Coroner tick box) the report lists all patients in the correct way, but when I ask for a SUM of all patients referred to the coroner and all post mortems ordered all I get in the sum area of the report is a “Yes” instead of the value/ammount.
I have searched through the forum and I cant seem to find my answer….. any help is much appreciated.

 

[Scooterbug]

Use a Count instead of a Sum of the field

 

[illingworth22]

OK I have my Query. Now lets say 20 people have died in November and of that 20, 10 were refered to the Coroner. Out of the 10 refered to the Coroner only 1 post mortem was ordered. My Query for November gives 20 records. If I put =Yes into the Crieria of Refered to Coroner I now only get 10 records, which is so far right. But how do I get my report to show a total of post mortems ordered (which is = 1) as the PM Oredered field only shows 1 Yes (and 9 No's).
I hope this makes sence, what I think I am asking is how do you count the Yes's in the colum of the 10 Refered to Coroner. The Yesses either appear as Yes or as Ticks depending on how I set up the Query!

 

[Scooterbug]

Just set the criteria to Yes for the PM Ordered field. That should only show the paitents (can they be called paitents after they die? ) that were referred to the Coroner for a post mortem.

If you are looking for just a count of total PM Ordered, create a new query and use the Count option in the totals entry.

 

[illingworth22]

Just set the criteria to Yes for the PM Ordered field. That should only show the paitents (can they be called paitents after they die? ) that were referred to the Coroner for a post mortem.

If you are looking for just a count of total PM Ordered, create a new query and use the Count option in the totals entry.

So two more questions from here. I put a -1 in the Criteria and all Refered to Coroners now only appear, with a -1 in the columb. Do I need to turn the -1 in to a +1 in order to count (Count rather than Sum)?
Second Question. If I want a report to show Total Coroners and Total Post Mortems ordered do I have to use two separate querys?

 

[Brianwarnock]

No, a tickbox is -1 if True/yes therefore
CountofPostmortem:sum(abs([postmortemtickbox))
can be done for each tickbox

Brian

 

[Scooterbug]

Count counts up the instances of the number of unique values of the particular field. So if you include the field ReferredToCoroners in the query twice (with the criteria set for the first column) and switch the Totals line to Count for the second column, the query would display one line. The first field will have a value of -1 (Which is the number assigned for Yes) and the second field will have the total number of records where that field is equal to Yes.

As for the report, I attempted to use sorting and grouping...but the way you have your table designed I couldn't quite get it. So, your best bet is to use separate queries for the information. If you are looking to put the data on the same report, you would use subreports. I've attached an example.

 

[Brianwarnock]

I have taken the liberty of modifying scooterbugs Db, I have set a couple of ToCoroneor to No and then done a second query to show the use of Sum(abs

Brian

 

[illingworth22]

I have taken the liberty of modifying scooterbugs Db, I have set a couple of ToCoroneor to No and then done a second query to show the use of Sum(abs

Brian

Thanks very much for all your help. If you ever need my help...... well let's just say I hope you don't