Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append Query (2000 SR-1)

    I'm trying to change an append query to an update query. The append query was creating duplicate records. My first attempt at an update query blanked out all the records in the target table--definitely not what I intended. Here's a sample from the SQL of both the update and append query (there are a lot more fields involved than EmpNo):

    Append Query

    INSERT INTO Bonus (EmpNo)
    SELECT BonusTemp.EmpNo
    FROM BonusTemp;

    Update Query

    UPDATE Bonus SET Bonus.EmpNo = [BonusTemp]![EmpNo]

    Thanks,
    Bob

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000 SR-1)

    Where are the values for the update coming from? Your fragment is too fragmentary to evaluate. Are you trying to update a field in Bonus from a field in BonusTemp? If so, you must have a way to link the two tables to that the right values get put in the right place.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000 SR-1)

    The source table is BonusTemp, and the target table is Bonus. I've attached a text file that shows the full SQL for each query.

    Thanks,
    Bob
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000 SR-1)

    That doesn't really help. All you have in the "update" query is a bunch of expressions setting one field equal to another. Is there some reason you haven't tried using the query grid to build this? It would be much simpler for you than trying to write the SQL based on an entirely different type of query. The basic syntax for an update query is this:

    UPDATE Table2 INNER JOIN Table1 ON Table2.KeyField = Table1.KeyField SET Table2.Field1 = Table1.Field1, Table2.Field2 = Table1.Field2

    Since you haven't explained what field the two tables might be joined on, I still can't help you with the SQL.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000 SR-1)

    I'm sorry--it's the WeekEnding field. The query updates records for the most current week.

    Thanks,
    Bob

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000 SR-1)

    And have you tried using the query grid to build the update?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000 SR-1)

    Yes--That's where the append query came from. I used the Expression Builder tool to fill in the Update To value. From what you've said, it sounds like I didn't fill in the Criteria value properly?

    Thanks,
    Bob

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000 SR-1)

    I tried using the grid query again to build the update query, but again with no luck. After I did this, I looked at the SQL and it used the UPDATE...SET...WHERE method (rather than the UPDATE...INNER JOIN...ON...SET method that Charlotte mentioned).

    After the WHERE method did nothing, I made a copy of the query and adjusted the SQL to use the INNER JOIN method. All it did was update all of the documents with the update criteria value (WeekEnding) in the target table with a value from the key field of the source table (EmpNo). That is, all of the documents in the target table with the week ending 1/16/04 had their EmpNo value changed to the same EmpNo (which happened to be the last alphabetical EmpNo in the source table).

    I've attached a text file that shows the SQL for each of these queries.

    Thanks,
    Bob
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000 SR-1)

    But if you didn't change the query type to Update, you aren't building an update query in the grid. Try that.
    Charlotte

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000 SR-1)

    Sorry, but I don't believe you created those update queries in the query grid because if you had, it would not have used the <!t>[table]
    ![field] syntax that I see in your attachment. The ! character is used when referring to controls on a form or report, not when referring to fields in a table or query. We're all willing to help, but it's up to you to make a genuine effort as well. If you want to learn to write SQL, there are a number of books on the topic, but if you want to create queries in Access, the easiest way is to learn to use the query grid. Here are some basic instructions:

    1. Open the query grid by clicking the Queries tab in the database window and then clicking the New button. That will put you in the query grid with the Show Table dialog up.
    2. Select the table you want to update and the table you want to update from and click Add. Then you can close the dialog.
    3. With the query grid open and both tables showing in it, join the two tables on the WeekEnding field.
    4. From the Query menu, select Update Query as the query type. This will change the look of the grid somewhat.
    5. Drag the fields you want to update from the target table onto the grid
    6. In the UpdateTo row, enter the table name and field name from the other table in the syntax TableName.FieldName. If there are spaces in your table or field names, you will have to put square brackets around each element (table and field).

    Try these and post back if you have problems.
    Charlotte

Posting Permissions

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