Larissa:
I think you may be confusing an 'update query' with a 'make table' query, or
with an 'append' query.
An 'update query' changes values in an existing table or tables. A 'make
table' query, as its name suggests, creates a new table using the results of
a query; an 'append' query inserts rows into an existing table using the
results of a query.
When it comes to using aggregating operator like MAX, MIN it sound to me like
you want to return these per patient, so you group the query by PatientID and
any other columns which don't change per patient, such as names. You really
need a unique identifier such as PatientID as names can be duplicated (in
fact I was once at a clinic where two patients turned up, both female , with
exactly the same first and last names and the same date of birth!). So, in
query design view you'd select Totals from the View menu (or whatever the
equivalent is in Access 2007 if you are using that) and leave the Total row
in the design grid as Group By for the PatientID, Firstname and LastName
columns, but change it in other columns to Min, Max, Avg or whatever
aggregation operation you want performed on the column in question. This
query will give you one row per patient, with the aggregated values in the
relevant columns.
If you want to create a table with these values to send to your colleague for
analysis then, while still in design view, select Make-Table Query from the
Query menu, enter a name for the table in the dialogue and click OK to close
it. The select Run from the Query menu. Confirm that you want to create the
table at the prompt and the new table will be created.
If you want to insert the rows returned by the query into an existing table
then instead of selecting Make-Table Query, select Append Query, select the
table to append to, and in the Append To row of each column select the column
to which the column is to be appended.
I hope I've understood what you are trying to do, but if not post back with a
more detailed description of what you want, both in terms of the data from
your current tables, and what you want to be in the table to be passed to
your colleague.
Ken Sheridan
Stafford, England
Thank you for your thorough answer. I unknowingly posted this three different
times, it looks like, because later searches for my question title weren't
returning anything and I figured that the posting had somehow failed to
upload.
The reason I need to have the max values is because we are in the analysis
portion of a small study, and I need to give our results to a statistician. I
wanted to put everything into a table and send it to her (max of variable A,
min of variable b, etc.) so that she can perform statistical analysis on the
data. I have about ten different variables that need to eventually be in this
small table, and I don't know of a better way to accomplish this goal than
the path that I've tried. Maybe it's just a fundamental misunderstanding of
the true purpose of an update query on my part. If you have another
suggestion, I'm definitely open to other ideas.
BTW I tried multiple things to try to get this to work. I joined the two
tables on two columns that appear in both: a unique patient identifier number
that is given to the patient on hospital admission, and the unique ID that
represents which hospital they were admitted to. I tried changing the join
types, and I also tried creating a query to calculate the max, and then
joining that query and my table to try to put in the values. I even made a
table of all of the max values and tried to make an update query work with
the two tables. Access remained stubborn and continued to give me the error
described. Not sure if this is enough info or too much, but that's the big
nutshell.
-Larissa
My guess would be that you are attempting to use a 'totals' query which gets
the value with MAX function to update a value in a field on a table. There
[quoted text clipped - 46 lines]
I appreciate your time. Thanks for looking at this post.
--
.
