Re: complex filter and calculations in access

Well, I am not really sophisticated enough to use more than one table, sort
of, but here goes.

Main table

Patient last name Text
Patient first name Text
Medical record Number number
operation performed text
operation code number
surgeon name text this is returned to the form from a combo
box from another table named surgeons containing about 20 names

then there is a series of yes/no check boxes
Diabetes yes/no
pre-op infections yes/no
high blood pressure yes/no
steroids yes/no

then the complications
infection yes/no
heart attack yes/no
death yes/no

there are some other fields scattered around like how long and operation took
and nunber of units of blood transfused.

to repeat my question how do I create a query that will alow me to do
calculations on a subset of my data from many fields such as counting the
number of infections one surgeon had compared to his total number of
operations. Or what is each surgeons average blood transfusion rate for a
given operation.

Thanks

Tom

PS I posted another question to anothr part of the forum as to how to get my
table to look up one piece of data on another table and return that piece of
information and the another bit associated with it in the same table - but I
didn't really get an answer


"Marshall Barton" wrote:

Not the contents of a table, the **structure** of the tables
involved in your question. I.E. just the table names along
with their significant fields, for example:

Customers:
CustID AutoNumber primary key
CName Text

Contacts:
ContID AutoNumber primary key
CustID Long foreign key to customer
ContName Text
ContPhone Text

table c:
. . .

If you can restate you problem in terms of the tables and
fields in the structure, that would also be a big help.
--
Marsh
MVP [MS Access]


DrTominRI wrote:
I would love to post my table to you all. how do I do that?


DrTominRI wrote:
I don't really think this is that complex. My medical database includes
mixed data, text, numbers, yes/No's etc Specifically, patients that have
undergone a specific operation by a specific surgeon, some have certain risk
factors and some have had certain complications. I can certainly filter to
get all of the patients who have had an appendectomy/by Dr Kildare/who have
diabetes/and got and infection/ But how do I calculate Dr Kildare's overall
infection rate? How do I make the query count Just all of Dr Kildares
operations, and use that as a denominator. How can I compare his infection
rate in diabetcs vs non diabetics?

Please help, my local exper says I need to export it all to excel and do it
there.


"Marshall Barton" wrote:
You will probably want to use queries of queries or
subqueries for some of that, but, as Karl said, we need to
know your table structure.

If your "expert" thinks Excel is the way to do this, s/he's
probably an "expert" in Excel, not in Access/SQL.

.

Loading