How to fill lookup field based on values from another table

[lamp123]

ProblemInAccess.zipI have two tables.
Table One: Dancers, 6 fields. All fields except categoryid is filled manually using either form or grid.


ID firstname lastname skill state categoryid
1 John Edward Ballet California
2 Ed Thomson Tap London
3 Natasha Curtis Ballet Australia
4 Shen Willson Modern Kansas

Table Two: Categories
categoryid categoryname
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdon/Wales
4 Dancers/Ballet/UnitedKingdon/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/UnitedKingdon/Wales
8 Dancers/Tap/UnitedJingdon/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas

Ideally what I am looking for is when I am done entering the "skill" and "state" in the table "Dancers" it should automatically fill in field "categoryid" in the same table (Dancers). The categoryid field should be extracted from table "Categories". The system should extract "categoryid" field from table "Categories" if the field "path" in table "Categories" contain text equal to "Skill" and "State". Attached sample file.
Appreciate your help.

[orange]

I have access 2003 and can not open or use an accdb file.

I suggest you should normalize your tables. I don't know your environment but it seems you have:

Dancers who "perform/specialize" in DanceTypes and who live in Countries that in some cases are broken down into States/Cities.

There appears to be some confusion with your presentation/recording of Country/City/State. You have Australia, London, Wales and Kansas at the "same level" which will be a nightmare in forms and reports. You should define/describe Country so the rest of us understand exactly what you mean.

You should have a table for DanceTypes such as

id   dancetype
1    Modern
2    Ballet
3    Tap
4     another type
5     yet another

I would suggest a Table for Countries (recognized ISO Countries or something) so that spelling errors
(such as UnitedJingdon/London can be avoided).

Clearly identify what a State/Province/Territory is and adjust you data values accordingly.

Can a Dancer have more than 1 DanceType?

I would suggest you should also clearly define/describe what exactly a Category represents. It isn't clear to me.
Similarly, I'm not following where Skill fits in your scenario.
You may need a separate Skill table with values (meaningful to you and those you communicate with) like

Id    SkillLevel
1     Beginner
2     Level1
3     Proficient
4     Professional

Also you should investigate Junction Tables. You may want information such as
Dancer ( Fritz) has skill level (Proficient) in DanceType (Modern) and Lives in (Kansas, United States)
Dancer ( Fritz) has skill level (Beginner) in DanceType (Tap) and Lives in (Kansas, United States)

You have to understand what each of your Tables/Entities means and how they relate one to another.
The key to data base is to get your Tables and relationships designed so that they represent your "business".

Good luck with your project

[lamp123]

Thank you for your help. The sample file does not represent actual text in categoryname. What all I am looking for is filling up the categoryid field in table one from table two where field contents of "skill" and "state" are part of field in categoryname in table 2. Please forget about the actual contents of field "categoryname" at this point since actual values contain "Skill/Country/State/City". I want to get categoryid from table 2 and put it in categoryid field in table 1 where "state" and "skill" are part of field categoryname.

I am not sure if I am able to explain this. Skill and State are two words which are part of categoryname and I want to extract categoryid corresponding to that record. Let me know. I can save the file in Access 2003 format if you are ready to look at it. Appreciate your help.

[orange]

I will look at you mdb but I am still not clear on what you are trying to do.

Please describe Category in 2 or 3 sentences. Same for Skill.
Forget the your tables for the moment and just tell us the purpose of the database/application.
And what exactly do you need help with?

Do you have a jpg of your Tables and relationships?

[lamp123]

I have a table with two fields - categoryid and categorydescription.
I have another table with categoryid, word1, word2

[lamp123]

Let us not go into the details of what actually I have.
Table One: id, word1, word2, categoryid
Table Two: categoryid, categorydescription
When I insert a record in Table One with Word1 and Word2, I want system to automatically insert categoryid if Word1 and Word2 are part of field categorydescription. The system should get corresponding categoryid from table two and insert in table one.
How can we do this?