CODEWITHSUNDEEP
sqlms-access
ChemEnger
ChemEnger

Reputation: 149

Retrieve multiple values from sub-query in MS Access

I have a table (SPG) with person (DonorID), dates of Donation (DonateDate) and place of Donation (Center). I would like to find the date & place of a donation (easy), then the date and place of the next donation, with the gap between the two dates.

I have used a SELECT subquery which gives me the next date (NextDate) but I can't then get the matching Center:

SELECT SPG.DonorID,
       SPG.DonateDate,
       SPG.Center AS Center1,
       (SELECT Min(DonateDate) as MinDate
          FROM SPG As A
         WHERE SPG.DonorID = A.DonorID
           AND A.DonateDate > SPG.DonateDate) AS NextDate,
       [NextDate]-[DonateDate] AS Gap
  FROM SPG
 GROUP BY SPG.DonorID, SPG.DonateDate, SPG.Center
HAVING ((((SELECT Min(DonateDate) as MinDate
             FROM SPG As A
            WHERE SPG.DonorID = A.DonorID
              AND A.DonateDate > SPG.DonateDate))<>""))
 ORDER BY SPG.DonorID, SPG.DonateDate;

I have a different INNER JOIN query which gives me the Center but it returns all future donations, not just the next one:

SELECT A.DonorID,
       A.DonateDate,
       A.Center,
       Min(B.DonateDate) AS NextDate, 
       B.Center,
       [B].[DonateDate]-[A].[DonateDate] AS Gap
  FROM SPG AS A INNER JOIN SPG AS B ON A.DonorID = B.DonorID
 WHERE B.DonateDate > A.DonateDate
 GROUP BY A.DonorID,
          A.DonateDate,
          A.Center,
          B.Center,
          B.DonateDate-A.DonateDate;

Basically, I can't seem to combine the Min() command from the sub-query with returning more than field from a JOIN.

(Please excuse the different notation - lots of copying & pasting of trial code)

Upvotes: 0

Views: 227

Answers (1)

Lee Mac
Lee Mac

Reputation: 16025

One of the SQL gods here may know of a cleaner way, but this is all I have at present:

select 
    s1.donorid,
    s1.donatedate,
    s1.center as center1, 
    (
        select min(s2.donatedate)
        from spg s2
        where s1.donatedate < s2.donatedate and s1.donorid = s2.donorid
    ) as nextdate, 
    (
        select s2.center
        from spg s2
        where 
        s1.donorid = s2.donorid and
        s2.donatedate = 
        (
            select min(s3.donatedate)
            from spg s3
            where s1.donatedate < s3.donatedate and s1.donorid = s3.donorid
        )
    ) as center2, 
    nextdate - s1.donatedate as gap
from
    spg s1
order by
    s1.donorid,
    s1.donatedate

And if you're only after those records which have a 'next' donation date, perhaps:

select 
    s1.donorid,
    s1.donatedate,
    s1.center as center1, 
    (
        select min(s2.donatedate)
        from spg s2
        where s1.donatedate < s2.donatedate and s1.donorid = s2.donorid
    ) as nextdate, 
    (
        select s2.center
        from spg s2
        where 
        s1.donorid = s2.donorid and
        s2.donatedate = 
        (
            select min(s3.donatedate)
            from spg s3
            where s1.donatedate < s3.donatedate and s1.donorid = s3.donorid
        )
    ) as center2, 
    nextdate - s1.donatedate as gap
from
    spg s1
where exists 
    (select 1 from spg s2 where s1.donorid = s2.donorid and s1.donatedate < s2.donatedate)
order by
    s1.donorid,
    s1.donatedate

I heard you like correlated subqueries...

Upvotes: 2

Related Questions