Page 1 of 4 1234 LastLast
Results 1 to 15 of 54
  1. #1
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138

    Show words that is in table to a query


    Good morning

    I need your help please

    I have about 10 tables with different info in them.

    on one table Wordlist (only 2 columns "ID" and "Fword") I want to use Fword. I have all the words of my language in it.

    Now every day I add new inf into some of the tables (Blokkies (3 columns "1","2","3") tblSynonyms (1 column "theword",) Gesegtes (two columns), Afkrortings (2 columns) and a few others.

    Now here is what I want to know how to do the following

    When I add new data to a table say "Blokkies" and I run this new query, it must look if the word is already in table wordlist. if not is must show me the word or words that is not in the wordlist table.
    Last edited by hendrikbez; 11-01-2019 at 03:46 AM.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    When I add new data to a table
    what would this data look like? a single field? multiple fields?

    Perhaps a function something like this which returns a comma separated string which you can use as a rowsource to a valuelist listbox or combo

    Code:
    function WordsNotInTable(s as string) as string
    dim rs as dao.recordset
    dim sqlStr as String
    
    'convert to css, removing any double spaces (this will need to be a loop if there are triple spaces)
    s=replace(trim(replace(s,"  "," "))," ",",")
    
    'get words that exist
    sqlStr="SELECT words FROM wordlist where words in (" & s & ")"
    set rst = currentdb.openrecordset(sqlStr)
    
    'remove found words from s
    'add commas to front and end to make comparison easier and avoid 'words within words'
    
    s="," & s & ","
    while not rs.eof
        s=replace(s,"," & rs!words & ",",",")
        rs.movenext
    wend
    
    'tidy up
    rs.close
    set rs=nothing
    WordsNotInTable=mid(s,2,len(s)-2) 'may need a bit of work if no words returned
    debug.print WordsNotInTable
    
    end function

  3. #3
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    I have edit my first post, before I saw your replay.

    The table "wordlist" have only one column with words in it.

    most of the other tables have more than 1 column in it.

    I want the query to look at all the tables with all the columns, then it must show all the words that are not in table "wordlist"

    then I will add them to table "wordlist"

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the function i provided works with one field value based on your requirement 'When I add new data to a table'.

    most of the other tables have more than 1 column in it.

    If you have multiple field values then either use the function multiple times or modify it to take multiple columns

    I want the query to look at all the tables with all the columns, then it must show all the words that are not in table "wordlist"
    a query cannot do this. And this requirement is completely different to
    When I add new data to a table say "Blokkies" and I run this new query, it must look if the word is already in table wordlist
    I don't have time to go around the houses - please be clear about exactly what you want - provide some clear examples

    I have a lot of work to do today for paying clients so cannot devote any more time to this at the moment - good luck with your project

  5. #5
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Hi Ajax how are you.

    in table wordlist with 1 column (fword) I have all words that should be in my language (eg. Peter, home, church) there is 450,000 word in this list, but there can be new words that I do not have.

    in table blokkies with 3 columns ("1","2","3"). so in column 1, 2 or 3 I may have a word "phone" that is not in table wordlist. when running query it must then show me the word "phone"

    in table tblSynonyms with 1 column ("theword") i may have a word "keeper" that is not in table wordlist. when running query it must then show me the word "keeper"

    There are more tables with about 1 to 3 columns in each.

    So if the word "Phone" or "Keeper" is not in table wordlist it must show when query is run that words "Phone" or "Keeper" is not there, then I can add them to wordlist.

    Hope this make more sense

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @Hendrikbez

    What do you not understand from what I have told you? And you have not said how you want the data presented or what you want to do with it. You have not provided example data and the outcome required

    It makes it extremely difficult and time consuming to help you

    best I can suggest is for Blockies you might have a query

    Code:
    SELECT WordsNotInTable([1]) & "," & WordsNotInTable([2]) & "," & WordsNotInTable([3])
    FROM Blockies
    note naming fields with numbers is a bad idea

    There is no point me responding again unless you can provide what has been asked for and, since I have provided a possible solution, what is wrong with the solution

  7. #7
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    OK, I want the info to be displayed in a table, I am looking at what you have Givin to me, but am just answering your questions

  8. #8
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    I have used your code, but it is giving me compile error on
    Code:
    set rst = currentdb.openrecordset(sqlStr)
    Variable not defiend

    But when I run my query
    Code:
    SELECT WordsNotInTable([1]) & "," & WordsNotInTable([2]) & "," & WordsNotInTable([3])
    FROM Blokkies
    I am getting this error
    Compile error. in query expression "WordsNotInTable([1]) & "," & WordsNotInTable([2]) & "," & WordsNotInTable([3])'

    Only need one column to see all the words

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    compile error - my mistake I dimmed rs, not rst

  10. #10
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    I have changed all the rs to rst, but getting error now "run-time error '3061' Too few parameters expected 4

    Code:
    Function WordsNotInTable(s As String) As String
    Dim rst As dao.Recordset
    Dim sqlStr As String
    
    'convert to css, removing any double spaces (this will need to be a loop if there are triple spaces)
    s = Replace(Trim(Replace(s, "  ", " ")), " ", ",")
    
    'get words that exist
    sqlStr = "SELECT words FROM wordlist where words in (" & s & ")"
    Set rst = CurrentDb.OpenRecordset(sqlStr)
    
    'remove found words from s
    'add commas to front and end to make comparison easier and avoid 'words within words'
    
    s = "," & s & ","
    While Not rst.EOF
        s = Replace(s, "," & rst!words & ",", ",")
        rst.MoveNext
    Wend
    
    'tidy up
    rst.Close
    Set rst = Nothing
    WordsNotInTable = Mid(s, 2, Len(s) - 2) 'may need a bit of work if no words returned
    Debug.Print WordsNotInTable

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    where? I notice you have not changed the field name for your word field in your wordlist table - have you modified your table?

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also just realised we need to add single quotes here

    s = Replace(Trim(Replace(s, " ", " ")), " ", "','")

    and here


    sqlStr="SELECT words FROM wordlist where words in ('" & s & "')"

    and also here


    s="," & replace(s,"'","") & ","

  13. #13
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Quote Originally Posted by Ajax View Post
    where? I notice you have not changed the field name for your word field in your wordlist table - have you modified your table?

    Sorry Ajax I do not understand what you mean here. is this on the function code or the query code.

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you said you had a error - is it in the function? or when you run the query

    you can easily test the function without running the query.

    And please be good enough to answer my question about the field name

  15. #15
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Ajax I will look at it tommorow, in bed now, I did ask you about the field name, not sure where to change it. That is what I was asking must it be changed in the function code or the query code. Not sure where to change it, but thank you for helping me.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to change words in a column in a table
    By hendrikbez in forum Access
    Replies: 1
    Last Post: 06-21-2016, 02:29 AM
  2. Replies: 8
    Last Post: 04-14-2014, 07:26 AM
  3. converting number into words in query
    By joshynaresh in forum Queries
    Replies: 3
    Last Post: 04-02-2014, 08:17 PM
  4. Replies: 6
    Last Post: 09-01-2013, 08:17 PM
  5. Query to find exactly matched words in Access
    By petercheng in forum Queries
    Replies: 5
    Last Post: 01-13-2012, 07:12 AM

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