Append Query (2000 SR-1)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Thanks,
Bob

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 [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.

Reply | Quote

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 [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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Thanks,
Bob

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote