Results 1 to 8 of 8
  1. #1
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146

    Search table1, write a string to a field in table2 based on search result

    I have two tables; tblLoop and tblEvents. tblEvents contains events related to a record in tblLoop. I want to be able to search tblEvents and find any event categorized as PM within the last year that relates to a record in tblLoop.

    If a result is found I want to write a string to a field called Maintained in tblLoop.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a query that joins the 2 tables, qsLoopEvents
    then make a query to pull data from it in the date range

    select * from qsLoopEvents where [year]=Year(date()) and EventCata = "PM"

    is that it?

  3. #3
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    How then do I write a string, say "done" to tblLoop in the Maintained field?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You convert your SELECT query into a UPDATE query and run it:
    "UPDATE tblEvents SET Maintained ='Done' WHERE ......;"
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    I primarily use Query Design view vs SQL in building query's, although I can see I need to transition more to SQL. In principle I can see how the responses make sense but practically putting it all together I'm not getting it.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So open the query designer, add the two tables, join them by the PK-FK fields, add your criteria for the event category and dates, run it to confirm that it is returning the desired records. Once you are happy with the results open the query in design view and change it into a update query by clicking the Update button on the Design ribon. In the Update To row of the Maintained field add Done. Save it and run it!
    Done 😀

  7. #7
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Thanks for the response. Got it.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 6
    Last Post: 01-27-2021, 02:38 AM
  2. Replies: 9
    Last Post: 12-08-2018, 07:21 PM
  3. Replies: 5
    Last Post: 07-29-2014, 11:20 AM
  4. Replies: 8
    Last Post: 07-08-2012, 02:20 PM
  5. Replies: 5
    Last Post: 08-20-2010, 06:40 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