Help Update Query (?)

[gmaster]

Hi everyboby, i'm new in the DB world and i need a little help on this matter:I've got two similar tables and i need to transfer some fields from one to the other, but sending them in specific records...! I'll try to explain better with an example: I have a table with fields A, B and C (text fields with completely filled records) and another table with fields A, B and D (where only field B is filled with some values equals to those in the B field of the first table); I'd like to copy a part of the A field values of the first table in the second one, but keeping the association with the respective B value....Is it clear?? (I hope...anyway, sorry for my bad english...)


I'm trying with some update query, but once i selcted the values i want from the A i still cant send them in the right records...it keepd adding them as new record on the second table...Help!!

[burrina]

What exactly are the table names,field names and criteria for this query? You say Part,what exactly does that mean? As I see it you are going to need a If statement in you criteria for sure. Can you post more details? You say New Records, are you wanting to append to existing records and change their value?

[John_G]

Hi -

It sounds like you might have used an append query instead of an update query, but you are on the right track.

Put your two tables into the query design view, and then join them on Field B, since that is is the field common to both.

Make the query an update query, and then in the "Update To:" row for Field A of the second table put
[first Table]![Column A]. This will update all the values for Field A in the second table with values from the corresponding records in the first table.

If you only want to update some of the records, you can put criteria in as needed.

Now, this is only going to work properly if the relationship between the two tables is one-to-one. It also may not work if the query is not updatable, but that problem can usually be resolved by putting indexes on Field A in both tables.

HTH

John

[gmaster]

Hi, thanks everyboby!!
@john: nice tip, i'm trying to use an append query but i have some trouble..i 'mean, i know how to chose the record of interest from the first table but i don't know how to make a criteria in order to put those values in the right record of the second table (i.e. where B1 is equal to B2)

@burrina: Hi, i'll try to be more specific: i 'have two tables (t1 and t2), t1 is made of a dozen field (text) and B1 is the key. In t2 there's less field, but some of them are equal to some in t1 (let's say C2,D2,E2, are analog to C1,D1,E1) and B2 is also the key.
Now, t1 is periodically update by a person while t2 is also periodically updated but by an automatic system. This particular software takes SOME of the values that i can find in B1 and writes them in B2 associating them to a code (that is written in A2 instead). What i need at this point is something (query? VBA?) that can copy the values from those C1,D1,E1 fields and writes them in the corresponding C2,D2,E2 that have the same B....I hope i've been clearer now
I'm able to retrieeve the record i need from t1 (i.e. those records whose B is also in t2), but now i'm stuck 'cause i don't know how to copy the values in the right records of t2 (it keeps adding them as new records...)

[John_G]

Hi -

Your query cannot be an APPEND query - it has to be an UPDATE query. An APPEND query adds new records to a table; an UPDATE query changes (i.e. updates) data in existing records.

What you want to do is easily done in a query - how familiar are you with designing queries in query design view?

John

[gmaster]

Hi, i'm slowly getting familiar (as i said, i'm quite new in this field...) but this one was a problem for me.....don't know why...i think i have found a way to solve it using this code:

UPDATE T1 INNER JOIN T2 ON T1.B1 = T2.B2 SET T2.A2 = T1.A1 WHERE Transfer = TRUE [this is the criteria i use to select the record i need]

But now i have another question (hahah sorry...!), is it possible for access to do an inner join between tables from two different files?? Thanks everybody!!

[John_G]

Hi -

You can create links to tables from different databases (I assume that's what you mean?) without having to import the data. Once you have done that, you can use the linked tables in queries. There can be issues that arise if you want to use them in update queries, but it is hard to generalise.

One technique you can use to avoid having to write the update SQL in VBA code (it can be messy) is to create an update query in query design, and then execute the query in VBA : currentDB.Execute "UpdateQueryName", dbfailonerror

John