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

[LarissaR]

Hi, community,
I work for a hospital and we are collecting patient data for a small study.
In one table I have the population of patients I'm wanting to find data for.
In another table (pulled from another source) I have more data than I need
for each patient. I tried to use an update query to first find the max value
for each patient for one field, and then update the field in my small table
to match the results. When I do that, I get an error stating, "Operation must
use an updateable query". One additional piece: some of my patients will not
have data in the large table and will therefore not be represented in the
query when finding the max.

I'm sure I need to give you additional information, but I'm not quite sure
what, exactly. Do you have any suggestions for how I might run this query, or
give you more data to help?

Thank you for your time.

 

[NevilleT]

Hi Larissa

Suspect you probably cannot do it through a query. Suggest you use VBA to
loop through your small table and then check if the big table has a matching
record. If it does, you update the small table from the big one. Not sure
if you know enough VBA to do this but it will probably be the best way.

Neville Turbit
www.projectperfect.com.au

 

[Mark Andrews]

I created two tables (tblSmall and tblBig) and this is the sql I would use
to update records in tblSmall with matching records in tblBig.
UPDATE tblBig INNER JOIN tblSmall ON tblBig.BigName = tblSmall.SmallName SET
tblSmall.SmallPhone = tblBig.BigPhone, tblSmall.SmallAddress =
[tblBig].[BigAddress];

I am matching on Name between the two tables.
I am just updating two fields in the small table (SmallPhone and
SmallAddress)

I'm assuming you have one record for each patient in your small table?

I don't know how many records exist for each patient in your big table and
how you want to determine what data goes into small???
This is the question I would have to further refine the approach. Perhaps
an example would help.

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com

 

[John W. Vinson]

I created two tables (tblSmall and tblBig) and this is the sql I would use
to update records in tblSmall with matching records in tblBig.
UPDATE tblBig INNER JOIN tblSmall ON tblBig.BigName = tblSmall.SmallName SET
tblSmall.SmallPhone = tblBig.BigPhone, tblSmall.SmallAddress =
[tblBig].[BigAddress];

I am matching on Name between the two tables.
I am just updating two fields in the small table (SmallPhone and
SmallAddress)

I'm assuming you have one record for each patient in your small table?

I don't know how many records exist for each patient in your big table and
how you want to determine what data goes into small???
This is the question I would have to further refine the approach. Perhaps
an example would help.

Just a caution for the lurkers: note that a person's name is NOT a good choice
of a primary key or a linking field, as names are not unique. My name's John
W. Vinson and I once worked at a university where there was a Prof. John W.
Vinson. At the time the school used Social Security Numbers as a unique ID,
and somehow my SSN got onto his Credit Union account; it took two years to
clear up the mess with the Internal Revenue Service.

Use *unique*, meaningless ID's (SSN's don't qualify!), and make sure they're
applied properly!

 

[Mark Andrews]

