Deleting records in a table based on criteria

[Jack Fleet]

I am looking for a way to delete records in a table based on a few criteria.
There are about 74 fields in the table. I need a query to look at each
unique [Ticket No] and if there is more than one [Claim No] then only show
the [Claim No] with the lowest number. Otherwise if there is not more than
one [Claim No] go to next [Ticket No].

I do not necessarily need to delete records, I could export only those
records that match this criteria. I might run into a size limitation if I do
Make Table Query.

Here is an example of what I might run into data wise:

Ticket No Claim No Line No
214 111 1
214 111 2
214 111 3
214 112 1
214 112 2
214 112 3
214 113 1
214 113 2
214 113 3
215 200 1
215 200 2

Desired Output/Result would be:

Ticket No Claim No Line No
214 111 1
214 111 2
214 111 3
215 200 1
215 200 2

Thanks in advance for taking a look at this.

 

[Klein-4]

I am looking for a way to delete records in a table based on a few criteria.
There are about 74 fields in the table. I need a query to look at each
unique [Ticket No] and if there is more than one [Claim No] then only show
the [Claim No] with the lowest number. Otherwise if there is not more than
one [Claim No] go to next [Ticket No].

I do not necessarily need to delete records, I could export only those
records that match this criteria. I might run into a size limitation if I do
Make Table Query.

Here is an example of what I might run into data wise:

Ticket No Claim No Line No
214 111 1
214 111 2
214 111 3
214 112 1
214 112 2
214 112 3
214 113 1
214 113 2
214 113 3
215 200 1
215 200 2

Desired Output/Result would be:

Ticket No Claim No Line No
214 111 1
214 111 2
214 111 3
215 200 1
215 200 2

Thanks in advance for taking a look at this.

One way to do this is the following --

Assume your table is called "tblTest". Create a query called
"qryTest0" as such --

SELECT tblTest.TicketNo, Min(tblTest.ClaimNo) AS MinOfClaimNo
FROM tblTest
GROUP BY tblTest.TicketNo;

Then create a query called "qryTest1" as such --

DELETE DISTINCTROW tblTest.*, tblTest.ClaimNo
FROM tblTest INNER JOIN qryTest0 ON tblTest.TicketNo =
qryTest0.TicketNo
WHERE (((tblTest.ClaimNo)<>[MinOfClaimNo]));

Running "qryTest1" will have the desired outcome.

Ross La Haye