Access Please help! Probably Simple

[RushG60]

Ok here is the deal;

In my table I have a list of employees name's going down in rows. At the top of each column I have a language (English, French, Spanish, German, etc).
Then for each field I have a Yes/No checkbox.

I want to create a query that will return the names of employees that speak a certain language.
I have created a form with the name of each language next to a checkbox.
So each on my query I have each language at the top (Field) and I set the criteria to look for a (Yes/-1) to return those names that match.

The problem is that; I only get the names returned that match EXACTLY what my criteria was for. For example if Jon D. speaks both English and Spanish (both clicked Yes on the table) and I query for people that speak English Jon D. does not get returned because he also speaks Spanish! I only get him if I query for people that speak English AND Spanish.

How can I make a criteria that will return anyone thats speaks the language I was searching for even if they speak additional langauges???

Thanks for the help!

 

[Rich]

You need to normalise your data first, you're trying to re-create a spreadsheet in access. Search here for articles on Normalisation/Normalization

 

[neileg]

Just to expand on what Rich said.

Instead of having a separate field for each language in the table, you need to create a new related table that has a new record for each employee and language. Then your task becomes very simple. You can also add an unlimited amount of languages without changing the table structure.

 

[RushG60]

Followup..

I'm not quite sure I understand..

So I should create a new table that has a record for each employee and the language they speak??

Something like?



James D. English Spanish French
Harold F. English German French

Can you point me to an example? I am just not sure how this would look in a table?

Thanks for your help!

 

[RushG60]

Thank you very much! I'm in a real bind with this problem. I will take a look at what you made and try and wrap my head around it...

 

[neileg]

Uncle Gizmo's example is not quite how I would have done it. The form doesn't deal with mutiple langauges.

Try this

 

[RushG60]

Hi Niel..
Thanks!

I'm looking at it now...
So this looks like a good way to add employees that have multiple language skills. What exactly does the qryLangs form do? Does it allow me to add languages?

 

[neileg]

frmLangs is the subform for frmEmps. I broke my own naming convention which should have called it subfrmLangs. You need a simple form to add new languages to tblLanguage.

Note that the relationship between employees and languages is a many-to-many relationship and tblEmpLang is known as a junction table, needed to implement the raltionship.

 

[Aquadevel]

I have to agree with Neil. What he was describing was much easier to use and utilize.

Aqua