Access User-Friendly Form

[INOPOOL]

Hi,
Can someone guide me step-by-step on how to create a form that when i search for a language, it will give me all the employees that speak that language?

 

[alansidman]

First, as I suspected your db is not normalized. You should have only one field for language and not five. You should set up a separate table for the languages, then have a one to many relationship between the employee table and the language table.

I think that you need to restructure your database so that it is normalized or you will continue to have issues.

Here is a write-up on normalization

http://www.mrexcel.com/forum/showthread.php?t=529033

This was all previously presented to you and we told you that there would be issues when you don't have a normalized database. I think that you should restructure your database.

Alan

 

[INOPOOL]

Alright...I have created three tables: name, personlanguage, and langID. What should I do now? Very lost. Thanks


http://www.box.net/shared/intj4f9isa

 

[alansidman]

Ok. I have looked at your db and you have a good start. I have to run out for the evening, but will revisit it later. Your tables need to be changed slightly and a search form created. Will post back before the end of the weekend.

 

[INOPOOL]

Thanks a lot for your help...I appreciate it!

 

[alansidman]

Here is what I have done for you. I fixed your tables so that they contain the appropriate fields and added autonumber IDs to all. I then created a query to list the names and languages. I then created a search form for you with a combo box lookup for the languages. You can search any language without typing them in. Look at the design of each table to see how they are arranged with no duplication of information. The query brings the data together.

I hope this helps you. The file is located at http://www.box.net/shared/yod5tyixj4

Alan

 

[INOPOOL]

Thanks for all your help! But what if i want a form like this:

email:
language 1
language 2
language 3
language 4
language 5

So if I type in "English" for language 1 and "Spanish" for language 2 and "French" for language 3, the employees who are trilingual and know how to speak all three languages will come up. I want to be able to search for multiple languages.

 

[alansidman]

You need to build a cross tab query to get those results.

Go back to datapig and look at his video on building cross tab queries.

Alan

 

[INOPOOL]

I watched the video and I don't know how to manipulate it so that it will work on my db the way I want it to. Can you please help me out? Thank you very much.

 

[alansidman]

I've taken you as far as I am capable of. I suspect that to achieve what you are looking for, you will need to develop a Listbox that allows you to select multiple language choices and have that convert into Field Headings in a query and with some VBA populate the query. I personally don't know how to do this. Hopefully, someone will offer you some help, but I would not wait. I would start with some research on Listboxes and variable field names in queries in a Google search.

Until then, you can run individual queries.

Remember, we are here to help you, but not do your work for you so some research may be in order.

Good Luck
Alan

 

[alansidman]

I had one more thought and have presented here:

http://www.box.net/shared/yod5tyixj4

I created five queries and then joined them in a UNION query to get the presentation you asked for. However, I was not able to get the languages if a person had more than one on the same line. Perhaps, someone else will be able to amend this for you. ie. if a person spoke French and English, two records would appear in the query results, not one, but they will appear one after the other.

Alan