bill201
asked on
on qeury access 2013
hi
i have a query like this on access 2013:
i have a form that is based on this query. but the problem is that when i delete a record on the form , it's delete the record from table1 and from table 2, but i want that it will not be able do delete any value from table2, and the record will be deleted only from table1, how can i make it with an sql string query.
thanks alot
i have a query like this on access 2013:
SELECT table1.makatID,table1.Price, table2.Description
FROM table1 LEFT JOIN table2 ON table1.MakatID = table2.makatID;i have a form that is based on this query. but the problem is that when i delete a record on the form , it's delete the record from table1 and from table 2, but i want that it will not be able do delete any value from table2, and the record will be deleted only from table1, how can i make it with an sql string query.
thanks alot
ASKER
thanks a lot for your comment
there is not a relationship between the two tables.
i am attaching a sample from the database to explain the situation, when you delete a record on query1 the record will be delete from both tables why is that, and is there a way to avoid it and only the record from table "product2t" will be deleted?
thanks a lot test2.mdb
there is not a relationship between the two tables.
i am attaching a sample from the database to explain the situation, when you delete a record on query1 the record will be delete from both tables why is that, and is there a way to avoid it and only the record from table "product2t" will be deleted?
thanks a lot test2.mdb
The only way that you can avoid having the records deleted from both tables is to not show the fields from the table you want to preserve, and set the recordset type of the query to Dynaset (Inconsistent Updates). So, in the example database you provided, if you open Query1 and remove the productt.price column from the SELECT portion of the SQL statement, and change the recordset type, then deleting rows from the query will only delete from the Product2T table.
Basically, any fields you put in the SELECT portion are going to impact the tables they are based on, however, you can use the other tables in the WHERE clause to filter data and those fields will not cause deletion from the underlying table.
The bottom line is that there is probably another way to do what you are trying to accomplish. If you want to provide some more details on what it is you are trying to do, perhaps we can come up with a better solution.
Basically, any fields you put in the SELECT portion are going to impact the tables they are based on, however, you can use the other tables in the WHERE clause to filter data and those fields will not cause deletion from the underlying table.
The bottom line is that there is probably another way to do what you are trying to accomplish. If you want to provide some more details on what it is you are trying to do, perhaps we can come up with a better solution.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Robert Sherman
thanks a lot for your answer. You're right, I already have a better idea how to do it.
But can you please show me ( just for knowledge reasons :) ) how can i change on access query screen the recordset type, and is there no way to relate on a query a two tables which one is update-able and the other isn't ?
Dale Fye
thanks for your comment, i think it's a An Interesting and Careful approach, but by my self I'm a bit too lazy to write so much code :) and i try to avoid them when i can.
thanks a lot for your answer. You're right, I already have a better idea how to do it.
But can you please show me ( just for knowledge reasons :) ) how can i change on access query screen the recordset type, and is there no way to relate on a query a two tables which one is update-able and the other isn't ?
Dale Fye
thanks for your comment, i think it's a An Interesting and Careful approach, but by my self I'm a bit too lazy to write so much code :) and i try to avoid them when i can.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot
I'll try to look up in a few minutes if no one else gets to this before me. You might want to look at these two suggestions, though.. might be enough to get you squared away.