How to add to a new field based on information in existing field?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm stuck and need advice on how to do the following:
I have a table that contains a field "Description" and I have added a new
field "DescriptionFr". I need a command that will search the existing
records, and place information into the new field based on a legend of sorts
that I will create. For example, if the record contained an entry in the
Description field that = "red", the command would then enter "rouge" into the
DescriptionFr field for that record. I would specify (somewhere?) the words
to search for and the appropriate translation to be entered into the new
field.
I will also need to be able to edit the "legend" as there could be new words
added in the future.
Any help would be greatly appreciated! I did try searching the boards, but
couldn't seem to find anything like this.
 
I think you'd be better to add a table with the French descriptions and use
a query to join the two tables. For example, Table1 includes fields TestID
and Description, Table2 includes fields Description and DescriptionFr. The
following query will list all records in Table1 with any matching records in
Table2. Note the user of an outer join - otherwise the query would exclude
any records from Table1 that had no matching record in Table2.

SELECT Table1.*, Table2.DescriptionFr
FROM Table1 LEFT JOIN Table2 ON Table1.Description = Table2.Description;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
I still really need some help with this - should I be asking in
another forum?
Click to expand...
1) people here are all volunteers, and most have daytime jobs too. An
answer often comes up quickly but not neccesarily

2) Not enough detail: what happens in there are two applicable key words
in the Description, say "red" and "large". Should the new field say
"rouge" because it came first, or "enorme" because it came last, or
"rouge et enorme"? Be aware that the last one will bring the whole weight
of the Relational Theory Police down on your head!

3) How big is the list of translating word - a dozen, a hundred or a few
thousand? How volatile is the old Description field -- will you have to
keep re-translating (despite the RT Police)?

B Wishes


Tim F
 
Thanks for the response, and I'm not trying to pressure anyone - just wasn't
sure if I posted in the appropriate forum. (I don't use Access much, and am
not familiar with all its functionality)
In response to your questions - the text in the description field shouldn't
look for key words, but rather to match the entire string of text. For
example, one Description contains "Country of Origin", I would match that
entire string and create the DescriptionFr of "Pays d'origine".
The translating list is probably less than a hundred phrases, and new ones
would be added only rarely.
As for the data in the Description field, I'm not sure how to answer that
one, other than to explain what I'm doing. I start with a db that contains
no records, only tables, relationships, etc. Each month I am sent a bunch of
text files which I import into the db. So, the records themselves change,
but the data in the fields I need to translate will generally be the same.
So the routine created will need to be run every month after the data has
been imported, but the routine itself shouldn't need to be changed.

I am also limited in that I can only add to this db, as the existing tables,
fields and relationships need to remain as they are now. The db is accessed
by another program that is coded to look for information based on the db's
existing layout.

Please let me know if I can clarify this further, and thanks again!
 
one, other than to explain what I'm doing. I start with a db that
contains no records, only tables, relationships, etc. Each month I am
sent a bunch of text files which I import into the db. So, the
records themselves change, but the data in the fields I need to
translate will generally be the same. So the routine created will
need to be run every month after the data has been imported, but the
routine itself shouldn't need to be changed.
Click to expand...

Matching long strings is going to be a real headache, but not impossible.
Your time would probably be best spent creating a new translation table,
like

Eng Fr
=== --
Country of Origin Pays d'origine
Red Rouge
White Blanc
Big Enorme
Large Enorme
etc. etc.

and then joining that to the table with all the imported descriptions in
it.
I am also limited in that I can only add to this db, as the existing
tables, fields and relationships need to remain as they are now. The
db is accessed by another program that is coded to look for
information based on the db's existing layout.
Click to expand...

One solution would be to re-educate whoever created the other program to
look for a query rather than a table... this may indeed already be
possible since tables and queries can sometimes be interchanged. The
advantage of that is that you don't have to do anything to the incoming
data at all -- the logical way is not to have an Access table at all, but
to make a link to the incoming text and join that to the translating
table, and expose the resulting query to the outside program. IYSWIM...

Hope that helps


Tim F
 
Back
Top