Results 1 to 14 of 14
  1. #1
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25

    Issue with update query

    Hello,


    I built a select query that filters the data based on a certain criteria. Please see SQL for it:

    SELECT Tbl1.Column1, Tbl1.Column2, Tbl1.Column3
    FROM Tbl1
    WHERE (((Tbl1.Column1) Like "Blue*") AND ((Tbl1.Column2) Not Like " 111 Central Ave*")) Or (((Tbl1.Column1) Like "Red*"));

    After I ran this query, I get a desired list. I am trying to build a code that will take a very first value that is listed in Column3 of the desired list and update all remaining records that are listed in this column to match the first record.
    Column1; Column2; Column3
    Blue; 500 Bell Street; 45
    Red; 300 Central Ave; 38
    Blue; 400 Saks Ave; 39
    Red; 300 Central Ave; 38

    Desired output should look as follows:
    Column1; Column2; Column3
    Blue; 500 Bell Street; 45
    Red; 300 Central Ave; 45
    Blue; 400 Saks Ave; 45
    Red; 300 Central Ave; 45

    Is this something that I can do in access? If yes, can someone please help me get to the correct syntax. Either SQL or VBA code will work. Thank you for your help.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If I understand correctly you should be able to accomplish what you need by creating a couple more Query objects.

    The first thing you will need to do to the query you have there is Sort the order on something unique. If you do not have a Primary Key, maybe you can ORDER BY on the column with the address.

    After you add sorting, save, and close your query... copy your Select Query object and create a second one just like it. You can accomplish this by right clicking the query in the Navigation Pane to Copy and then Right Click anywhere in the Navigation Pane to paste a copy. You will be prompted to name your copy.

    In the new query, open it in Design View and then switch over to SQL View. Then add the words TOP 1.
    SELECT TOP 1 Tbl1.Column1, Tbl1.Column2, Tbl1.Column3

    .
    Click image for larger version. 

