?
Solved

How do I delete records based on matching records in a subquery? MS Access

Posted on 2008-10-18
31
Medium Priority
?
288 Views
Last Modified: 2012-05-05
I want to delete any records from a table (tblAllData) before I append new records from qryNewData. I don't know how to do this. Subqueries are a mystery.

Here is my SQL

DELETE tblAllData.*
FROM tblAllData
WHERE EXISTS
(SELECT qryNewData.SchoolNum, qryNewData.ReportType, qryNewData.ShortVarCode, qryNewData.Year
FROM qryNewData
WHERE tblAllData.SchoolNum = qryNewData.SchoolNum AND tblAllData.ReportType =qryNewData.ReportType AND tblAllData.ShortVarCode = qryNewData.ShortVarCode AND tblAllData.Year = qryNewData.Year);

Unfortunately, this returns ALL records from tblAllData, If I execute it I would be deleting everything, which is not my intent.

How in the devil do I accomplish this?
0
Comment
Question by:Dsastray
[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
  • 22
  • 5
  • 4
31 Comments
 

Author Comment

by:Dsastray
ID: 22749599
DELETE tblAllData.*, tblAllData.SchoolNum, tblAllData.ReportType, tblAllData.ShortVarCode, tblAllData.Year
FROM tblAllData
WHERE (((tblAllData.SchoolNum) In (select SchoolNum from qryNewData)) AND ((tblAllData.ReportType) In (select ReportType from qryNewData)) AND ((tblAllData.ShortVarCode) In (select ShortVarCode from qryNewData)) AND ((tblAllData.Year) In (select Year from qryNewData)));
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749600
why are you deleting the records?
0
 

Author Comment

by:Dsastray
ID: 22749614
The above works.

But my first syntax was damn near straight cribbed from Alan Brownes subquery tutorial. What was the first one doing?

And is there a more efficient way of accomplishing this? (Is my solution optimal?) I worry that a sub-optimal solution could cost me a lot of time. This is one of the few times that I work with a DB that has Millions of records, so I get concerned...
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Dsastray
ID: 22749635
Since I cannot overwrite data when I append to tblAllData, my understanding is I can either
a) first delete from tblAllData any records matching those in qryNewData, then append qryNewData to tblAllData
b) update any records in tblAllData matching those in qryNewData, then append qryNewData to tblAllData

I am choosing a)

Is b) preferred?

Is there a c) I don't know about?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22749652
which records do you want to delete?

using { EXISTS } is faster than using { IN }
0
 

Author Comment

by:Dsastray
ID: 22749679
I should say
b) update any records in tblAllData matching those in qryNewData (updating fields in tblAllData from with same fields qryNewData), then append qryNewData to tblAllData (to account for new records)
0
 

Author Comment

by:Dsastray
ID: 22749682
So capricorn1, what would that SQL look like?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22749684
1. if you want newdata and don't want to keep record with old data
    a. how many fields will have new values?
        if most of the fields will have new data
              * delete old record then
              * append new record
        if only few fields will have new data
              * update the record is a good way to do it


     
0
 

Author Comment

by:Dsastray
ID: 22749686
Because obviously I know not how to use EXISTS, or the first SQL I posted would be working and I'd be a happy happy man.
0
 

Author Comment

by:Dsastray
ID: 22749695
I'd like to know how to do both approaches, ultimately, but why don't you explain the 1a, and then I'll post a question asking you to explain how to do 1b.
0
 

Author Comment

by:Dsastray
ID: 22749816
And, I just realized, my solution query I posted above is sub optimal. As in it doesn't work right at all.

I was hoping to delete as if I had INNER joins between tblAllData and qryNewData on Year, SchoolNum, ShortVarCode, ReportType, but this is not doing that.

Ugh. This works (but is slow):
DELETE tblAllData.*, tblAllData.SchoolNum, tblAllData.ReportType, tblAllData.ShortVarCode, tblAllData.Year
FROM tblAllData
WHERE (((tblAllData.SchoolNum) In (SELECT qryNewData.SchoolNum
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))) AND ((tblAllData.ReportType) In (SELECT qryNewData.ReportType
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))) AND ((tblAllData.ShortVarCode) In (SELECT qryNewData.ShortVarCode
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))) AND ((tblAllData.Year) In (SELECT qryNewData.Year
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))));
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749892
That looks like overkill for some reason.
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749905
Are you using Access 2000?
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749917
Ok, try the first query you posted again.  But first copy the select statement from the exists clause and see if that works and returns the records you want.
0
 

Author Comment

by:Dsastray
ID: 22749930
This is elegant, but does not work:

SELECT tblAllData.*
FROM tblAllData;
WHERE EXISTS
  (select *
    from qxtbNewType
    where qxtbNewType.SchoolNum = tblAllData.SchoolNum);

I get the error message "The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression."
0
 

Author Comment

by:Dsastray
ID: 22749935
Okay, hold on while I do that...
0
 

Author Comment

by:Dsastray
ID: 22749959
So if you mean does this:

SELECT  qryNewData.SchoolNum,  qryNewData.ReportType,  qryNewData.ShortVarCode,  qryNewData.Year
FROM  qryNewData

work? Yes. It returns the records I want.

But if I copy

