Help creating updateable query

[jsccorps]

Trying to create an updateable query, but, I keep getting "Operation must use
updateable query" message when trying to do the following:

Table A: Table B:
LOC VALUE LOC VALUE
A 10 A <blank>

B 40 B <blank>

B 20 C <blank>

C 70
C 5
C 80

Want to update VALUE in Table B to be the first occurrence of the VALUE in
Table A. For Example:

Table B:
LOC VALUE
A 10
B 40
C 70

 

[Klatuu]

Post the SQL so we can see what the problem is, please.

 

[jsccorps]

UPDATE TableB INNER JOIN TableA_Query ON TableB.LOC = TableA_Query.LOC SET
TableB.[VALUE] = [FirstOfVALUE];

 

[John W. Vinson]

Trying to create an updateable query, but, I keep getting "Operation must use
updateable query" message when trying to do the following:

Table A: Table B:
LOC VALUE LOC VALUE
A 10 A <blank>

B 40 B <blank>

B 20 C <blank>

C 70
C 5
C 80

Want to update VALUE in Table B to be the first occurrence of the VALUE in
Table A. For Example:

Table B:
LOC VALUE
A 10
B 40
C 70

Tables in Access are unordered "heaps" of data. There is NO SUCH THING as "the
first occurance", any more than you can uniquely identify the "first" potato
in a sack of potatoes.

If you're treating TableA as if it were a spreadsheet with row numbers...
you're in trouble.

Is there any other field in TableA which will allow you to unambiguously
identify which record you consider the "first"?

John W. Vinson [MVP]

 

[jsccorps]

Question (bear with me - i am a novice here). What is the function of First,
Last, Max, etc. in the Query Groupings? For example, I thought that for LOC
C with VALUEs 10, 70 and 5, then (using First) it would pull 10. If I used
Max, then it would pull the record with VALUE=70.

 

[John Spencer]

Max shows the LARGEST Value
Min show the SMALLEST Value
First shows the value for the first record found in the group. That is not
necessarily the first record entered nor is it necessarly the first value
you see when you look at the records in the table.
Last shows the value for the last record found in the group. That is not
necessarily the last record entered.
(So first and last will show you more or less a random value from the
group).

If you need the FIRST record from a group of records, you will probably need
to use a correlated subquery specifying the members of the group, the order
of the records, and a "TOP 1" in the select clause of the subquery. OR you
can use a ranking query and select the record(s) with a rank of 1.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Question (bear with me - i am a novice here). What is the function of First,
Last, Max, etc. in the Query Groupings? For example, I thought that for LOC
C with VALUEs 10, 70 and 5, then (using First) it would pull 10. If I used
Max, then it would pull the record with VALUE=70.

In my experience, First() returns the first record *in disk storage order* -
or, possibly, in the order in which Access happened to retrieve the records
from disk. You have absolutely no control over either of these. In practice,
First() is useful only if you don't care which record you want to see, and
just want to pick any arbitrary record; Last() is of no use whatsoever.

You're still assuming that LOC C values 10, 70 and 5 are rigidly stored in
that order and will always be retrieved in that order. Your assumption *is
wrong*. They're not, and you have no guarantee that (say, after compacting the
database) they won't come out as 5, 10, 70.

John W. Vinson [MVP]