Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Access 2000 - Update Query Based on another Table

    Hi Everyone ~ Been years since I've been here!

    Okay, I don't even know if this is possible but here is what I am attempting to do:

    Product_Table (about 40,000 records)
    ItemName
    Description
    Specifications
    DoNotSell
    DoNotSellReason

    Originally I only had about 20 items I could no longer sell so I was doing it manually by an update query:

    UPDATE Product_Table SET Product_Table.DoNotSell = "Do Not Sell", Product_Table.DoNotSellReason = "ABC Worldwide"
    WHERE ((([Product_Table].[ItemName]) Like [Enter Name]) Or (([Product_Table].[Description]) Like [Enter Name]) OR (([Product_Table].[Specifications]) Like [Enter Name]));

    In the Enter Name prompt I would enter in the name with asterisks around it.

    Now that I have over 200 I don't want to do it manually

    I created a Do_Not_Sell_Table

    Do_Not_Sell_Table (around 200 items)
    ItemName

    I would like to find a way to search for the 200 items (with wildcards as the item name may be in the middle of a text string) and if the Do_Not_Sell_Table.ItemName appears in the Product_Table.ItemName or Product_Table.Description or Product_Table.Specifications update the Product_Table.DoNotSell and Product_Table.DoNotSellReason fields.

    I'm assuming this is possible, I just don't know how to do it. Can anyone give me a hand to help me solve my problem?

    Thanks!

    -Leslie

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    It seems to me that you would need to do this via VBA, this would read through your table Do_Not_Sell_Table and to setup the SQL you have above on the fly and run the SQL via DoCmd.RunSql or CurrentDB.Execute.
    Instead of the parameter you would use rs!ItemName from the recordset.
    If you want more information just ask.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Patt,

    Yes, that sounds exactly like what I am trying to do. I just don't know how to write the proper code to do it!

    Can you help lead me in the proper direction?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Hi Leslie,
    In VBA try the following (this is air code ok):

    Dim dbs as DAO.Database, rs as DAO.Recordset, sSql as string
    sSql = "SELECT ItemName from Do_Not_Sell_Table"
    Set dbs = CurrentDB
    Set rs = dbs.OpenRecordset(sSql)
    Do While Not rs.eof
    sSql = "UPDATE Product_Table SET Product_Table.DoNotSell = "Do Not Sell", Product_Table.DoNotSellReason = "ABC Worldwide"
    WHERE ((([Product_Table].[ItemName]) Like *" & rs!ItemName & "*) Or (([Product_Table].[Description]) Like *" & rs!ItemName & "*) OR (([Product_Table].[Specifications]) Like *" & rs!ItemName & "*));"
    dbs.execute sSql
    rs.MoveNext
    Loop
    rs.Close
    set rs = nothing
    dbs.close
    set dbs = Nothing

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Patt,

    Thanks! I've been quite sick, so I apologize for not thanking you for your help earlier.

    I'll see if I can get this to work and then come back and let you know my results..

    -Leslie

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Hope you have recovered

  7. #7
    Star Lounger
    Join Date
    Mar 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by patt View Post
    Hi Leslie,
    In VBA try the following (this is air code ok):

    Dim dbs as DAO.Database, rs as DAO.Recordset, sSql as string
    sSql = "SELECT ItemName from Do_Not_Sell_Table"
    Set dbs = CurrentDB
    Set rs = dbs.OpenRecordset(sSql)
    Do While Not rs.eof
    sSql = "UPDATE Product_Table SET Product_Table.DoNotSell = "Do Not Sell", Product_Table.DoNotSellReason = "ABC Worldwide"
    WHERE ((([Product_Table].[ItemName]) Like *" & rs!ItemName & "*) Or (([Product_Table].[Description]) Like *" & rs!ItemName & "*) OR (([Product_Table].[Specifications]) Like *" & rs!ItemName & "*));"
    dbs.execute sSql
    rs.MoveNext
    Loop
    rs.Close
    set rs = nothing
    dbs.close
    set dbs = Nothing
    I learned from this one... Im also working on MS Access database. This is a big help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •