Solved

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

Posted on 2008-10-18
31
279 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
  • 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
 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

759 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

18 Experts available now in Live!

Get 1:1 Help Now