Update query error: "Operation must use an updateable query."


L

LarissaR

Hi, community!
I work for a hospital and we're collecting patient data for a small study. I
have a table of patients that I want data for, and I have another table that
contains more information than I need. The tables are joined by the number we
give to the patient (new for each admission) and by the number code for the
hospital where they stayed. I've run a query on the "heaps of data" table to
find the maximum value for a field for each patient, but when I try to put
that maximum value into my small set of patients by using an update query, I
get the error, "Operation must use an updateable query". Not every patient in
my small table will have a maximum value, so their records will be left as
(null). I'm sure I need to give you more information than that, but I'm not
quite sure what.

I appreciate your time. Thanks for looking at this post.
 
Ad

Advertisements

J

Jerry Whittle

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Show both the select query that works, and the
update query that gives you the error message.
 
L

LarissaR

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
 
Ad

Advertisements

L

LarissaR

Ken,
Thank you again for your time.

From my understanding, Append will add records to the bottom of an existing
table. In my case, I have an existing table with columns for the patients'
unique IDs, the ID of the hospital they admitted to, and then I need things
like their max weight, age, date of discharge, gender, and a few other
measures that are difficult to explain so I'll leave them out. Essentially,
I'm trying to create a patient summary table, but the values need to be
filled in.

The difficulty is that there is incomplete information, which then causes
problems when running a multiple-value query. You can imagine if you're
looking at 350 patients and 50 of them are missing the data for their
weights, and a different 30 patients are missing a date of discharge, that's
80 patients who won't have values returned (I'm sure I could do something
with different types of joins, but I haven't gone that direction yet). I'm
trying to create a table that will summarize each patient, including showing
nulls when appropriate.

So the big idea I thought would be simple to do (don't we always?) would be
to get the max (or min, or first, or whatever since it depends on the
variable) value for each patient for each variable from our ginormous data
table (seriously, millions upon millions of records) and put it into my
little table whenever the patient ID and the facility ID match. Once
everything is completed in the summary table(as much as we're able), I copy
it into an Excel file and send it to the statistician. She needs it set up in
a certain way, with one row per patient and all of their variables in
columns. Then we can do regression analyses or other statistical trials.

I don't know if that's explained anything in a different or more thorough
way. It sounds like I might need to find another approach. I welcome your
thoughts.
-Larissa

KenSheridan via AccessMonster.com said:
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.

--
Message posted via AccessMonster.com


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top