I've been tangling with this beast for about 3 or 4 days now and just had a breakthrough that I really don't want to forget. First off, a comment made by Erland Sommerskag in comp.database.ms-sqlserver about a more limited version of the problem pointed me in the right direction. Many thanks to him for that.

I'm storing questions and answers in a database which can obviously have question text and answer text. The users really want a "multilingual" view where a box will be shown for each question for each language to facilitate translation from English. The problem, though, is that until translation occurs there's only English text in the DB (mening there's only 1 box to show for each question).

I spent a couple of days trying continually more off the wall SQL statements to generate a query that would have NULL texts for those languages missing from each question, but would return only 1 entry for each language if there was already a text defined in that language. All this time, I've been working one level too low, however.

The solution was to start off at the question level, doing a cross join with the table that had all the languages defined in it (returning a table with NumQuestions x NumLanguages tuples/rows/records in it). Then I could do a LEFT OUTER JOIN with that result table to the questiontext table using a compound ON condition: 


  SELECT tblLanguage.ID as Lang_ID, tblQuestion.Item_Id, QuestionName,

         LanguageReference, QuestionText

  FROM   (tblQuestion CROSS JOIN tblLanguage)

  LEFT OUTER JOIN tblQuestionText 

  ON tblQuestionText.Item_Id = tblQuestion.Item_Id 

  AND tblLanguage.ID = tblQuestionText.LanguageReference

  ORDER BY tblQuestion.Item_Id, Lang_ID

The result is a table that has NULL LanguageReference and QuestionText if no language has been defined otherwise the defined QuestionText for the given Language. On the form, some trickery has to be put into the QuestionText_BeforeUpdate() event handler since adding a new language that doesn't already exists will cause run-time errors (MS Access thinks that adding a new questiontext breaks referential integrity because you're changing one of the component fields of the "Primary Key" on QuestionText from NULL to a value). A new record containing the user entered text is inserted into the questiontext table using an ADO Recordset. Then the user-entered edit is canceled using Form.Undo. The form is then requeried to display the newly entered text.