Agree, this was just a quick example to show how the join/update should work
(I don't know what value this person is using to join the two tables with).
I was guessing she didn't have any numeric value that could be used to match
between the two tables?

Mark

I created two tables (tblSmall and tblBig) and this is the sql I would use
to update records in tblSmall with matching records in tblBig.
UPDATE tblBig INNER JOIN tblSmall ON tblBig.BigName = tblSmall.SmallName
SET
tblSmall.SmallPhone = tblBig.BigPhone, tblSmall.SmallAddress =
[tblBig].[BigAddress];

I am matching on Name between the two tables.
I am just updating two fields in the small table (SmallPhone and
SmallAddress)

I'm assuming you have one record for each patient in your small table?

I don't know how many records exist for each patient in your big table and
how you want to determine what data goes into small???
This is the question I would have to further refine the approach. Perhaps
an example would help.

Just a caution for the lurkers: note that a person's name is NOT a good
choice
of a primary key or a linking field, as names are not unique. My name's
John
W. Vinson and I once worked at a university where there was a Prof. John
W.
Vinson. At the time the school used Social Security Numbers as a unique
ID,
and somehow my SSN got onto his Credit Union account; it took two years to
clear up the mess with the Internal Revenue Service.

Use *unique*, meaningless ID's (SSN's don't qualify!), and make sure
they're
applied properly!

 

[Klatuu]

I am the last David Hargis in the line, but at one point in time there were
5 in our family. (guess we started recycling early)
David B. Hargis - grandfather
David B. Hargis, Jr - father
David Alton Hargis - Uncle
David Edwin Hargis - Cousin
and me.

I created two tables (tblSmall and tblBig) and this is the sql I would use
to update records in tblSmall with matching records in tblBig.
UPDATE tblBig INNER JOIN tblSmall ON tblBig.BigName = tblSmall.SmallName
SET
tblSmall.SmallPhone = tblBig.BigPhone, tblSmall.SmallAddress =
[tblBig].[BigAddress];

I am matching on Name between the two tables.
I am just updating two fields in the small table (SmallPhone and
SmallAddress)

I'm assuming you have one record for each patient in your small table?

I don't know how many records exist for each patient in your big table and
how you want to determine what data goes into small???
This is the question I would have to further refine the approach. Perhaps
an example would help.

Just a caution for the lurkers: note that a person's name is NOT a good
choice
of a primary key or a linking field, as names are not unique. My name's
John
W. Vinson and I once worked at a university where there was a Prof. John
W.
Vinson. At the time the school used Social Security Numbers as a unique
ID,
and somehow my SSN got onto his Credit Union account; it took two years to
clear up the mess with the Internal Revenue Service.

Use *unique*, meaningless ID's (SSN's don't qualify!), and make sure
they're
applied properly!

 

[LarissaR]

I wasn't sure if this posted. I've had a hard time searching and finding
things I've posted on, so I'm sorry I didn't respond earlier.

To answer the questions posed below: I created a join based on two columns
that appear in both tables: a unique patient number that is given to a
patient upon hospital admission, and the numeric ID of the hospital where
their hospital stay occurred. My little table does only contain one row per
patient.

Let's say I was looking for the maximum weight for each of the patients in
my little table, just to pick an easy example. Sometimes the weight wasn't
recorded at all, and sometimes it was recorded multiple times a day (usually
because the patient needed a concentration of a drug that depended upon their
body weight). I wanted to find the maximum weight for each patient, and I
wanted to keep this data in my little table. I had hoped that I could update
all of the blank records in my little table to reflect the maximum weight
results from the query. I tried a number of things, including changing the
join type and also running a query to show the max first, and then trying to
update my little table. Unfortunately I know nothing about VBA, and my SQL is
still pretty pathetic. If this isn't possible, I'll figure out a workaround.
It just looks so simple on paper . . .
Thanks again for your time!
-Larissa

Agree, this was just a quick example to show how the join/update should work
(I don't know what value this person is using to join the two tables with).
I was guessing she didn't have any numeric value that could be used to match
between the two tables?

Mark

I created two tables (tblSmall and tblBig) and this is the sql I would use
to update records in tblSmall with matching records in tblBig.
UPDATE tblBig INNER JOIN tblSmall ON tblBig.BigName = tblSmall.SmallName
SET
tblSmall.SmallPhone = tblBig.BigPhone, tblSmall.SmallAddress =
[tblBig].[BigAddress];

I am matching on Name between the two tables.
I am just updating two fields in the small table (SmallPhone and
SmallAddress)

I'm assuming you have one record for each patient in your small table?

I don't know how many records exist for each patient in your big table and
how you want to determine what data goes into small???
This is the question I would have to further refine the approach. Perhaps
an example would help.

Just a caution for the lurkers: note that a person's name is NOT a good
choice
of a primary key or a linking field, as names are not unique. My name's
John
W. Vinson and I once worked at a university where there was a Prof. John
W.
Vinson. At the time the school used Social Security Numbers as a unique
ID,
and somehow my SSN got onto his Credit Union account; it took two years to
clear up the mess with the Internal Revenue Service.

Use *unique*, meaningless ID's (SSN's don't qualify!), and make sure
they're
applied properly!

.

 

[(PeteCresswell)]

Per John W. Vinson:

Vinson. At the time the school used Social Security Numbers as a unique ID,
and somehow my SSN got onto his Credit Union account; it took two years to
clear up the mess with the Internal Revenue Service.

Use *unique*, meaningless ID's (SSN's don't qualify!), and make sure they're
applied properly!

I'd second the comment on SSNs.

Few years back, I wrote an application that dealt with both
people and tax-exempt organizations. The tax-exempt
organizations had an ID number that looked to me exactly like an
SSN. The properties of the people and orgs were the same too,
so we used "SSN" as primary key.

Oops.... turns out there was duplication. Mrs Smith's SSN was
the same as the "Edna Smith Charitable Endowment"'s "SSN".

In a "people-only" application there will also be duplications as
people supply the wrong SSN - either intentionally or by
accident.

 

[John W. Vinson]

I wasn't sure if this posted. I've had a hard time searching and finding
things I've posted on, so I'm sorry I didn't respond earlier.

To answer the questions posed below: I created a join based on two columns
that appear in both tables: a unique patient number that is given to a
patient upon hospital admission, and the numeric ID of the hospital where
their hospital stay occurred. My little table does only contain one row per
patient.

Let's say I was looking for the maximum weight for each of the patients in
my little table, just to pick an easy example. Sometimes the weight wasn't
recorded at all, and sometimes it was recorded multiple times a day (usually
because the patient needed a concentration of a drug that depended upon their
body weight). I wanted to find the maximum weight for each patient, and I
wanted to keep this data in my little table. I had hoped that I could update
all of the blank records in my little table to reflect the maximum weight
results from the query. I tried a number of things, including changing the
join type and also running a query to show the max first, and then trying to
update my little table. Unfortunately I know nothing about VBA, and my SQL is
still pretty pathetic. If this isn't possible, I'll figure out a workaround.
It just looks so simple on paper . . .

Annoyingly, any Totals query - even one that logically should be updateable -
ISN'T updateable. You can use the DMax() function to find the maximum; it'll
be slow and inefficient but if it's a one-off or infrequent action it should
work.