adding field to table 1 from table 2

[avivit]

In ms-access:

I have 2 tables. Both contain "title" field.
I would like to add to table 1, one field from table 2,when the title is the same.

For exmple:
table 1:

title: title1
name: smith
address: add1

table 2:

title:title 1
id: id1

Wanted result in table 1:

title: title1
name: smith
address: add1
id: id1

How can I update table 1?
I prefer to keep table 1 as it is, ad only update then create a query
that will create a new table.
Btw - When I did try to use left outer join for that, I had more records
than I had in table 1, and table 1 was first in the left outer join query.
How is that?

I would like to keep table 1 and only update it, thus stay with the same number of records.
Is it possible to do in a query, or do I need vba, or maybe to use asp page?
Pls help.

Thanks

 

[PHV]

Something like this in SQL ?
UPDATE [table 1] SET id =
(SELECT id FROM [table 2]
WHERE [table 2].title = [table 1].title)
WHERE EXISTS(SELECT id FROM [table 2]
WHERE [table 2].title = [table 1].title)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[avivit]

Thanks,
A great solution.

But - It does not work for me somehow.
I built your query in the queries module, and tried to run from there.

I get an error:"Operation must use an updateable query",
and I am working on my local pc, not on server.
The field that is compared (in the where clause) is unique in both of the 2 tables, and it is the key.I use no relationships other than the the one created usinf queries.

Do you have any idea why?

Thanks

 

[PHV]

Have you write permission on the table ?
Your database is not read only ?
Have you permission on the directory to create the .ldb file ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[avivit]

Well,

In the proerties of the database - it is not read only.
*.ldb file is created all the time, when I open the forms.
And I do not know how to write permission to the table.

When I use permissions, I define them in the asp files,
which is not the case here, as I am only working on the database on my local pc.

I do not know what to do.

Thanks for your reply.

 

[avivit]

I found a site that explains the above problem in ms-access,
and I think it can help me but I am not sure what to do.
Can it help?

http://chrishiller.net/archives/databases/ms_access_operation_must_use_an_updateable_query.php

Thanks

 

[PHV]

Have you tried the DoCmd.RunSQL method ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884