?
Solved

on qeury access 2013

Posted on 2014-12-22
7
Medium Priority
?
409 Views
Last Modified: 2014-12-23
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
0
Comment
Question by:bill201
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40514347
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
 

Author Comment

by:bill201
ID: 40514467
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
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40514519
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 40514694
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
 

Author Comment

by:bill201
ID: 40514769
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
 
LVL 7

Accepted Solution

by:
Robert Sherman earned 1000 total points
ID: 40514930
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
 

Author Closing Comment

by:bill201
ID: 40515830
thanks a lot
0

Featured Post

A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question