Hi Betsey,
Here's something that accomplishes what you need:
1- Create Delete Query#1 that performs the following
Table1.field1 innerjoin Table2.field1
Table1.field2 innerjoin Table2.field2
Where Table1.field3 IS NULL
2- Create Delete Query#2 that performs the following
Table1.field1 innerjoin Table2.field1
Table1.field2 innerjoin Table2.field2
Where Table2.field3 IS NULL
3- Copy the Structure Only of Table1 and paste As NewTemporaryTable
4- Open NewTemporaryTable in design view and set as the PrimaryKey "field1
and field2"
5- Create an AppendQuery does the following
Table1.field1 innerjoin Table2.field1
Table1.field2 innerjoin Table2.field2
And Appends the records to the table NewTemporaryTable
NOTE: other than the copy of the table structure you can call ALL the
queries to run in an macro (use SetWarnings if needed).
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <
http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
This posting is provided "AS IS" with no warranties, and confers no rights
--------------------
| Content-Class: urn:content-classes:message
| From: "Betsey Thurlo" <
[email protected]>
| Sender: "Betsey Thurlo" <
[email protected]>
| Subject: unique records
| Date: Mon, 7 Jun 2004 12:11:13 -0700
| Lines: 15
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcRMwzNC3eUCFjaGQNqSXLgMHjgLSg==
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.queries:203067
| NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi.
| I'd like to create a sql statement in a query that does
| this:
|
| If record1/field1 is equal to record2/field1 AND
| record1/field2 is equal to record2/field2 AND
| record1/field3 or record2/field3 is NULL then delete the
| record with the field3 NULL value.
|
| Then If record1/field1 is equal to record2/field1 AND
| record1/field2 is equal to record2/field2 delete all of
| the identical records except for one (doesn't matter
| which one since the Null record is already gone).
|
| BDT
|
