Date Limits Query

[NeonSky via AccessMonster.com]

Good Afternoon,

I have a rather challenging question for your consideration. Please consider
my existing process/sample data....

tblReservationData

LName Addr1 Arrival Departure Owned
Smith House12 4/29/2008 5/2/2008 AB
Smith House12 5/2/2008 5/6/2008 AB
Smith House12 5/5/2008 5/11/2008 AB

qryArrivalLimit

SELECT Arrivals_A.LNAME, Arrivals_A.ARRIVAL, Arrivals_A.ADDR1, Arrivals_A.
OWNED
FROM tblReservationData AS Arrivals_A LEFT JOIN tblReservationData AS
Arrivals_B ON (Arrivals_A.OWNED = Arrivals_B.OWNED) AND (Arrivals_A.ADDR1 =
Arrivals_B.ADDR1) AND (Arrivals_A.LNAME = Arrivals_B.LNAME) AND (Arrivals_A.
ARRIVAL = Arrivals_B.DEPARTURE)
WHERE (((Arrivals_B.LNAME) Is Null) AND ((Arrivals_B.ADDR1) Is Null) AND (
(Arrivals_B.OWNED) Is Null));

qryArrivalLimit returns.....

LName Addr1 Arrival Owned
Smith House12 4/29/2008 AB
Smith House12 5/5/2008 AB

qryDepartureLimit

SELECT Arrivals_A.LNAME, Arrivals_A.DEPARTURE, Arrivals_A.ADDR1, Arrivals_A.
OWNED
FROM tblReservationData AS Arrivals_A LEFT JOIN tblReservationData AS
Arrivals_B ON (Arrivals_A.OWNED = Arrivals_B.OWNED) AND (Arrivals_A.ADDR1 =
Arrivals_B.ADDR1) AND (Arrivals_A.LNAME = Arrivals_B.LNAME) AND (Arrivals_A.
DEPARTURE = Arrivals_B.ARRIVAL)
WHERE (((Arrivals_B.LNAME) Is Null) AND ((Arrivals_B.ADDR1) Is Null) AND (
(Arrivals_B.Owned) Is Null));

qryDepartureLimit returns....

LName Addr1 Departure Owned
Smith House12 5/6/2008 AB
Smith House12 5/11/2008 AB

Now I have my final query which is pulling together the two above queries....

qryArrivalsDepartures

SELECT qryArrivalLimit.LNAME, qryArrivalLimit.Addr1, qryArrivalLimit.Owned,
qryArrivalLimit.Arrival AS Arrivals, Min(qryDepartureLimit.Departure) AS
Departures
FROM qryArrivalLimit2 INNER JOIN qryDepartureLimit2 ON (qryArrivalLimit.
LNAME=qryDepartureLimit.LNAME) AND (qryArrivalLimit.ADDR1=qryDepartureLimit.
ADDR1) AND (qryArrivalLimit.Owned=qryDepartureLimit.Owned) AND
(qryArrivalLimit.Arrival<=qryDepartureLimit.Departure)
GROUP BY qryArrivalLimit.LNAME, qryArrivalLimit.Addr1,qryArrivalLimit.Owned,
qryArrivalLimit2.Arrival;

qryArrivalsDepartures gives us.....

LName Addr1 Arrival Departure Owned
Smith House12 4/29/2008 5/6/2008 AB
Smith House12 5/5/2008 **5/6/2008** AB

Now what is incorrect is the second output line, which displays "5/6/2008",
when it should actually read "5/11/2008".

Would anyone be so kind as to see a means of establishing the proper record
relationship dynamic to get the proper output?

Thank you for your time and thoughts!

 

[KARL DEWEY]

I did not finish reviewing it all but one possibility is your data.
qryArrivalLimit can return two of --
Smith House12 5/5/2008 AB
as your data has --
Smith House12 5/2/2008 5/6/2008 AB
Smith House12 5/5/2008 5/11/2008 AB
I would think that the second one would be 5/6/2008 or later and not
5/5/2008 as Arrival.