I agree with Marshall Barton's comments, but I kind of like to use
concrete (even if imaginary) examples in my explanations, when I have
time to do so. So, hoping not to muddy the waters, my suggestion (based
on Marshall's) is to set up (at least) the following three Tables.
First, you already said that you'd have a Table containing information
on surgeons, so I set up an example containing two records.
[Surgeons] Table Datasheet View:
Surgeons_ID name
----------- -------
-1124293098 Kildare
240216869 Welby
Also, thinking that one operation may be performed more than once, and
you included two fields that appeared to be related to that, I set up a
Table to contain that type of information. (Actually, I know that such
information already exists, so you could probably just import this from
a government or insurance-company Web site.) For my example, this Table
contains only a numeric code and a verbal description, but I strongly
urge you to use the standard codes.
[Operations] Table Datasheet View:
operation code operation performed
-------------- -------------------
123 Brain transplant
666 Liposuction
855 Cardiectomy
Now, for the Table containing patient information, I included fields
linking it to the other Tables. For example, in the first record,
instead of specifying a surgeon's name, I used the number, 240216869,
from the [Surgeons_ID] field for that surgeon's record in the [Surgeons]
Table. This is called a "foreign key", being a reference to the primary
key in some other Table. Same for the [operation code] values. Since
each one may be used more than once, it avoids duplicating information
such as the surgeon's name that would otherwise take extra space in the
[Patients] Table, and even worse, cause confusion in case the surgeon's
name were misspelled in some [Patients] records.
[Patients] Table Datasheet View:
Patients_ID last name ... operation Surgeons_ID ...
code
----------- ----------- --------- -----------
-1419127 Doe ... 123 240216869 ...
287390171 Interruptus ... 855 240216869 ...
1896333185 Piggy ... 666 -1124293098 ...
2060653442 Stiltskin ... 666 -1124293098 ...
1284631936 Tenshun ... 855 -1124293098 ...
However, these foreign keys may be hard to read, so I almost always
define a Lookup property (in Table Design View) for the foreign keys.
This causes the datasheet to display some more meaningful information,
in my case the name of the surgeon or of the procedure. Some people
dislike using Lookup properties; the most compelling reason I've seen
for that is that the underlying value is still that key number, and it's
easy to forget that the name you're seeing is not the actual value
stored in the Table. If this bothers you, then don't use Lookup
properties. But for now, the rest of my example will use Lookups in
order to make the datasheets easier to read.
So, with Lookups defined on the two foreign keys, and with the (long)
records in the [Patients] Table split into 3 parts to make them easier
to read, my example [Patients] Table looks like this:
[Patients] Table Datasheet View:
Patients_ID last name first name Medical operation code
record
Number
----------- ----------- ---------- ------- ----------------
-1419127 Doe John 1872 Brain transplant
287390171 Interruptus Curtis 1933 Cardiectomy
1896333185 Piggy Miss 1777 Liposuction
2060653442 Stiltskin Rumple 1980 Liposuction
1284631936 Tenshun Hooper 1922 Cardiectomy
Surgeons_ID Diabetes pre-op high steroids
infections blood
pressure
----------- -------- ---------- -------- --------
Welby No No No No
Welby No No No No
Kildare No No No No
Kildare No No No No
Kildare No No No No
infection heart death how long units of
attack the opera- blood
tion took transfused
--------- ------ ----- ---------- ----------
Yes No No 3:25 17
Yes Yes Yes 0:20 1
No No No 0:45 3
Yes No Yes 1:30 1
No No Yes 2:35 22
Now that we have example Tables on which to base some Queries, my
versions of Marshall's Queries follow.
First, I need to determine how many operations are recorded for each
surgeon; this number will be used later.
[Q_TotalOperations] SQL:
SELECT Surgeons.Surgeons_ID, Surgeons.name,
Count(Patients.Patients_ID) AS CountOfOperations
FROM Surgeons INNER JOIN Patients
ON Surgeons.Surgeons_ID = Patients.Surgeons_ID
GROUP BY Surgeons.Surgeons_ID, Surgeons.name;
[Q_TotalOperations] Query Datasheet View:
Surgeons_ID name CountOfOperations
----------- ------- -----------------
-1124293098 Kildare 3
240216869 Welby 2
Next, I count the number of post-op infections on record for patients
linked to each surgeon, and I calculate relevant infection rates (based
partly on the values returned by the [Q_TotalOperations] Query).
[Q_Infections] SQL:
SELECT Surgeons.name,
Count(Patients.Patients_ID) AS CountOfInfections,
[CountOfInfections]/[Q_TotalOperations]![CountOfOperations]
AS InfectionRate
FROM (Surgeons INNER JOIN Q_TotalOperations
ON Surgeons.Surgeons_ID = Q_TotalOperations.Surgeons_ID)
INNER JOIN Patients
ON Surgeons.Surgeons_ID = Patients.Surgeons_ID
WHERE (((Patients.infection)<>False))
GROUP BY Surgeons.name,
Q_TotalOperations.CountOfOperations
ORDER BY Count(Patients.Patients_ID) DESC;
In this Query Datasheet, I specified a "percentage" format with 2
decimal places for the last field. You can specify a format by opening
the Query in Query Design View, right-clicking at the top of the field's
column, choosing Properties, and specifying the format you want.
[Q_Infections] Query Datasheet View:
name CountOfInfections InfectionRate
-------- ----------------- -------------
Welby 2 100.00%
Kildare 1 33.33%
Here's another Query showing some averages:
[Q_AvgTime&Amount] SQL:
SELECT Surgeons.name,
Avg(Patients.[how long the operation took])
AS [AvgOfhow long the operation took],
Avg(Patients.[units of blood transfused])
AS [AvgOfunits of blood transfused]
FROM Surgeons INNER JOIN Patients
ON Surgeons.Surgeons_ID = Patients.Surgeons_ID
GROUP BY Surgeons.name
ORDER BY Surgeons.name;
Again, I specified appropriate formats for the numeric fields in this Query.
[Q_AvgTime&Amount] Query Datasheet View:
name AvgOfhow long AvgOfunits of
the operation took blood transfused
------- ------------------ ----------------
Kildare 1:36 8.67
Welby 1:52 9.00
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Marshall said:
We get that here every day too ;-))
You're welcome, but we're not done here until you get a
query to work. Where do things stand now?
DrTominRI said:
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
:
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. ;-)
