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
Solved

delete query not working

Posted on 2013-02-07
16
276 Views
Last Modified: 2013-02-12
hey guys i'm trying to delete records from 2 table that don't belong to our team. but i keep getting errors from access. here are the pictures below. i'm not sure if this is due to the relationship, but even so, many of the relationship do not even have referential integrity checked except the one is to many relationships

i've deleted records from these 2 tables before - all records before a certain date.

what i've tried:
1) doing a delete query selecting all fields from the tblParsing or the tblDataStorage with an outer join to tblTeamMembers where criteria is null.

2) i've done is that i've done a select query to select all the records i want to delete. then did a make table. from there a new query with the new table identifying records that need to be deleted and the tblParsing or tblDataStorage (i know i can't delete from 2 tables at one time). still can't work

error 1st tableerror another tablerelationship diagramrelationship properties
0
Comment
Question by:developingprogrammer
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38864664
try this

delete tblForParsing.*
from tblForParsing
where tblForParsing.[task/activity#] in(select [task/activity#] from [delete these activities])


will delete everything where  tblForParsing.[task/activity#] = [delete these activities].[task/activity#]



or this one,

delete tblForParsing.*
from tblForParsing
where tblForParsing.[task/activity#] not in(select [task/activity#] from [delete these activities])

will delete everything where  tblForParsing.[task/activity#] <> [delete these activities].[task/activity#]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38864753
See here as well:
http://support.microsoft.com/kb/240098

Also note that if the Delete query includes an Non-update-able query, then this could also cause the error
0
 

Author Comment

by:developingprogrammer
ID: 38864758
thanks so much for your help capricon1. i tried it but it still doesn't work. here are the screenshots
5.png
6.png
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:developingprogrammer
ID: 38864773
thanks boag2000, the Unique Records was set to no just now, i just set it to Yes but it still can't work

unique records - yes
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38864799
developingprogrammer,

change the name of the fields accordingly..

[task/activity#]  with [task/activity #]

delete tblForParsing.*
from tblForParsing
where tblForParsing.[task/activity #] in(select [task/activity #] from [delete these activities])
0
 

Author Comment

by:developingprogrammer
ID: 38864813
whao cool it works!! so sorry capricon1 for not spotting such an elementary fix.

i've got a short wrap up question. how you did it was by using written out sql. can you show me how i can do it using the access QBE? how come my previous method / screenshots couldn't work?

i'm trying to learn how to do everything via QBE so that i outsource the SQL to access thus minimising my mistakes at this stage
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38864821
<how come my previous method / screenshots couldn't work?>

because you are using a join in your query.
0
 

Author Comment

by:developingprogrammer
ID: 38864850
ok i believe you're referring to my base query which identifies which activities to delete cause for my delete query i've tried both with and without join.

for my base query this is how i'm doing it. how should i do it via QBE without using an outer join?
8.png
0
 

Author Comment

by:developingprogrammer
ID: 38865013
ok re-reading the thread a bit i realised it can't be the base query you were talking about because in your example which works, you're refering to my base query in your SQL - which verifies that my base query is ok.

so it must be the delete query. so if the joins were causing the problem, i removed the join already but i'm not sure how come the query still can't work = ((

could someone help me out? i'm eager to find out what i was doing wrong. thanks guys!!
9.png
0
 

Author Comment

by:developingprogrammer
ID: 38865037
comparison of query that work and query that doesn't work - how do i make the query work using purely QBE?

this worksthis doesn't work
0
 
LVL 18

Assisted Solution

by:Richard Daneke
Richard Daneke earned 150 total points
ID: 38870615
right-click on your query tab and view the SQL to compare the effect of the extra table  in the top of the QBE grid
0
 

Author Comment

by:developingprogrammer
ID: 38876027
hi DoDahD, thanks so much for your great suggestion! i looked at the SQL and this is what i've got. could you kindly share with me how i can get the function of the working query through using QBE? i'm not sure how to do it now though i've tried quite a few ways already.

this query doesn't work

DELETE tblDataStorage.*, tblDataStorage.[Task/Activity #]
FROM [DELETE THESE ACTIVITIES], tblDataStorage
WHERE (((tblDataStorage.[Task/Activity #]) In ([DELETE THESE ACTIVITIES].[Task/Activity #])));

this query works

DELETE tblForParsing.*, tblForParsing.[task/activity #]
FROM tblForParsing
WHERE (((tblForParsing.[task/activity #]) In (select [task/activity #] from [delete these activities])));
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 38876071
In this delete query:

DELETE tblDataStorage.*, tblDataStorage.[Task/Activity #]
FROM [DELETE THESE ACTIVITIES], tblDataStorage
WHERE (((tblDataStorage.[Task/Activity #]) In ([DELETE THESE ACTIVITIES].[Task/Activity #])));
you have no relationship established.  In QBE, the relationship is the line from one table to the next in the top half of the window.

This should be able to be fixed by dragging the [Task/Activity #] field from one table and dropping it on the matching field in the other table.  This creates a linked relationship between the tables where the records in [DELETE THESE ACTIVITIES] will be used to match records in [tblDataStorage] to determine when to delete records.

As always, when testing, it is safest to backup first and/or to test in a copy of the data!!!!!!!
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 350 total points
ID: 38876150
try this version



delete DISTINCTROW tblForParsing.*
from tblForParsing
INNER JOIN [delete these activities]
ON tblForParsing.[task/activity #] = [delete these activities].[task/activity #]





.
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 38876207
Capricorn,

I suspect that you are correct in your suggestion.   When you establish the link in the QBE window, Access will correct the SQL to reflect the relationship.
0
 

Author Closing Comment

by:developingprogrammer
ID: 38883408
fantastic guys it works!! thanks so much guys!!!!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 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