on qeury access 2013

hi

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;

Open in new window



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
bill201Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
Is there a relationship set between the two tables with referential integrity set to do cascading deletes?   If so, that could be what is causing the deletes from the second table.   Also, if I remember correctly, setting the form to do "inconsistent updates" using a dynaset may be part of the solution.

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.
0
bill201Author Commented:
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
0
Robert ShermanOwnerCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeCommented:
how are you displaying the data, single record form, continuous form, datasheet?

I generally set the forms AllowDeletions property to false and have a "Delete Record" button in the form footer on those forms where I want the user to be able to delete records.  Then, in the Click event of that button, I execute a SQL statement that deletes the current record based upon in PK of the record.
Private Sub cmd_Delete_Click

    Dim strSQL as string

    On Error Goto ProcError

    strSQL = "DELETE FROM table1 WHERE [PK_Field] = " & me.txt_PKID
    currentdb.execute strsql, dbfailonerror

ProcExit:
    Exit Sub
ProcError:
    msgbox err.number & vbcrlf & err.description, , "Deletion error"
    debug.print err.number, err.Description
    debug.print strSQL
End Sub

Open in new window

0
bill201Author Commented:
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.
0
Robert ShermanOwnerCommented:
in the query Design View, you can right click on the background in the upper section where your tables are and select "properties", this will bring up the Query Properties, you will find the recordset type in there.

Basically, in Access it comes down to a user interface issue -- if you see the data in the row of the query, and you select delete, the standard datasheet view pretty much assumes you want to delete what you are seeing, which is records from multiple tables..  

There's lots of ways to get around this, but not without doing some custom programming to handle specifically what you want to do, for example the way Dale Fye mentioned.  

Perhaps what you were looking to do could be accomplished with a lookup field?  If all you were looking for is a particular description from the 2nd table, you could change the field in the 1st table to be a lookup field or dropdown.  Then you would not need to pull in the second table in the query.  (Not sure that is what you were looking for, but perhaps..)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bill201Author Commented:
thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.