Re: complex filter and calculations in access

I really appreciate the help I was given. I know it is impossible to guess
how to help me if I barely know what I am doing. Your analogy is more
perfect than you know. Unfortunately, I actually get that eveery day.
Thanks for the help

"Marshall Barton" wrote:

DrTominRI wrote:

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.

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


The basic query for aggregating (Count, Sum, etc) data such
as you're looking for will be along these lines:

SELECT M.[Surgeon Name],
Count(*) As CountOf Operations,
Sum(IIf(M.Infection, 1, Null)) As CountOfInfections
FROM MainTable As M
GROUP BY M.[Surgeon Name]

or

SELECT M.[Surgeon Name],
Avg(M.OperationTime) As AvgOperationTime,
Avg(M.TransFusionAmt) As AvgTransFusionAmt
FROM MainTable As M
GROUP BY M.[Surgeon Name]

I am uncertain whether you will see the surgeon's name or an
ID number because you did not provide details about that
combo box lookup field.

If you take one of those queries and Paste into a new
query's SQL view, fix the names to the ones you are using,
and then switch to design view, you should be able to see
how to create other similar queries.

On your other request for assistance, I want to warn you
that your problem is poorly defined and the critical
information is not complete. In addition, the fact that you
have most all of your data in a single table (which violates
the Normalization rules of relational database theory) means
that there are only a few knowledgable people with
experience in working with an ill designed table structure.
So, getting answers means that one of those few has to see
it, decipher what you're talking about, and have the time
and wherewithal to come up with a good answer. I understand
that you are not, and have no interest in becoming, a
database programmer, but getting someone that is capable in
this arena to help you means that you should do some
homework and provide a clear problem description. It's kind
of analogous to someone coming to you and saying "hey Doc,
I don't feel good, make me better" and then you have to
perform your diagnostic procedures not only using email,but
also in a language you are only vaguely familiar with. ;-)

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: complex filter and calculations in access
    ... box from another table named surgeons containing about 20 names ... Or what is each surgeons average blood transfusion rate for a ... If you take one of those queries and Paste into a new ... the Normalization rules of relational database theory) means ...
    (microsoft.public.access.queries)
  • Re: complex filter and calculations in access
    ... box from another table named surgeons containing about 20 names ... to repeat my question how do I create a query that will alow me to do ... Or what is each surgeons average blood transfusion rate for a ... the Normalization rules of relational database theory) means ...
    (microsoft.public.access.queries)
  • Re: complex filter and calculations in access
    ... box from another table named surgeons containing about 20 names ... Or what is each surgeons average blood transfusion rate for a ... If you take one of those queries and Paste into a new ... the Normalization rules of relational database theory) means ...
    (microsoft.public.access.queries)
Loading