Iterate through recordset to create criteria string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a table that I've created that contains 2 fields: CompanyName and
Contact Name. This entire table needs to be the criteria for a delete query
that I need to run matching it to a larger table. My question is: How do I
iterate through the table (with 2 fields) and build a criteria string. I
know I can then use that string and execute a SQL string that deletes those
records.

Criteria for example:
Company Name Contact Name
ABC and Jane Doe or
CDE and Al Smith or ....

Thanks, D.
 
D Collins said:
Hello,
I have a table that I've created that contains 2 fields: CompanyName
and Contact Name. This entire table needs to be the criteria for a
delete query that I need to run matching it to a larger table. My
question is: How do I iterate through the table (with 2 fields) and
build a criteria string. I know I can then use that string and
execute a SQL string that deletes those records.

Criteria for example:
Company Name Contact Name
ABC and Jane Doe or
CDE and Al Smith or ....

Thanks, D.
Click to expand...

You could do that, but it might be easier to build a delete query that
matches the two tables directly. Suppose you want to delete from
tblContacts (primary key ContactID) all people who match fields
CompanyName and ContactName with the table tblContactsToDelete. You
might execute a delete query with SQL like this:

DELETE tblContacts.*
FROM tblContacts
WHERE tblContacts.ID In
(
SELECT tblContacts.ID
FROM tblContacts INNER JOIN tblContactsToDelete
ON (tblContacts.CompanyName = tblContactsToDelete.CompanyName)
AND (tblContacts.ContactName = tblContactsToDelete.ContactName)
);
 
Thanks, that'll work great!
-----Original Message-----


You could do that, but it might be easier to build a delete query that
matches the two tables directly. Suppose you want to delete from
tblContacts (primary key ContactID) all people who match fields
CompanyName and ContactName with the table tblContactsToDelete. You
might execute a delete query with SQL like this:

DELETE tblContacts.*
FROM tblContacts
WHERE tblContacts.ID In
(
SELECT tblContacts.ID
FROM tblContacts INNER JOIN tblContactsToDelete
ON (tblContacts.CompanyName = tblContactsToDelete.CompanyName)
AND (tblContacts.ContactName = tblContactsToDelete.ContactName)
);

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top