Multi-table Delete Query (A2K)

Topic

New Reply

I’m attempting to design a delete query calling records from two tables, where if one field from each table matches, then delete all records with that match from one of the tables. I’d appreciate any suggestions or alternate methods.

Reply | Quote

Replies

Why do you need alternate methods? The only trick to delete queries is that in 2000 and later you must be sure to add the DISTINCTROW keyword, which can be done from the query grid by right clicking on the gray area of the grid and opening the properties dialog for the query. Set Unique Records to Yes, and that will insert DISTINCTROW and make your query updatable.

Reply | Quote

I was looking to make a query updatable and this worked using 2 tables in the query. If I tried using 1 table and 1 union query in the query, I could not make it updatable by this method. Is it possible to accomplish what I am trying to do???
Thanks, John

Reply | Quote

We don’t know enough to provide detailed help, but you can probably use a subquery. If you want to delete records from the table that have a match in the union query, the SQL looks like this:

DELETE * FROM tblSomething WHERE UniqueID IN (SELECT UniqueID FROM qryUnion)

You can’t do it the other way round – a union query isn’t updatable.

Reply | Quote

Hi Hans,
I am trying to update (change not delete) the records in tblwlcostbook. This what the SQL looks like so far:

SELECT DISTINCTROW tblCostBook.[Mod], tblCostBook.SN, tblCostBook.LN, qryJoinJNOP.op, tblCostBook.JN, tblCostBook.UCost, tblCostBook.OF, tblCostBook.[D#]
FROM qryJoinJNOP RIGHT JOIN tblCostBook ON qryJoinJNOP.jn = tblCostBook.JN;

I am not sure how to change this.
Thank you for your help. John

Reply | Quote

This is a select query, not an update query. You need to explain what you’re trying to update and why you have another query in there.

Reply | Quote

Hi Charlotte-
I am using a union query to bring together the numbers (jn) and the description (op) from a purchased part number table and a labor table. In the select query I am bringing together the Cost Book that has no descriptions, just numbers, and the union query for the description of the number. When I run this select query, I would like to be able to change the information in the fields of the records when we make changes in manufacturing. We might want to change a number because we are using a different part number for a certain model. I also have a field that lets me print the records in a report in a certain order. BY looking at this select query I can tell whether or not the records are in the correct order when I make a change by looking at the description. If they are not in the right order, I would want to be able to change the field contents for any record in the query. Just looking at numbers (jn) are meaningless without the description (op).
If this still doesn’t make sense, please let me know and I will try again.
Thank you for your help. John

Reply | Quote

Sorry, but union queries and any queries that include unions are not updatable. That’s the nature of the beast and there isn’t any workaround. The way to avoid the problem in the first place is through the appropriate use of forms and subforms, not queries.

Reply | Quote

Sorry, but union queries and any queries that include unions are not updatable. That’s the nature of the beast and there isn’t any workaround. The way to avoid the problem in the first place is through the appropriate use of forms and subforms, not queries.

Reply | Quote

Hi Charlotte-
I am using a union query to bring together the numbers (jn) and the description (op) from a purchased part number table and a labor table. In the select query I am bringing together the Cost Book that has no descriptions, just numbers, and the union query for the description of the number. When I run this select query, I would like to be able to change the information in the fields of the records when we make changes in manufacturing. We might want to change a number because we are using a different part number for a certain model. I also have a field that lets me print the records in a report in a certain order. BY looking at this select query I can tell whether or not the records are in the correct order when I make a change by looking at the description. If they are not in the right order, I would want to be able to change the field contents for any record in the query. Just looking at numbers (jn) are meaningless without the description (op).
If this still doesn’t make sense, please let me know and I will try again.
Thank you for your help. John

Reply | Quote

This is a select query, not an update query. You need to explain what you’re trying to update and why you have another query in there.

Reply | Quote

Hi Hans,
I am trying to update (change not delete) the records in tblwlcostbook. This what the SQL looks like so far:

SELECT DISTINCTROW tblCostBook.[Mod], tblCostBook.SN, tblCostBook.LN, qryJoinJNOP.op, tblCostBook.JN, tblCostBook.UCost, tblCostBook.OF, tblCostBook.[D#]
FROM qryJoinJNOP RIGHT JOIN tblCostBook ON qryJoinJNOP.jn = tblCostBook.JN;

I am not sure how to change this.
Thank you for your help. John

Reply | Quote

We don’t know enough to provide detailed help, but you can probably use a subquery. If you want to delete records from the table that have a match in the union query, the SQL looks like this:

DELETE * FROM tblSomething WHERE UniqueID IN (SELECT UniqueID FROM qryUnion)

You can’t do it the other way round – a union query isn’t updatable.

Reply | Quote

I was looking to make a query updatable and this worked using 2 tables in the query. If I tried using 1 table and 1 union query in the query, I could not make it updatable by this method. Is it possible to accomplish what I am trying to do???
Thanks, John

Reply | Quote

Why do you need alternate methods? The only trick to delete queries is that in 2000 and later you must be sure to add the DISTINCTROW keyword, which can be done from the query grid by right clicking on the gray area of the grid and opening the properties dialog for the query. Set Unique Records to Yes, and that will insert DISTINCTROW and make your query updatable.

Reply | Quote