I have a database that consists of text that is translated from
english into spanish and French. I have it setup so that there are 3
tables, one for each language and the text from the first record in
the english table corresponds to the translatd version on the same
record in the spanish and french table.
Example:
Record 1 on the English table has text that reads "Hi my name is John"
Record 1 on the Spanish table has text that reads "Hola me llamo es Juan"
Record 1 on the French table has text that reads the same thing in French..
I need to create a SQL search function that will allow the user to
enter a word or phrase and search the database (English Table) to find
any records containing that word or phrase and return not only the
text of those records but the corresponding text from the Spanish and
French tables as well..
Any Suggestions? Its probably a relatively simple search function, but I am stuck. |
Request for Question Clarification by
lotd-ga
on
20 Oct 2005 21:14 PDT
Hi Christopher1,
Which kind of database software are you using (SQL Server, Oracle, etc)?
Regards,
lotd
|
Clarification of Question by
christopher1-ga
on
21 Oct 2005 05:53 PDT
Lotd-ga,
I am trying to keep things simple and just use Microsoft Access 2000
for this database.
Do you think that it is not the right choice?
|
Clarification of Question by
christopher1-ga
on
21 Oct 2005 05:57 PDT
This translation database is only going to be used by about half a
dozen people, in order to store lines of text for advertisements that
have been already translated from English into Spanish and French. The
users would like to enter a word or phrase in English and have the
database present them with all the different lines of text that
include that word/phrase along with the corresponding Spanish and/or
French translations.
Does that answer your question?
|
Request for Question Clarification by
lotd-ga
on
21 Oct 2005 06:52 PDT
Hi Christopher1,
I only asked which database software you were using because the SQL
syntax can be different between them. Does each table have a column
with a record number? This is needed to be able to join the tables
once the text is found in the English table.
Regards,
lotd
|
Clarification of Question by
christopher1-ga
on
21 Oct 2005 07:20 PDT
Yes, each table has a column for an ID number that is used to join tables
Thanks
|
Request for Question Clarification by
lotd-ga
on
21 Oct 2005 07:38 PDT
Hi Christopher1,
I believe I have an answer for you. However, I won't post it as an
official answer until you get it working on your tables.
The query is based on the following three tables
Table Name: English
Columns: Record_ID, English_Text
Table Name: French
Columns: Record_ID, French_Text
Table Name: Spanish
Columns: Record_ID, Spanish_Text
The following query will prompt you for the English text and will
display the English French and Spanish for any rows that match all or
part of the entered English text.
SELECT English.English_Text, Spanish.Spanish_Text, French.French_Text
FROM (English INNER JOIN French ON English.Record_ID =
French.Record_ID) INNER JOIN Spanish ON English.Record_ID =
Spanish.Record_ID
WHERE (((English.English_Text) Like "*" & [English Search] & "*"));
Please modify it as necessary using your table and column names. If
you do have any trouble getting it to work, please respond with your
column and table names.
If this answers your question, please let me know and I will post it
as an official answer.
Kind regards,
lotd
|
Request for Question Clarification by
lotd-ga
on
25 Oct 2005 09:45 PDT
Hi Christopher1,
As I have not heard anything further regarding the problem, I assume
the solution provided answered your question. I would be grateful if
you could confirm whether this is the case or not.
Thanks,
lotd
|
Clarification of Question by
christopher1-ga
on
25 Oct 2005 10:16 PDT
Lotd-ga,
Sorry I haven't gotten back to you yet.
That was pretty much what I needed! The search worked with my existing
table structure! Go ahead and post as an official answer!
Thanks again!
Chris
|
