Table Layout/Field lookup

[hughess7]

Hi all

I have a table in Access which holds a list of codes (104 records), each
code has Issue text translations for 10+ countries. Currently it is a flat
table with:
Code Issue A B C
each letter representing a different country.

Is this the best way to have the table, or should it be relational with one
table for 104 codes and one table containing the translations (ie if 10
countries this would results in 1040 records).

If done the first way I am struggling to be able to extract just one
countries Issues. I have a table holding the current country code though and
the field names match this. Is it possible to use a field name to identify
which column of data you want to look at. ie find the field name that matches
the letter in CurrentCountry field.

Happy New Year to all!

Thanks in advance for any help.
Sue

 

[Allen Browne]

Without question, you need the 3 tabes: Codes, Countries, and the junction
table between them.

 

[Brendan Reynolds]

Responses in-line below ...

Hi all

I have a table in Access which holds a list of codes (104 records), each
code has Issue text translations for 10+ countries. Currently it is a flat
table with:
Code Issue A B C
each letter representing a different country.

Is this the best way to have the table, or should it be relational with
one
table for 104 codes and one table containing the translations (ie if 10
countries this would results in 1040 records).

It should be relational with two table, yes.

If done the first way I am struggling to be able to extract just one
countries Issues.

SELECT Code, Issue, Translation FROM Issues INNER JOIN Translations ON
Issues.Code = Translations.Code WHERE Translations.Country = [Which
Country?]

The above example makes the following assumptions ...

Issues is the name of a table containing the code and the description of the
issue.

Translations is the name of a table containing the code, the translated
description, and a country name or code.

BTW: We probably should be talking about languages rather than countries, in
that some countries will use the same translation as other countries - for
example the many English-speaking or Spanish-speaking countries - and some
countries might require more than one translation - for example Belgium or
Switzerland.

I have a table holding the current country code though and
the field names match this. Is it possible to use a field name to identify
which column of data you want to look at. ie find the field name that
matches
the letter in CurrentCountry field.

Yes you could join join that table to the query above, but I don't see the
purpose of that. The example query would prompt the user for the country
code when run, or could be easily modified to refer a country code entered
into a text box on a form. Here's a link to the on-line help topic on
parameter queries ...

http://office.microsoft.com/client/...rt=2&ns=MSACCESS&lcid=2057&pid=CH100645771033

 

[hughess7]

Thanks to both replies, I suspected that you would tell me to split the
tables and make it relational. It is just that the flat table way has less
records and there is no duplication etc so I wasn't 100% sure it was
necessary to change it.

The system is developed as one database containing all countries
translations etc but when we distribute to our staff we send country specific
ones out, the only difference is the current country has been set for them so
all reports etc work for that particular country without any user
intervention. Hence the need for the current country table. But yes you are
right, I should have said language rather than country really as we have BF
for Belgium France and BN for the dutch side and three different ones for
Switzerland.

Thanks
Sue

Responses in-line below ...

Hi all

I have a table in Access which holds a list of codes (104 records), each
code has Issue text translations for 10+ countries. Currently it is a flat
table with:
Code Issue A B C
each letter representing a different country.

Is this the best way to have the table, or should it be relational with
one
table for 104 codes and one table containing the translations (ie if 10
countries this would results in 1040 records).

It should be relational with two table, yes.

If done the first way I am struggling to be able to extract just one
countries Issues.

SELECT Code, Issue, Translation FROM Issues INNER JOIN Translations ON
Issues.Code = Translations.Code WHERE Translations.Country = [Which
Country?]

The above example makes the following assumptions ...

Issues is the name of a table containing the code and the description of the
issue.

Translations is the name of a table containing the code, the translated
description, and a country name or code.

BTW: We probably should be talking about languages rather than countries, in
that some countries will use the same translation as other countries - for
example the many English-speaking or Spanish-speaking countries - and some
countries might require more than one translation - for example Belgium or
Switzerland.

I have a table holding the current country code though and
the field names match this. Is it possible to use a field name to identify
which column of data you want to look at. ie find the field name that
matches
the letter in CurrentCountry field.

Yes you could join join that table to the query above, but I don't see the
purpose of that. The example query would prompt the user for the country
code when run, or could be easily modified to refer a country code entered
into a text box on a form. Here's a link to the on-line help topic on
parameter queries ...

http://office.microsoft.com/client/...rt=2&ns=MSACCESS&lcid=2057&pid=CH100645771033

Happy New Year to all!

Thanks in advance for any help.
Sue