Solved

on qeury access 2013

Posted on 2014-12-22
7
387 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
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
thanks a lot
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now