Delete Records by Date - 2 tables involved

[alhotch]

I want to "prune" a reservations database - that is delete records that are
older than, say 11/01/2007. I have two tables - Reservations and
ReservationDetails - in a One-to-Many relationship. The ReservationDetails
table (field is PickUpDate) represents a travel date (outbound or inbound).
The Reservations table represents one or more ReservationDetail records.
Therefore, I can have several ReservationDetails records for ONE Reservation.
The JOIN looks like this: Reservations.ReservationID ===>
ReservationDetails.ReservationID in a One-to-Many. Primary keys are
Reservations.ReservationID and ReservationDetails.ResDetailID. Now I want to
delete those records (from ReservationDetails) whose PickUpDate is older than
11/01/2007. BUT, If there are additional detail records tied to the same
Reservation record that are newer than 11/01/2007, I want to keep the main
(Reservation) record. Example: I have detail records 618660 and 618661 that
are "owned" by Reservation record 363436. Detail record 618660 PickUpDate is
10/20/2007. Detail record 618661 PickUpDate is 11/10/2007. Even though I want
to "prune" the database back to 11/01/2007, I want to maintain the complete
record as referenced by the main Reservation record. So, after too many words
about describing my problem, what can I do to make this deletion query work
"as advertised" ?

 

[John Spencer]

This query will delete records in ReservationDetails

DELETE *
FROM ReservationDetails
WHERE PickupDate < #11/1/2007#

This query will delete records in Reservations that have no detail records

DELETE *
FROM Reservations
WHERE ReservationID in
(SELECT ReservationID
FROM Reservations LEFT JOIN ReservationDetails
ON Reservation.ReservationID = ReservationDetails.ReservationID
WHERE ReservationDetails.ReservationID is Null)

If you want to keep all reservations details if any reservation detail
associated with the reservationId is on or after the cutoff date, then
the first query would look more like;

DELETE *
FROM ReservationDetails
WHERE ReservationID NOT in
(SELECT ReservationID
FROM Reservation.Details
WHERE PickupDate>= #11/1/2007#)

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

 

[alhotch]

Thanks, John. I was initially able to work up a delete query for any detail
records that were older than 11/01/2007. But my problem is that I can have
more than one detail record for each reservation record. Example: Reservation
record #363436 has two detail records #618660 and #618661. Detail record
#618660 has a PickUpDate of 10/20/2007 and detail record #618661 has a
PickUpDate of 11/10/2007. I want to keep the entegrity of the Reservation
record (both detail records) even though one of the detail records has an
older date than 11/01/2007. This way, I keep the entire travel record, for
this reservation, together. Other detail records that are older than
11/01/2007 (all within a single reservation record) are OK to be deleted.
Where I have concerns is in the logic of how to determine which details are
older and if they are part of a reservation that contains details that are
not "older". How to do this in a delete query is where I need assistance.

 

[John Spencer]

Perhaps I don't understand your structure enough to help you.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

I think what you are saying is that you want to keep any Reservations records
that have any reservation details with a PickupDate later than a specified
cutoff date. AND if you keep the Reservations record, you want to keep all
the associated reservationDetails records.


This query should identify which records to delete in reservations details.

SELECT ReservationID
FROM ReservationDetails LEFT JOIN
(SELECT ReservationID
FROM ReservationsDetails
WHERE PickUpDate>=#11/1/2007#) as Keep
ON ReservationDetails.ReservationID = Keep.ReservationID
WHERE Keep.ReservationID is Null

So then you should be able to use this to delete the records.
DELETE *
FROM ReservationsDetails
WHERE ReservationID IN
(
SELECT ReservationID
FROM ReservationDetails LEFT JOIN
(SELECT ReservationID
FROM ReservationsDetails
WHERE PickUpDate>=#11/1/2007#) as Keep
ON ReservationDetails.ReservationID = Keep.ReservationID
WHERE Keep.ReservationID is Null
)

Once you have delete the reservationDetails records, you should be able to
identify the Reservations records without any detail records and delete them
(see my original reply).

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

 

[alhotch]

"I think what you are saying is that you want to keep any Reservations records
that have any reservation details with a PickupDate later than a specified
cutoff date. AND if you keep the Reservations record, you want to keep all
the associated reservationDetails records."

Yes, John. This is EXACTLY what I want. Keep allassociated details with the
single reservation EVEN if the detatils "span" the cutoff date(s).

I will use your suggestion(s) and report later. Much Gras !!!

Al

 

[alhotch]

For John Spencer - These two queries worked AS ADVERTISED ! Thanks you again
for your valuable assistance. I added one small piece to the WHERE criteria
when I found out I also had "blank" records in the PickUpDate field - WHERE
(PickUpDate >=#11/01/2007#) OR (PickUpoDate Is Null) - Exact results required.

Thank you AGAIN, John.