Multiple conditions for selecting an entire row

[DanL]

I have a table like this:

Key1 Key2 UserID Phone#Called DateCalled Minutes
1 1 1 555-1111 1/1/2009 10
1 2 1 555-1111 1/1/2009 7
2 1 2 555-2222 1/1/2009 15
2 2 2 555-2222 1/2/2009 23
2 3 2 555-2222 1/3/2009 50

What is the best way to create a delete query that removes the entire rows
associated with these keys:

Key1 Key2
1 2
2 2
2 3

I want to keep a single record for each unique combination of UserID and
Phone#Called. If there are multiple values of DateCalled for a combination
of UserID and Phone#Called, I want to select the earliest value of
DateCalled. If there are multiple values of Minutes for a combination of
UserID and Phone#Called with the earliest DateCalled, I want to select the
largest value for Minutes. So when the delete query has run, the following
rows should remain:

Key1 Key2 UserID Phone#Called DateCalled Minutes
1 1 1 555-1111 1/1/2009 10
2 1 2 555-2222 1/1/2009 15

The actual table I am working with has hundreds of thousands (if not
millions) of rows and several additional columns. I need to use conditional
row filtering for these columns similar to what I have described for
DateCalled and Minutes in the example.

My current solution is a series of queries, but I am looking for something
less cumbersome, easier to maintain, and requiring less time to run. I have
used subqueries some and could see how they might be useful here, but am not
very skilled at creating them.

 

[John Spencer MVP]

Saved QueryOne (Identify the combination of user id and phone with the
earliest date)
SELECT UserID, [Phone#Called] as PhoneCalled, Min(DateCalled) as FirstDate
FROM TheTable
GROUP BY UserID, [Phone#Called]

Saved QueryTwo (Identify userId, Phone, earliest date, max time)
SELECT TheTable.UserId, PhoneCalled, FirstDate, Max(Minutes) as BigTime
FROM TheTable INNER JOIN QueryOne
On TheTable.UserID = QueryOne.UserID
AND theTable.[Phone#Called] = QueryOne.PhoneCalled
AND TheTable.DateCalled = QUeryOne.FirstDate
GROUP BY TheTable.UserId, PhoneCalled, FirstDate

This query identifies the records to KEEP
SELECT TheTable.*
FROM TheTable INNER JOIN QueryTwo
ON TheTable.UserID = QueryTwo.UserID
AND TheTable.[Phone#Called] = QueryTwo.PhoneCalled
AND TheTable.DateCalled = QueryTwo.FirstDate
AND TheTable.Minutes = QueryTwo.BigTime

It might be simpler to build a new table based on the above then to delete
records from the old table.

This modification identifies the records to DELETE
SELECT TheTable.Key1, TheTable.Key2
FROM TheTable LEFT JOIN QueryTwo
ON TheTable.UserID = QueryTwo.UserID
AND TheTable.[Phone#Called] = QueryTwo.PhoneCalled
AND TheTable.DateCalled = QueryTwo.FirstDate
AND TheTable.Minutes = QueryTwo.BigTime
WHERE QueryTwo.PhoneCalled is NULL

So you should be able to use a final query that looks like this one.

DELETE
FROM TheTable
WHERE Key1 & "/" & Key2 in
(SELECT TheTable.Key1 & "/" & TheTable.Key2
FROM TheTable LEFT JOIN QueryTwo
ON TheTable.UserID = QueryTwo.UserID
AND TheTable.[Phone#Called] = QueryTwo.PhoneCalled
AND TheTable.DateCalled = QueryTwo.FirstDate
AND TheTable.Minutes = QueryTwo.BigTime
WHERE QueryTwo.PhoneCalled is NULL)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[DanL]

Thanks for the quick and detailed reply, John. I am basically doing what you
suggest, with the only difference being that I am creating a table based on
your DELETE table and then joining that to TheTable in the delete query.
When I try to use a subquery within the delete query like you show, it takes
too long for the query to complete.

I am using a delete query (rather than just making a new table with only the
rows I want to keep) because only a small percentage of rows need to be
deleted in most cases. And if I create a new table, I run up against the
Access 2GB file size limit since the original table is quite large.

Thanks for your help. If you or others know of a different approach that
would run quickly in as few queries as possible, please let me know.