Linking fields

[INOPOOL]

So I have a table like this

Name: Language 1: Language 2: Language 3: Language 4:


How do I filter the table that when I define a language, for example, "Chinese," that it will pull up all the records of names of people who speak Chinese regardless if they put it in Language 1, Language 2, Language 3, or Language 4 field.

Thanks

 

[TomRenish]

you could make a query to read the table, and enter "chinese" for each column.

'       Language 1   Language 2   Language 3   Language 4
OR       "chinese"
                     "chinese"
                                  "chinese"
                                              "chinese"

You could make a parameter that would prompt you for the language of interest rather than hard-coding "chinese" as well.

 

[alansidman]

Your issue is greater than you have shown. Your table is not normalized. You should have two tables for this part of your database.
First table will contain name data and the second table will contain the language data. In the first table you will have a Primary key (autonumber) and the Name field which should not carry the fieldname of "Name" as that is a reserved word in Access. Your second table should have a a Primary key (autonumber) and a foreign key which will link to the name or primary key in the Name table. You will have a second field which lists all the languages. Now when you run a query or filter on "Chinese", all instances will appear.

Here is a link to database normalization

http://forums.aspfree.com/microsoft-access-help-18/relational-database-principles-208217.html

This should help you in setting up your database properly.

 

[Joe4]

This is a good example of how data that isn't normalized makes tasks harder than they have to be.

If you are doing this from the Query Builder, you will need to:
- add "Chinese" to Criteria line 1 under Language 1
- add "Chinese" to Criteria line 2 under Language 2
- add "Chinese" to Criteria line 3 under Language 3
- add "Chinese" to Criteria line 4 under Language 4

Having these on separate Criteria rows treats each criteria as an "OR" condition, which is what you want. If you put them all on the same line, they will be treated as "AND".

Now, this would be a lot easier if you normalized your data. Instead of having 4 fields for each person, 4 you have four record with the following fields:

Name
Language Number
Language

So the data might look like this:
Bob,1,English
Bob,2,Spanish
Bob,3,French
Bob,4,Chinese
Sue,1,Chinese
Sue,2,German
...

Now that criteria becomes real simple. Just place "Chinese" under the Language field.

Here is a link to a write-up on relational databases and data normalization: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx