Retrieve multiple values from sub-query in MS Access
up vote
0
down vote
favorite
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)
sql ms-access
edited Nov 20 at 0:41
StoneGiant
758415
asked Nov 19 at 21:10
ChemEnger
33
add a comment |
up vote
0
down vote
favorite
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)
sql ms-access
edited Nov 20 at 0:41
StoneGiant
758415
asked Nov 19 at 21:10
ChemEnger
33
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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)
sql ms-access
edited Nov 20 at 0:41
StoneGiant
758415
asked Nov 19 at 21:10
ChemEnger
33
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)
sql ms-access
sql ms-access
edited Nov 20 at 0:41
StoneGiant
758415
asked Nov 19 at 21:10
ChemEnger
33
edited Nov 20 at 0:41
StoneGiant
758415
asked Nov 19 at 21:10
ChemEnger
33
edited Nov 20 at 0:41
StoneGiant
758415
edited Nov 20 at 0:41
StoneGiant
758415
edited Nov 20 at 0:41
StoneGiant
758415
758415
asked Nov 19 at 21:10
ChemEnger
33
asked Nov 19 at 21:10
ChemEnger
33
asked Nov 19 at 21:10
ChemEnger
33
33
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
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...
answered Nov 19 at 23:06
Lee Mac
3,13521138
1
Thanks @Lee Mac, That's absolutely spot on! I had got as far as managing 'a' Center but it was the 'current' one, not the next one. The sub-sub-query completely sorted it. And I am only interested in records with a 'next' date - I had solved it previously but with a much more clunky DonateDate <> "". I can go to bed happy now!
– ChemEnger
Nov 20 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 at 13:22
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
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...
answered Nov 19 at 23:06
Lee Mac
3,13521138
1
Thanks @Lee Mac, That's absolutely spot on! I had got as far as managing 'a' Center but it was the 'current' one, not the next one. The sub-sub-query completely sorted it. And I am only interested in records with a 'next' date - I had solved it previously but with a much more clunky DonateDate <> "". I can go to bed happy now!
– ChemEnger
Nov 20 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 at 13:22
add a comment |
up vote
2
down vote
accepted
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...
answered Nov 19 at 23:06
Lee Mac
3,13521138
1
Thanks @Lee Mac, That's absolutely spot on! I had got as far as managing 'a' Center but it was the 'current' one, not the next one. The sub-sub-query completely sorted it. And I am only interested in records with a 'next' date - I had solved it previously but with a much more clunky DonateDate <> "". I can go to bed happy now!
– ChemEnger
Nov 20 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 at 13:22
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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...
answered Nov 19 at 23:06
Lee Mac
3,13521138
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...
answered Nov 19 at 23:06
Lee Mac
3,13521138
edited Nov 19 at 23:11
answered Nov 19 at 23:06
Lee Mac
3,13521138
answered Nov 19 at 23:06
Lee Mac
3,13521138
answered Nov 19 at 23:06
Lee Mac
3,13521138
3,13521138
1
Thanks @Lee Mac, That's absolutely spot on! I had got as far as managing 'a' Center but it was the 'current' one, not the next one. The sub-sub-query completely sorted it. And I am only interested in records with a 'next' date - I had solved it previously but with a much more clunky DonateDate <> "". I can go to bed happy now!
– ChemEnger
Nov 20 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 at 13:22
add a comment |
1
Thanks @Lee Mac, That's absolutely spot on! I had got as far as managing 'a' Center but it was the 'current' one, not the next one. The sub-sub-query completely sorted it. And I am only interested in records with a 'next' date - I had solved it previously but with a much more clunky DonateDate <> "". I can go to bed happy now!
– ChemEnger
Nov 20 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 at 13:22
1
1
Thanks @Lee Mac, That's absolutely spot on! I had got as far as managing 'a' Center but it was the 'current' one, not the next one. The sub-sub-query completely sorted it. And I am only interested in records with a 'next' date - I had solved it previously but with a much more clunky DonateDate <> "". I can go to bed happy now!
– ChemEnger
Nov 20 at 0:47
Thanks @Lee Mac, That's absolutely spot on! I had got as far as managing 'a' Center but it was the 'current' one, not the next one. The sub-sub-query completely sorted it. And I am only interested in records with a 'next' date - I had solved it previously but with a much more clunky DonateDate <> "". I can go to bed happy now!
– ChemEnger
Nov 20 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 at 13:22
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 at 13:22
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382676%2fretrieve-multiple-values-from-sub-query-in-ms-access%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
