access query

  • Thread starter Thread starter David Carolla
  • Start date Start date
D

David Carolla

New Access user here, i've got two databases, and what I
need to do is; in database1, if field1 and field2 match
field1 and field2 in database2, I need to replace
database1 field1 with field3 from database2. Any help
would be greatly appreciated.

Thanks
 
David said:
New Access user here, i've got two databases, and what I
need to do is; in database1, if field1 and field2 match
field1 and field2 in database2, I need to replace
database1 field1 with field3 from database2. Any help
would be greatly appreciated.
Click to expand...

By 'database' do you mean a set of data, consisting of several rows
(records), in every row the same columns (fields)? We call that a table.

You want to change data; use an UPDATE query for that. Maybe I am
misleading you here, but most of the actions and movements around data
are accomplished using queries. INSERT will add records, DELETE will
remove them, SELECT will show them.

Now, you need to change table1.field1 into (the value in) table2.field3,
under the condition that both field1 and field2 in these tables match.
SQL writes this as

UPDATE table1 SET field1 = table2.field3 WHERE table1.field1 =
table2.field1 AND table1.field2 = table2.field2

Now, I am truely misleading you, because this will not work. Access
doesn't want to update when two tables are linked. Try it.

You can use a procedure that takes every record in table1, compares
these fields, and if a match is found changes the appropriate values. By
'procedure' I mean a bunch of Visual Basic code lines. I could spew this
out if need be. But before I do, is there somebody who will show *me*
how to use an UPDATE this way in Access?
 
New Access user here, i've got two databases,
Click to expand...

Jargon alert: Two *DATABASES* - separate .mdb files containing
multiple tables, forms, reports and other objects? Or two *TABLES*?
I'm guessing the latter.
and what I
need to do is; in database1, if field1 and field2 match
field1 and field2 in database2, I need to replace
database1 field1 with field3 from database2. Any help
would be greatly appreciated.
Click to expand...

Create a Query adding Table1 and Table2 to the query grid. Join the
two tables, joining Field1 to Field1, Field2 to Field2. Change the
query to an Update query and on the Update To line of the field you
want to update type

[Table2].[Field3]

Run the Query by clicking the ! icon.

Note that you must have a unique Index, such as a Primary Key, on the
combination of Field1 and Field2 for this to work.
 
Back
Top