Multi Language Order Report

[Mark]

I am trying to design a report which is capable of being printed in
different languages, the report is a printout of an order. This involves 4
tables tblOrder, tblOrderDetail, tblLanguage and tblProduct. tblOrder has a
foreign key LanguageID from tblLanguage. tblOrderDetail has the foreign key
productID. In tblProducts I have 3 product description fields,
ProductDescUK, ProductDescFR and ProductDescES. What I need is for when an
order is printed where the LanguageID is Spanish I need the ProductDescES to
print as the product description on the report. I have tried a couple of
ways but at the moment I am struggling. There is no way to link the
ProductDesc and the LanguageID and I thought that my table structures need
to be adjusted but I have the adventureworks sql server sample and that has
the same kind of tblProducts fields as I have. Any suggestions?

thanks

Mark

 

[SA]

Mark:

To make this easy (and position for more languages) re-design your tables.
Create a new table called something like ProductDescriptions with a
multi-field primary key of the productID and then a language ID and a
single field for the product description. so that rather than trying to
pick up a different field, you are simply using joins on the two fields to
pull the proper product desciption from a single field.

If that is too big a hassle, then you can use IIF statements in your query
like so:

ProdDesc: IIF([LanguageID]=1,[ ProductDescUK],
IIF(([LanguageID]=2,[ProductDescFR],[ProductDescES])

The problem here is its slower to do it this way and if you ever add more
languages, then you've got to modify code.

 

[Mark]

Thanks for your reply. I did consider revising my table structures but
opted for your second suggestion due to deadlines and the fact that the
customer shouldn't (famous last words) need any other languages. Instead of
IIF, I used the Switch function.
Switch([LanguageID]=1,[ProductDescEn],[LanguageID]=2,[ProductDescES],etc

Thanks again

Mark

Mark:

To make this easy (and position for more languages) re-design your tables.
Create a new table called something like ProductDescriptions with a
multi-field primary key of the productID and then a language ID and a
single field for the product description. so that rather than trying to
pick up a different field, you are simply using joins on the two fields to
pull the proper product desciption from a single field.

If that is too big a hassle, then you can use IIF statements in your query
like so:

ProdDesc: IIF([LanguageID]=1,[ ProductDescUK],
IIF(([LanguageID]=2,[ProductDescFR],[ProductDescES])

The problem here is its slower to do it this way and if you ever add more
languages, then you've got to modify code.
--
SA
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

I am trying to design a report which is capable of being printed in
different languages, the report is a printout of an order. This involves
4 tables tblOrder, tblOrderDetail, tblLanguage and tblProduct. tblOrder
has a foreign key LanguageID from tblLanguage. tblOrderDetail has the
foreign key productID. In tblProducts I have 3 product description
fields, ProductDescUK, ProductDescFR and ProductDescES. What I need is
for when an order is printed where the LanguageID is Spanish I need the
ProductDescES to print as the product description on the report. I have
tried a couple of ways but at the moment I am struggling. There is no way
to link the ProductDesc and the LanguageID and I thought that my table
structures need to be adjusted but I have the adventureworks sql server
sample and that has the same kind of tblProducts fields as I have. Any
suggestions?

thanks

Mark