comparing two tables

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

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


imranmp wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

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,

"imranmp" wrote in message
...
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

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?

"George Nicholson" wrote:

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,

"imranmp" wrote in message
...
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

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


"kingston via AccessMonster.com" wrote:

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


imranmp wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

My bad. Change to:

"DELETE TempTable.* FROM TempTable....



"imranmp" wrote in message
...
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?

"George Nicholson" wrote:

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,

"imranmp" wrote in message
...
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

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


"George Nicholson" wrote:

My bad. Change to:

"DELETE TempTable.* FROM TempTable....



"imranmp" wrote in message
...
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?

"George Nicholson" wrote:

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,

"imranmp" wrote in message
...
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

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
================================================== ==


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


"George Nicholson" wrote:

My bad. Change to:

"DELETE TempTable.* FROM TempTable....



"imranmp" wrote in message
...
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?

"George Nicholson" wrote:

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,

"imranmp" wrote in message
...
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

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

"John Spencer" wrote:

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
================================================== ==


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


"George Nicholson" wrote:

My bad. Change to:

"DELETE TempTable.* FROM TempTable....



"imranmp" wrote in message
...
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?

"George Nicholson" wrote:

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,

"imranmp" wrote in message
...
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

Never Mind... the second query works and its quick.. there was a typo
tblDataFile.County AND TempTable.F1)
needs to be
tblDataFile.County = TempTable.F1)


"John Spencer" wrote:

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
================================================== ==


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


"George Nicholson" wrote:

My bad. Change to:

"DELETE TempTable.* FROM TempTable....



"imranmp" wrote in message
...
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?

"George Nicholson" wrote:

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,

"imranmp" wrote in message
...
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