L
Leo Saumure
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.
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.