possible?

[Scubaman]

Good afternoon.

I have several tables. Is it possible to have a statement like this if
(tablenaam.tablefield="UK" then tablename.tablefield="Scubaman") I tried
to do so but I don't know if it's possible and second where to put such
statment. I guess in gueries, but not sure either.
Is there someone who can help me here?

Thanks
Scubaman

 

[Douglas J. Steele]

You're trying to rename that field? I don't think you can do that use DDL
(Data Definition Language). You'd need to add a new field, populate it with
the value from the old field, then delete the old field:

ALTER TABLE tablename ADD COLUMN Scubaman Text(50)
UPDATE tablename SET Scubaman = UK
ALTER TABLE tablename DROP COLUMN UK

Those are 3 separate commands: you can't bundle them together in a single
query.

It's trivial to rename the column using DAO, though:

CurrentDb().TableDefs("tablename").Fields("UK").Name = "Scubaman"

 

[Scubaman]

You're trying to rename that field? I don't think you can do that use DDL
(Data Definition Language). You'd need to add a new field, populate it with
the value from the old field, then delete the old field:

ALTER TABLE tablename ADD COLUMN Scubaman Text(50)
UPDATE tablename SET Scubaman = UK
ALTER TABLE tablename DROP COLUMN UK

Those are 3 separate commands: you can't bundle them together in a single
query.

It's trivial to rename the column using DAO, though:

CurrentDb().TableDefs("tablename").Fields("UK").Name = "Scubaman"

Thanks for the answer Douglas,

I am not trying to rename the field. What I want/would like is this.
If the table hotels I have a field called Land (country for you0 if that
field is filled with United Kingdom, I want/would like to have the field
Mentorname in the table Mentoren, automatically filles with Scubaman. I
think I have to create a quesry with the tables hotels and mentoren and
that some there I have to make a criterium but I haven't the slightest idea.
Hope I made myself clear now
Thanks

Scubaman

 

[Douglas J. Steele]

Douglas J. Steele wrote:
Thanks for the answer Douglas,

I am not trying to rename the field. What I want/would like is this.
If the table hotels I have a field called Land (country for you0 if that
field is filled with United Kingdom, I want/would like to have the field
Mentorname in the table Mentoren, automatically filles with Scubaman. I
think I have to create a quesry with the tables hotels and mentoren and
that some there I have to make a criterium but I haven't the slightest
idea.
Hope I made myself clear now
Thanks

Please describe the two tables in more details, especially how they're
related.

 

[Scubaman]

Please describe the two tables in more details, especially how they're
related.

In Hotels I have a field called Mentor. In the table Mentor I have
afield called Mentor too. I related Mentor ID to Hotels.mentor 1.
Relation type one to many
Hope that will help

 

[Douglas J. Steele]

In Hotels I have a field called Mentor. In the table Mentor I have afield
called Mentor too. I related Mentor ID to Hotels.mentor 1. Relation type
one to many
Hope that will help

To avoid confusion, I've renamed your tables tblMentor and tblHotels, and
call the Mentor field MentorId

One approach (not necessarily the best) is

UPDATE tblMentor SET Mentorname = "Scubaman"
WHERE MentorId IN (SELECT MentorId FROM tblHotels WHERE Land = "UK")

 

[Scubaman]

To avoid confusion, I've renamed your tables tblMentor and tblHotels, and
call the Mentor field MentorId

One approach (not necessarily the best) is

UPDATE tblMentor SET Mentorname = "Scubaman"
WHERE MentorId IN (SELECT MentorId FROM tblHotels WHERE Land = "UK")

Thank you Douglas,

I think I understand what you did. Perhaps a naieve question, where do I
put this sttement. In a query?
Thanks again
Scubaman

 

[Douglas J. Steele]

Thank you Douglas,

I think I understand what you did. Perhaps a naieve question, where do I
put this sttement. In a query?
Thanks again
Scubaman

Yes, that's the SQL associated with a query.