Results 1 to 2 of 2
  1. #1
    msasteel is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2014
    Posts
    7

    Compare/update query needed to auto fill table with categories based on a second table.

    URGENT!I have a database with hundreds of records in a main table called tblPARTS which has three fields - Part Number, Part Description and Category.


    I have another table, tblKEYWORDS, with two fields; a field called KEYWORDS which has words like "cable", "bracket", and "relay". The table has a second field called CATEGORY. The CATEGORY field has either the word ELECTRICAL or MECHANICAL associated with each of the KEYWORDS. So, for example, "bracket" would have "MECHANICAL" as its associated category. There are dozens of these entries in the KEYWORD table.

    The problem: I need to auto fill the parts table (tblPARTS) with the correct category by looking at the DESCRIPTION field in the PARTS table which is made of several words, e.g. "STAINLESS STEEL BRACKET - 3" X 2" ". I need to find the word BRACKET and fill in the CATEGORY field in the parts table automatically via a query. There are several hundred parts in the main table.

    I can do this easily using a standard update query, but it is limited to 10 IFF statements and there will be many more than this. Can I do this in VBA and if so, where do I start? I am an intermediate user of ACCESS, but I would appreciate as much detail as possible in any help one can give.

    Thank you very much for the assistance!

    Just wondering if anyone had any ideas here.... thanks.
    Last edited by msasteel; 11-21-2014 at 02:04 PM. Reason: Need help, please!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is there any possibility the DESCRIPTION field could contain multiple key words or none?

    Possible query:

    SELECT tblPARTS.*, tblKEYWORDS.* FROM tblPARTS, tblKEYWORDS WHERE [DESCRIPTION] LIKE "*" & [KEYWORDS] & "*";

    or may be a way to use DLookup() in query but I gave up on it.

    If you want to provide db for analysis and testing, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 25
    Last Post: 10-15-2014, 11:01 AM
  2. Replies: 3
    Last Post: 12-24-2013, 04:20 PM
  3. Replies: 6
    Last Post: 07-12-2013, 01:07 AM
  4. Combo Box auto fill and update table
    By memmons in forum Access
    Replies: 4
    Last Post: 09-27-2012, 11:58 AM
  5. Replies: 5
    Last Post: 01-20-2011, 11:36 PM

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