Name:	ViewSQL_Ribbon.jpg 
Views:	33 
Size:	35.6 KB 
ID:	25527


    Save your new query. Now create a new third query object. Add the two queries to the Design Surface. First, add the query with SELECT TOP 1, then the other.

    Create a join between the column you want to match. I think this is the one that is the color description. Now adjust you join by selecting the join and then double clicking the line after it gets fatter. In the window that pops up, create a LEFT JOIN by selecting "Include ALL records in "The query name of the TOP 1" and only those from ..."

    While still in design view of your third query, up top in the Ribbon, select the Update option. Now look in the design surface and locate the second query, the one that is not TOP 1 and retrieves multiple records, double click the column with the address column to add it to the grid.

    In the grid, add the name of the column from your first query in the Update To: field.
    [FirstQueryName].[ColumnNameWithAddress]

    Save your new update query and run it. You can click the exclamation point in the Ribbon or close the query and then double click it from the Navigation Pane.
    Last edited by ItsMe; 08-21-2016 at 12:45 PM. Reason: deleted extraneous content

  3. #3
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Hello ItsMe,
    Thank you for your response. I followed your instructions and the first 2 queries worked as you suggested, however the final query does not give me the result that i am looking for.
    Query 1:
    I added a new column "ID" as auto number to get PK.
    Query 2:
    I copied query 1 and added word TOP 1 Into SQL after SELECT word. This query filters the data set and returns the very first record that is listed in the data set.
    Query 3:
    It appears that this query updates first record which is located in Column3 with the same value that is listed in Column3 in Query 1 and Query 2 . In other words I am copying and pasting the same value again for the same record, but the remaining records did not get updated at all.
    I tried to play with Query 3, but could not come up with the result that I am looking for.
    Did I miss anything from your process that you listed below? What did you mean when you wrote the last sentence:"Do this by referencing the name of the column."?
    Thanks,

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try this all in one:
    Code:
    Option Compare Database
    Option Explicit
    Public Sub subUpdate()
    
    
    Dim sSQL As String
    Dim sSave As String
    Dim rst As DAO.Recordset
    
    
    sSQL = "SELECT Tbl1.Column1, Tbl1.Column2, Tbl1.Column3 " _
        & "FROM Tbl1 " _
        & "WHERE (((Tbl1.Column1) Like 'Blue*') " _
        & "AND ((Tbl1.Column2) Not Like ' 111 Central Ave*')) " _
        & "Or (((Tbl1.Column1) Like 'Red*')) " _
        & "ORDER BY Column3 DESC;"
    Debug.Print sSQL
    Set rst = CurrentDb.OpenRecordset(sSQL)
    rst.MoveLast
    rst.MoveFirst
    sSave = rst!column3
    Do While rst.EOF = False
        rst.Edit
        rst!column3 = sSave
        rst.Update
        rst.MoveNext
    Loop
    Set rst = Nothing
    End Sub

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Magnolia1 View Post
    ...Did I miss anything from your process that you listed below? What did you mean when you wrote the last sentence:"Do this by referencing the name of the column."?...
    That is an artifact left by me when I was composing the post. I will delete it from my post.

    From you description, it sounds like your JOIN may not be correct. Perhaps you are using the default Inner Join on your third query or came from the LEFT when you needed the RIGHT. Posting the SQL for your third query here and also providing the name of the query that includes the SELECT TOP clause may shed some light on the issue.

    It sounds as though you are understanding the logic behind the approach I suggested. If you can wrap your head around it, you will be able to apply what you learned here in the future. I say this because it is possible to do this all in a single SQL statement. But, if you utilize some of the tools provided by Access, you might be able to solve complex problems with relative ease. It is also nice to break different processes down and scrutinize the results of a given process.

    If you look at post #4 you will see a different approach. I did not look at the code in depth. However, this seems like an approach I would likely take. Just understand that it is quite a departure from the process I described in post #1.

  6. #6
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Thank you ItsMe, for your response.
    I used davegri's code and it worked great. I just needed to change the code a bit to fit me needs. Thanks again for trying to help me I really appreciate your input and definitely use it in the future.

  7. #7
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Hello davegri, Thank you for your response. I used your code and it worked. I just needed to change it a bit to update my records to match the first record and not the last one. But I think I got the idea ...
    Thank you again for your help. I appreciate it.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You're welcome. Glad to help.

    The 'DESC' at the end of the query string is for sort descending. The highest value in Column3 should be at the top.

  9. #9
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Thank you.

  10. #10
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Hello guys
    Hello davegri
    How can I snap the two tables on this?

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by azhar2006 View Post
    ...
    How can I snap the two tables on this?
    I am not understanding your use of the word, 'snap'.

    I believe you already understand how to JOIN two tables together.

  12. #12
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Thank you ItsMe
    What I want is the work of renovation of the number one table to No. 2, but only for a particular field
    And I also try to use (Loop) to find a record in the total records and then back again to search for the next record in all the records and so on.
    I saw something like that, but I can not do that.
    Any help or example.
    Function yourFunctionName()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("Query1")
    Set rs2 = db.OpenRecordset("Query2")

    If rs1.RecordCount=0 Then Exit Sub

    rs1.MoveFirst
    ' loop through each record in the first recordset
    Do Until rs1.EOF
    ' If matching record is found then update field in
    ' second recordset to value you determine
    If rs2.RecordCount=0 Then Exit Sub
    rs2.MoveFirst
    Do Until rs2.EOF
    If rs1![FieldName] = rs2!FieldName Then
    rs2.Edit
    rs2![FieldName] = 'Your Value'
    rs2.Update
    End If
    rs2.MoveNext
    Loop
    rs1.MoveNext
    Loop

    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing

    End Function

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    OK, perhaps you are looking to swap data from one table to another. I suggest you look into building a couple of queries using the Wizard. Try the Find Unmatched option and the Find Matched option. In other words, build a new query but do no use the Design option, choose the Wizard.

    The result of the find unmatched may provide a query to get you started.


    Perhaps you should start a new thread and provide a link here to your new thread.

  14. #14
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Well, thank you ItsMe
    I will publish a new topic.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-23-2015, 01:51 PM
  2. Update Query Issue
    By Voodeux2014 in forum Queries
    Replies: 9
    Last Post: 02-18-2015, 12:01 PM
  3. Update query issue
    By dastr in forum Queries
    Replies: 1
    Last Post: 03-28-2012, 04:39 AM
  4. Update query issue
    By doomy304 in forum Queries
    Replies: 2
    Last Post: 06-25-2011, 02:31 PM
  5. Update Query Performance Issue
    By Amber_1977 in forum Queries
    Replies: 2
    Last Post: 12-07-2010, 08:36 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