How to update "First" record only?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables that are related by id#. For example: Table one has three
records with the same id#. I want to take the First of those records (by id#
sequence) and put that value in a Table Two field.

In using a Update query, I had no luck getting the last record. So I
created a select query with the table one in it and the id#. In "totals", I
put "first". When run it has the record I want. Then in the Update query I
have the query and table two (related) shown. I put the field in table two
in the field and table row in the first column. In the "Update to" row, I
put it's reference: [Query5tst.FirstofChkAmnt] -(the field is chkamnt but it
appears as firstofchkamnt when it's referenced from the query. (Yes, both
fields i'm manipulating are the same - currency) When the update query is run
i get "operation must us an updatable query.....i've tried diff. things all
day and can't get it to update the table..... Any ideas?

Thanks in advance for your help.
 
I have two tables that are related by id#. For example: Table one has three
records with the same id#. I want to take the First of those records (by id#
sequence) and put that value in a Table Two field.
Click to expand...

Ummm... if they all have the same id# then what's the "id# sequence"?
If you have three records with ID# 8132, as far as Access is concerned
there is no order amongst them.
In using a Update query, I had no luck getting the last record. So I
created a select query with the table one in it and the id#. In "totals", I
put "first". When run it has the record I want. Then in the Update query I
have the query and table two (related) shown. I put the field in table two
in the field and table row in the first column. In the "Update to" row, I
put it's reference: [Query5tst.FirstofChkAmnt] -(the field is chkamnt but it
appears as firstofchkamnt when it's referenced from the query. (Yes, both
fields i'm manipulating are the same - currency) When the update query is run
i get "operation must us an updatable query.....i've tried diff. things all
day and can't get it to update the table..... Any ideas?
Click to expand...

No Totals query or any query involving a Totals operation is ever
updateable. The moment you put in a First or Max or Group By
operation, you lose updateability. That's just an Access limitation.
Note that the First() and Last() operations are very misleading; they
do NOT refer to the first-entered or last-entered records in any
reliable way! The First record is the first record *in disk storage
order* and Access gives you NO control over that order.

How do *you* determine, looking purely at the contents of the three
records (not their sequential appearance on the screen or on paper,
but only on the fields in the record) which one you want to update?
And are you *updating* - i.e. changing the values already stored in an
existing TableTwo record - or do you want to Append, create a new
record in TableTwo?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
the id# is a unique autonumber, primary index

John Vinson said:
I have two tables that are related by id#. For example: Table one has three
records with the same id#. I want to take the First of those records (by id#
sequence) and put that value in a Table Two field.
Click to expand...

Ummm... if they all have the same id# then what's the "id# sequence"?
If you have three records with ID# 8132, as far as Access is concerned
there is no order amongst them.
In using a Update query, I had no luck getting the last record. So I
created a select query with the table one in it and the id#. In "totals", I
put "first". When run it has the record I want. Then in the Update query I
have the query and table two (related) shown. I put the field in table two
in the field and table row in the first column. In the "Update to" row, I
put it's reference: [Query5tst.FirstofChkAmnt] -(the field is chkamnt but it
appears as firstofchkamnt when it's referenced from the query. (Yes, both
fields i'm manipulating are the same - currency) When the update query is run
i get "operation must us an updatable query.....i've tried diff. things all
day and can't get it to update the table..... Any ideas?
Click to expand...

No Totals query or any query involving a Totals operation is ever
updateable. The moment you put in a First or Max or Group By
operation, you lose updateability. That's just an Access limitation.
Note that the First() and Last() operations are very misleading; they
do NOT refer to the first-entered or last-entered records in any
reliable way! The First record is the first record *in disk storage
order* and Access gives you NO control over that order.

How do *you* determine, looking purely at the contents of the three
records (not their sequential appearance on the screen or on paper,
but only on the fields in the record) which one you want to update?
And are you *updating* - i.e. changing the values already stored in an
existing TableTwo record - or do you want to Append, create a new
record in TableTwo?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top