Calculate field based on two fields as referenced in another table

[lamp123]

I have two tables. Table One: Dancers, 6 fields. I want to fill categoryid field as I create the new record based on contents of field skill and state if they are present in another table called Categories.

Table One: Dancers
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/KansasAttachment 7937

[RuralGuy]

The ComboBox wizard can assist in creating a Lookup ComboBox for the CategoryID control.

[lamp123]

Can someone help me to write a code for Categoryid field. I am not that conversant with Access. Thank you.

[RuralGuy]

Do you have a Form to enter records into this table?

[lamp123]

No. I was just entering information in a grid (which is displayed when I double click on table name). I am hoping when I enter value for "skill" and "state", a categoryid will be filled (may be as claculated field). Or Do I have to have form?

[RuralGuy]

I would strongly suggest you use a form.

[lamp123]

If I have a form, can someone help me with steps to fill in category_id as described above. Appreciate your response.

[RuralGuy]

The ComboBox wizard on the form can help you with your task.

[lamp123]

I am attaching a MS Access Database file. There is a form and I want to fill in categoryid field in table "Dancers" if the field "skill" and "state" are part of the text field "categoryname" in table "Categories". I will really appreciate if you can help me since I do not know how to use any advance techniques in Access such as query, combo box or anything like that.ProblemInAccess.accdb

[RuralGuy]

You mean like this?

[lamp123]

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". I am not sure how you got that and I really do not want any dropdown on category path in the form. Let me know if I am able to tell you my requirement.

You mean like this?

[RuralGuy]

Since you do not seem to wish to utilize features built into Access then it will probably require some VBA to achieve your desired result. If you do not have the VBA skills then you will need to do some studying.