SELECT  qryNewData.SchoolNum,  qryNewData.ReportType,  qryNewData.ShortVarCode,  qryNewData.Year
FROM  qryNewData
WHERE tblAllData.SchoolNum =  qryNewData.SchoolNum AND tblAllData.ReportType = qryNewData.ReportType AND tblAllData.ShortVarCode =  qryNewData.ShortVarCode AND tblAllData.Year =  qryNewData.Year

I get the same message as above; "The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression."

(And note, if I delete tblAllData.SchoolNum, I get "The Microsoft Jet database engine does not recognize tblAllData.ReportType as a valid field name or expression." and so forth ~ no misspellings)
0
 

Author Comment

by:Dsastray
ID: 22749972
Sorry, missed your q. I am using 2002, but realize that the DB is in 2000 format. Is there a gotcha here? (meanwhile, I'll be converting it to 2002 and trying again).
0
 

Author Comment

by:Dsastray
ID: 22749983
No difference, I am still getting "The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression." in my new 2002 format db. when I use

SELECT tblAllData.*
FROM tblAllData;
WHERE EXISTS
  (select *
    from qryNewData
    where qryNewData.SchoolNum = tblAllData.SchoolNum);
0
 

Author Comment

by:Dsastray
ID: 22750022
Okay, I'll feel dumb if this is an issue, because I like to try and give full disclosure:

tblAllData is a linked table.

Umm, let's see, anything else?

Access 2002, SP3
0
 

Author Comment

by:Dsastray
ID: 22750038
Copied tblAllData as a local table "tblAllData2". Retried the query with tblAllData2. Same errors.
0
 
LVL 120

Expert Comment

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

SELECT tblAllData.*
FROM tblAllData
WHERE (EXISTS
  (select SchoolNum
    from qryNewData
    where qryNewData.SchoolNum = tblAllData.SchoolNum)=true);

0
 

Author Comment

by:Dsastray
ID: 22750164
Same error.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 22750325
Dsastray,

create  a NEW query.
copy and paste this

SELECT tblAllData.*
FROM tblAllData
WHERE (EXISTS(select SchoolNum from qryNewData where qryNewData.SchoolNum = tblAllData.SchoolNum)=true);

in the SQL view
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22750393
Does this return what you are looking for?  You may have to tweak the field names


SELECT tblAllData.*, tblAllData.SchoolNum
FROM tblAllData
WHERE (((tblAllData.SchoolNum) In (Select schoolnum from qryNewData)));

Open in new window

0
 

Author Comment

by:Dsastray
ID: 22750483
Capricorn - Okay, so I tweaked it, because I am actually trying to match on several fields, I had just simplified it.

Delete tblAlldata.*
From tblAllData
WHERE (Exists (SELECT SchoolNum, ReportType, ShortVarCode,Year From qryNewData WHERE (qryNewData.SchoolNum = tblAllData.SchoolNum) AND (qryNewData.ReportType = tblAllData.ReportType) AND (qryNewData.ShortVarCode = tblAllData.ShortVarCode) And (qryNewData.Year = tblAllData.Year)))

Damn. It works!

I made far too many changes to say exactly what combination made the magic happen, but some notes:

(I'm mortified to admit I didn't have one before but) I went in and added a primary key to tblAllData. It had a unique index that was a combination of SchoolNum, ReportType, Year & ShortVarCode. I made a primary key out of the same combination.

Not sure how this affected things. A last interesting note, I made a quick table, indexed, primary keys etc. that contains the same data as qryNewData. (tblNewData). It runs the same EXISTS query about 10 times faster. (Damn. It works, and it is fast!)
0
 

Author Comment

by:Dsastray
ID: 22750488
Anyhow, PIER117, I'll ask a separate question asking how do to this with an IN statement (because I am genuinely curious), if you can figure out how to use multiple criteria that way... My syntax in post 22749816 is essentially the same as yours above (when matching more than one field), no?

Yet it would return multiple combinations -
ie, if qryNewData has
schoolnum          year
1                          2008
2                          2006

using separate IN statements returns

schoolnum          year
1                          2006
1                          2008
2                          2006
2                          2008

0
 

Author Comment

by:Dsastray
ID: 22750498
Also, what is weird, and I think the genius of Capricorn1's solution

SELECT tblAllData.*
FROM tblAllData
WHERE ((Exists (SELECT tblNewData.* From tblNewData WHERE (tblNewData.SchoolNum = tblAllData.SchoolNum) AND (tblNewData.ReportType = tblAllData.ReportType) AND (tblNewData.ShortVarCode = tblAllData.ShortVarCode) And (tblNewData.Year = tblAllData.Year)))=True);

works, but takes FOREVER!!! Apparently the ...Exists(Select tblNewData.*  <----- is a bad idea
0
 

Author Comment

by:Dsastray
ID: 22750508
Meaning (and I am frankly not inclined to test this theory out, because I've already pissed my day away on this)

"The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression."

error was I believe related to having a (linked?!?) table, with no primary key, and then the rest of it was the tweak of replacing SELECT qryNewData.* with the actual field names.

Oh, and a temporary table beats a query.

Phew
0
 

Author Closing Comment

by:Dsastray
ID: 31507466
Thanks, brother.
0
 

Author Comment

by:Dsastray
ID: 22750514
So all to Capricorn1, with a distinct possibility that if you can make me a working IN version, PIER117, I'll ask that question and you can whisk me off my feet.

I'd like to see you get your due, but if you aren't able to come up with a solution that works, I probably won't post the new Q. I'm curious and grateful, but not THAT curious to start the chase again with a whole bunch of other Experts.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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