Results 1 to 8 of 8

Thread: Query HELPP!

  1. #1
    Join Date
    May 2013
    Posts
    28

    Question Unanswered: Query HELPP!

    Hello Everyone,

    I was trying to make a query that will feed a form. Except I don't have any idea where to start. I currently have a table that has translations in it. The Fields are Product, Language, English String, ID String, and current translation. Now each product has their own set of languages and strings with translations. I have to figure out a way to see if any of the translations for the languages are different then other translations.

    So basically translations that mean AC in English should all have the same translation for each product. Now in the case that one product has a translation for AC thats lets say AC in French. But another product for the same translation in French for AC is ACC. I want a report to come back to me and let me know that there is an error and that one of the translations could be wrong.

    EDIT*
    Since post I have looked into making and unmatched query? I think this could help and fix my issue but I still can not figure it out.
    I want to be able to run a query that goes through the table and basically asks 3 questions about each record(I think this is what I want).
    1. Does Language Match?
    2. Does English String Match?
    3. Are Current Translations Different.
    If Current Translation Different But Language and English match then return those values so that I will be able to fix the miss match of translations.

    Thanks All,
    Hoping for Quick Reply
    Last edited by kpelkey151; 05-30-13 at 12:51.

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    Are you looking to compare all the records in your table to all other records? So if you have 100 records, you are comparing record 1 with the other 99, record 2 with the other 99, record 3 with the other 99 etc.? Or, do you have a specific record that you would like to compare to the other 99 records?

  3. #3
    Join Date
    May 2013
    Posts
    28
    Basically 1 record to the other 99. So Lets Say I Have 5 different products im selling. Each product has anywhere from 5-10 languages depending on where it will be sold. Now for each product and language there are string ids, and the English translation along with a translation into the language. So Lets Say Product 1, 2, and 3 are compatible with french. So that means I need 3 translations for each product. Now with multiple people changing things throughout the week one of those three translations might get changed to something wrong or have a typo. So Lets Say Product 1 and 2 french translations for You is vous. Well obviously Product 3 should have the same translation for You, but instead of vous, it has vvoius. I want to know that there is a problem with one of the translations since i currently have over 30,000 translations and plan on receiving 35,000 more in the upcoming week.

    Hopefully that clears something up.

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    I'm assuming you are using a form that will display the current record you would like to compare. If that is so, I believe a query like the one below should return the records that are in need of repair. The only trouble, I think you want to have an automatic way of comparing your entire table. At some point there needs to be a reference and if comparing your entire table to each other what becomes the reference?

    I'm not sure how things are set up in your DB, but if I were you, I wouldn't allow manual typing in the Translation or English String fields. I would create a table for words and have the users use comboboxes to select the word and whenever I need the translation I would pull it from that table. The table would contain: EnglishWord, OtherLanguage, Translation.

    Code:
    SELECT tblProducts.Language, tblProducts.Product, tblProducts.[English String], tblProducts.Translation
    FROM tblProducts
    WHERE (((tblProducts.Language)=[Forms]![tblProducts]![Language]) AND ((tblProducts.[English String])=[Forms]![tblProducts]![English String]) AND ((tblProducts.Translation) Not Like [Forms]![tblProducts]![Translation]));

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why check for matches or mismatches?
    thats duplicating data
    it sounds to me that your design is flaky
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2013
    Posts
    28
    This is the code that i ended up getting. It shows the differences. I used basically a copy of the fsame table to reference it agaisnt. However I am running into the problem now that it is giving me duplicates that dont exists more than once. Is there a way to remove these duplicates?

    By duplicates i mean that it is displaying the same product, english string, language and translation more than once in the report.


    Code:
    SELECT [Translations Full].Product, [Translations Full].Language, [Translations Full].ENG, [Translations Full].ID, [Translations Full].CurrentTranslation
    FROM [Translations Full] INNER JOIN [Translations Full] AS [Translations Full_1] ON ([Translations Full].ENG = [Translations Full_1].ENG) AND ([Translations Full].Language = [Translations Full_1].Language)
    WHERE ((([Translations Full_1].Language)=[Translations Full].[Language]) AND (([Translations Full_1].ENG)=[Translations Full].[ENG]) AND (([Translations Full_1].CurrentTranslation)<>[Translations Full].[CurrentTranslation]));
    EDIT***
    My design may be flaky but it currently is the best idea I have. Just learned access in last two weeks. I currently have translation issues where translations for the same English String does not match up and our linguists are very slow at getting the correct ones. I originally wanted to have a "Master Table" but I could not have that since there wasn't already one predefined translation for each string. However I have to check the current data for missmatches so i can create a report that can be sent out to figure out which translation is correct.
    Last edited by kpelkey151; 05-30-13 at 16:55.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A tdanslation of a phrase should be consistent. Store the translation once. And thdn store an associatin between that phrase and a product. Never store the same information twice, becUse if you do you start getting the problems you are now experiencing. Instead of fixing the problem (ie changing te design) you are sticking plaster on a festering sore that will bite you i the long term
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2013
    Posts
    28
    Well there is nothing I can do about this. I first wanted to have one table and link the translations to other places. But at my company there is no way of getting one translation. We send our translations out to companies which cost up near $1000 per word to get translated. With the data we already have there are multiple translations for different products that do not match. So this is an issue I have to deal with and my bosses know it too

    Code:
    Product	Language	ENG	ID	CurrentTranslation
    Trilogy	BRZ	Active PAP	ACTIVE_PAP	PAP ativa
    DV	BRZ	Active PAP	Active PAP	PAP ativo
    DV	BRZ	Apnea	Apnea	Apnéia
    Trilogy	BRZ	Apnea	APNEA	Apneia
    Merlin	BRZ	Apnea	APNEA	Apneia
    DV	BRZ	Apnea	Apnea	Apnéia
    DV	BRZ	Apnea Rate	Apnea Rate	Taxa de apnéia
    Trilogy	BRZ	Apnea Rate	APNEA_RATE	Taxa de apneia
    This is an example from my report that I want to try to fix. It shows up double of the same record sometimes. I am assuming this is from comparing the records multiple times or something.
    Is there a way to filter it out so there are no duplicates in my query/report?
    Last edited by kpelkey151; 05-31-13 at 11:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •