Results 1 to 12 of 12
  1. #1
    lamp123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    10

    Calculate field based on two fields as referenced in another table

    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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    The ComboBox wizard can assist in creating a Lookup ComboBox for the CategoryID control.

  3. #3
    lamp123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    10
    Can someone help me to write a code for Categoryid field. I am not that conversant with Access. Thank you.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Do you have a Form to enter records into this table?

  5. #5
    lamp123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    10
    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?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I would strongly suggest you use a form.

  7. #7
    lamp123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    10
    If I have a form, can someone help me with steps to fill in category_id as described above. Appreciate your response.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    The ComboBox wizard on the form can help you with your task.

  9. #9
    lamp123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    10

    Auto fill a field based on two fields from another table

    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

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    You mean like this?
    Attached Files Attached Files

  11. #11
    lamp123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    10

    Calculate field based on two other fields

    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.



    Quote Originally Posted by RuralGuy View Post
    You mean like this?

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-02-2013, 11:14 PM
  2. Replies: 8
    Last Post: 06-05-2012, 11:49 AM
  3. Replies: 34
    Last Post: 12-01-2011, 08:18 AM
  4. Replies: 5
    Last Post: 03-06-2011, 10:38 AM
  5. Calculate one field based on another one
    By Douglasrac in forum Forms
    Replies: 7
    Last Post: 11-18-2010, 01:30 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums