Mixing
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)
sql ms-access
edited Nov 20 '18 at 0:41
StoneGiant
895416
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
33
add a comment |
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 '18 at 0:41
StoneGiant
895416
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
33
add a comment |
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 '18 at 0:41
StoneGiant
895416
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
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 '18 at 0:41
StoneGiant
895416
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
33
edited Nov 20 '18 at 0:41
StoneGiant
895416
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
33
edited Nov 20 '18 at 0:41
StoneGiant
895416
edited Nov 20 '18 at 0:41
StoneGiant
895416
edited Nov 20 '18 at 0:41
StoneGiant
895416
895416
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
33
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
33
asked Nov 19 '18 at 21:10
ChemEngerChemEnger
33
33
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 23:06
Lee MacLee Mac
3,66131339
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 '18 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 '18 at 13:22
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 23:06
Lee MacLee Mac
3,66131339
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 '18 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 '18 at 13:22
add a comment |
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 '18 at 23:06
Lee MacLee Mac
3,66131339
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 '18 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 '18 at 13:22
add a comment |
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 '18 at 23:06
Lee MacLee Mac
3,66131339
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 '18 at 23:06
Lee MacLee Mac
3,66131339
edited Nov 19 '18 at 23:11
answered Nov 19 '18 at 23:06
Lee MacLee Mac
3,66131339
answered Nov 19 '18 at 23:06
Lee MacLee Mac
3,66131339
answered Nov 19 '18 at 23:06
Lee MacLee Mac
3,66131339
3,66131339
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 '18 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 '18 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 '18 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 '18 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 '18 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 '18 at 0:47
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 '18 at 13:22
@ChemEnger You're most welcome!
– Lee Mac
Nov 20 '18 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.
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
