comparing two tables

[Guest]

Hi,
I want to compare records from two tables:
TempTable - has all the records that i have imported, i want to check if
there are any new records that do not already exist in tblDataFile; if it
finds a repeat record then delete from TempTable

am trying to use the below query... what am i doing wrong? is there a better
way?

Do While tblDataFile <> EOF
DoCmd.RunSQL "DELETE * FROM TempTable WHERE TempTable.F2 =
tblDataFile.Date AND TempTable.F1 = tblDataFile.County"
Loop

thanks

 

[kingston via AccessMonster.com]

What exactly is the relationship between the data table and the temp table?
In other words, where did the data come from for the temp table? What is
your code not doing that you expect it to do? I assume that this code is
just representative of your logic and you know you need to advance the record
in the tblDataFile recordset:

tblDataFile.MoveNext

 

[George Nicholson]

No need for Loop.

strSQL = "DELETE * FROM TempTable
INNER JOIN tblDataFile ON (TempTable.F2 =
tblDataFile.Date) AND (TempTable.F1 = tblDataFile.County)"

DoCmd.RunSQL strSQL

(or)
CurrentDb.Execute strSQL, dbFailOnError

HTH,

 

[Guest]

thanks for the quick reply...
using your code i am getting an error saying "specify the table containing
the records you want to delete"

any ideas?

 

[Guest]

tblDataFile and TempTable have exactly same structure(fields and types)
TempTable has data(new and old) which was imported form a CSV file; I want
to list all the new records. thats why i want to compare the TempTable and
the tblDataFile and delete all the records that are already existing in
tblDataFile... i check if the record exists by cheking the combination of
date and county fields
thanks

 

[George Nicholson]

My bad. Change to:

"DELETE TempTable.* FROM TempTable....

 

[Guest]

Still getting an error: Could Not delete from specified tables.

 

[John Spencer]

Try this

DELETE * FROM TempTable
WHERE TempTable.F2 & '///' & TempTable.F1 IN
(SELECT tblDataFile.Date & '///' & tblDataFile.County
FROM tblDataFile)

OR
DELETE * FROM TempTable
WHERE Exists
(SELECT *
FROM tblDataFile
WHERE tblDataFile.Date = TempTable.F2 AND
tblDataFile.County AND TempTable.F1)

If those fail then try adding DISTINCTROW to the DELETE clause as in
DELETE DISTINCTROW * FROM ...

====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
====================================================

 

[Guest]

Thanks John,
The first query works but its takes too long.. almost 2 mins - and the
tables have only 5000 records; is there a way to make it faster.

The Second query is much faster but it deletes 33 records while it should
delete only 1.

thanks again

 

[Guest]

Never Mind... the second query works and its quick.. there was a typo

 

[John Spencer]

I had a typo in the second query. I typed "and" instead of "=" in the
last condition in the where clause. (Actually it was a cut and paste
error on my part)

Try the following (and as I should have said in the earlier post. BACK
UP your data first.)

DELETE * FROM TempTable
WHERE Exists
(SELECT *
FROM tblDataFile
WHERE tblDataFile.Date = TempTable.F2 AND
tblDataFile.County = TempTable.F1)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================