Re: complex filter and calculations in access

I hope you didn't think I had abandoned you, but I've been
out of town for the past week.

The M in M.fieldname is just an alias for the full table
name as specified by the "As M" in the FROM clause. Aliases
are often used to shorten the query's SQL statement and make
it easier to read. Although you're not at this point yet,
when using a subquery on the same table as the main query,
then at least one of the FROM clauses must use an alias.

As you have already found, Me is only used with VBA code in
a Class Module to refer to the module's class object (e.g. a
Form).
--
Marsh
MVP [MS Access]


DrTominRI wrote:
It worked perfectly, Thank you!

Now, as you said, I can translate one field into another, replace infection
with deaths.
I think what I need to learn more about is the correct commands and syntax.
I actually used to write code in Fortran in th 80's when I was in college.
where can I find our more about the "M." and "Me." parts? (I got the Me.
from the other question I was working on - which is solved now) what do they
mean?


"Marshall Barton" wrote:

Let's try this query as an example:
_____________________________
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}]
______________________________

First create a new query without selecting a table and
without entering anything, switch to SQL view (View Menu).

Then Copy and Paste the above SQL statement over the top of
whatever is in the SQL window. The names you need to
replace with your names are contained in { }. Remove the
{}s but leave the []s.

Once you are sure you have the correct names, try to run the
query by switching to data*** view (View menu), if you get
an error message, click OK and it will usually highlight
something at or near the error in the query. If you are
prompted to enter a value, take careful note of the message
and fix the spelling of whatever it is promting for.
Hopefully, sooner, rather than later, the will run and you
will see the data in *** view. When you are done looking
at the query's results, switch to Design view. It might be
easier for you to follow the graphical representation than
it is for you to decipher the cryptic SQL view.

I must be in a good mood today, so go ahead and send me a
compacted copy (Tools - Database Utilities - Compact menu)
of your database using the from address in this post.


DrTominRI wrote:
My problem is that I don't speak enough "access", or at least not fluently
I do have a fair idea about what your describing but I am not sure what and
where to type it. What part of what your writing should be copied verbatum
and what is meant to represent something else?

where we stand right now is that I still have no clue as to what to do.
I have been trying to create little test databases to no avail

Is there away to create this relationship through a wizard or the event
builder?
Is there a way I can send you a piece of the DB or a screenshot or something?

"Marshall Barton" wrote:

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 wrote:
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


"Marshall Barton" wrote:
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. ;-)





.