query dates that show previous appointment AND the next appointmen

[Guest]

I have two tables: tblDonor & tblAppt

tblDonor is a list of blood donors and tblAppt is a list of the appointment
dates. the table structure is as follows:

tblDonor
-----------------
MakID Primary Key (Number)
DonorName Text


tblAppt
-----------------
ApptID Primary Key (Auto)
MakID Number
ApptDate Date

The tables are joined on the MakID number with a one to many relationship

Each donor can have multiple appointments within tblAppt. My problem is I
want the query to display two dates relative to the current date: The
previous ApptDate (or null if no previous appointment) and the next ApptDate
(or null in no next appointment). See example below using current date of
2005-02-17:


MakID PrevDate NextApptDate
------- ----------- ----------------
4322344 2005-02-15
4234234 2005-02-14 2005-03-17
3244581 2005-07-01
3698224 2005-01-01 2005-04-01

I have tried using agregated queries, I have tried sub queries, and I have
tried DMin/Dmax, but the only thing that seems to work is three consecutive
queries.

I would really appreciate the help to have this done with a single query.

Thanks in advance.

 

[Van T. Dinh]

If a Donor already had 5 donations before today's date and 3 appointments
booked in advanve, how many rows do you want to show in the Recordset
returned by the Query?

 

[Guest]

I would like the query to show a single record with the single most recent
appointment and the single next recent (or upcoming) appointment.

for instance of the five previous donations, I want to show the most recent,
and of the 3 up coming appointments I want to show the one which will be
coming up next.

Thanks

 

[Van T. Dinh]

It may not be the most efficient way but try something like:

SELECT D.MakID, D.DonorName,
DMax("[ApptDate]","[tblDonation]","([frg_MakID] = " & [D].[MakID] &
") AND ([ApptDate] < Date())") AS LastDon,
DMin("[ApptDate]","[tblDonation]","([frg_MakID] = " & [D].[MakID] &
") AND ([ApptDate] >= Date())") AS NextDon
FROM tblDonor AS D

 

[John Spencer (MVP)]

Three query solution:

QPrior:
SELECT MakID, Max(ApptDate) as PriorDate
FROM tblAppt as A
WHERE ApptDate < Date()
GROUP BY MakID

QNext:
SELECT MakID, Min(ApptDate) as NextDate
FROM tblAppt as A
WHERE ApptDate > Date()
GROUP BY MakID

Combine those to get the following:
SELECT MakID, PriorDate, NextDate
FROM (tblAppt LEFT JOIN QPrior
ON tblAppt.MakID = QPrior.MakID)
LEFT JOIN QNext
ON tblAppt.MakID = Q.MakID

If you are using later versions of Access (2K and later), you can probably do
this all in one query

SELECT MakID,
(SELECT Max(ApptDate)
FROM tblAppt as A
WHERE A.MakID = tblAppt.MakID AND A.ApptDate<Date()) as PriorDate,
(SELECT Min(ApptDate)
FROM tblAppt as B
WHERE B.MakID = tblAppt.MakID AND B.ApptDate>Date()) as NextDate
FROM tblAppt

 

[PC Datasheet]

Leo,

I have an appointments module for entering and reporting appointments you
might be interested in. Send me your email address to my email address below
and I will send you a screen shot.

 

[Guest]

Hi John,

Thanks for your help. The only problem with this solution (your single
query) is that it did not generate a single record for a particular MakID;
while the dates themselves were correct, there were multiple instances of
them per MakID.

However, a slight modification using aggregates has solved this problem.
Here is what I came up with using your query:

SELECT tblAppointments.MakID, Max((SELECT Max(ApptDate)
FROM tblAppointments as A
WHERE A.MakID = tblAppointments.MakID AND A.ApptDate<Date())) AS
PriorDate, Min((SELECT Min(ApptDate)
FROM tblAppointments as B
WHERE B.MakID = tblAppointments.MakID AND B.ApptDate>Date())) AS NextDate
FROM tblAppointments
GROUP BY tblAppointments.MakID;

This is exactly what I was looking for. Thank you for your help and
expertise.

Leo Saumure