unique records

  • Thread starter Thread starter Betsey Thurlo
  • Start date Start date
B

Betsey Thurlo

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
 
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
|
 
Back
Top