how to update 1 field, not disturb others.

[Guest_imported]

I need to update data in one field using another similar table that has the most current data. My goal is "updatedTable"

OldTable NewTable UpdatedTable
*name* *id* *name* *id* *name* *id*
bob 1 bob 1 bob 1
frank 2 frank 100 frank 100
james 3 james 3 james 3

Can anyone present me with a query that produces "updatedTable" from the two other tables. One thing that the above example does not show is that "oldTable" has alot more records that "newTable, don't know if that's a problem.
Thank you for any assistance.

Steve M.

 

[Guest_imported]

If my query has gone unanswered because, A: it's completely stupid. B: it's so difficult, no one has the answer. or
C: you can't bring yourself to answer a question posed by someone using the handle "turkeyTime", could an expert/moderator for this forum give me a reply and give me their opinion.

Steve M.

 

[LonnieJohnson]

I think your question may be a little unclear. Do you want to create a third table or do you want to modify table the new table? Also the UpdatedTable and the NewTable look exactly the same. I think we are not clear on what you are trying to do.

PS: It is turkeyTime, so the handle is fine (for now ;-) ) ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[K9Boxer]

Ok.... a few questions...
Are all three tables already in existance -or- are you looking to execute a 'Make Table' Query that will result in the new table? (Meaning that the rows will be replaced with each execution.)

 

[gwinn7]

There are two more obvious options...

1. Use an Update query

Example:
"UPDATE Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name SET Table2.ID = 100 WHERE Name = 'Frank';"

2. Use DAO/ADO to change the data.

Example(something like):

Dim rs as DAO.Recordset
set rs = Currentdb.OpenRecordset("SELECT Table2.ID FROM Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name" , dbopendynaset)
rs.FindFirst "Table1.Name = 'Frank'"
If Not rs.NoMatch then
rs.Edit
rs.Fields("ID&quot = 100
rs.Update
end if

 

[Guest_imported]

Ah, right. Yes I was very unclear, sorry about that and thanks for replying.

I have a table on my hard drive that contains about 14,000 records, of which approx. 1500 were appended from a table on our server which has 1500 records. 1 field in the table on the server is subject to change occasionally, so I need to somehow have the 1500 records from the table on my hard drive updated to reflect the changes to that field while leaving the other fields in the table undisturbed. Other fields in those 1500 records on the table on my hard drive like "phone number" etc. have been changed by yours truly while they have resided on my PC and I don't want those fields altered, only the one field I had mentioned. Please let me know if there is any other info you require and thanks again for your reply. I will be sure to be as concise as possible in the future.

Steve

 

[K9Boxer]

UPDATE Local, Server SET Local.ID = [Server].[ID]
WHERE ( (Local.Name = Server.name) and
(Local.ID <> server.id) );

Should be what you are looking for.

 

[Guest_imported]

OK thanks, i'll give it a shot. Happy holidays to everyone on the forum.

Steve M.