Not sure if this is your only errors but first, a subquery must be a full
Select query, including the name of the table(s) to read from and second,
you must always give a name (alias) to your subqueries:
SELECT Count(tblDonations.keyDonation) AS CountOfkeyDonation,
qryClientFileAs.compFileAs, Sum(tblDonations.curDonationAmount) AS
SumOfcurDonationAmount, (SELECT Count(tblDonations.keyDonation) AS
CountOfCards FROM tblDonations WHERE tblDonations.keyFund=1) as MySubQuery
FROM tblDonations INNER JOIN qryClientFileAs ON tblDonations.keyDonor =
qryClientFileAs.keyClient
GROUP BY qryClientFileAs.compFileAs, tblDonations.ynDonationPaid
HAVING (((tblDonations.ynDonationPaid)=Yes))
ORDER BY Count(tblDonations.keyDonation) DESC;
Giving aliases to all the tables would also make it easier to read:
SELECT Count(D.keyDonation) AS CountOfkeyDonation, C.compFileAs,
Sum(D.curDonationAmount) AS SumOfcurDonationAmount,
(SELECT Count(D2.keyDonation) AS CountOfCards FROM tblDonations D2 WHERE
D2.keyFund=1) as MySubQuery
FROM tblDonations as D INNER JOIN qryClientFileAs as C ON D.keyDonor =
C.keyClient
GROUP BY C.compFileAs, D.ynDonationPaid
HAVING (((D.ynDonationPaid)=Yes))
ORDER BY Count(D.keyDonation) DESC;
In your case, possibly that you want to etablish a relationship between each
keyDonor and the result of your subquery:
SELECT Count(D.keyDonation) AS CountOfkeyDonation, C.compFileAs,
Sum(D.curDonationAmount) AS SumOfcurDonationAmount,
(SELECT Count(D2.keyDonation) AS CountOfCards FROM tblDonations D2 WHERE
D2.keyDonor = D.KeyDonor and D2.keyFund=1) as MySubQuery
FROM tblDonations as D INNER JOIN qryClientFileAs as C ON D.keyDonor =
C.keyClient
GROUP BY C.compFileAs, D.ynDonationPaid
HAVING (((D.ynDonationPaid)=Yes))
ORDER BY Count(D.keyDonation) DESC;
Finally, using the HAVING statement should be done to filter the result of a
Group By and not just to filter the rows from the table(s):
SELECT Count(D.keyDonation) AS CountOfkeyDonation, C.compFileAs,
Sum(D.curDonationAmount) AS SumOfcurDonationAmount,
(SELECT Count(D2.keyDonation) AS CountOfCards FROM tblDonations D2 WHERE
D2.keyDonor = D.KeyDonor and D2.keyFund=1) as MySubQuery
FROM tblDonations as D INNER JOIN qryClientFileAs as C ON D.keyDonor =
C.keyClient
WHERE (((D.ynDonationPaid)=Yes))
GROUP BY C.compFileAs
ORDER BY Count(D.keyDonation) DESC;
Finally, in many cases, Access will replace the parenthesis () around your
Subquery with a set of []. ; including the point . right after the [].
Don't be surprised to see Access doing that.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
Tal said:
Hello,
Could somebody please tell me why I am getting a syntax error in the
subquery.
This is my first subquery so... I could have the whole thing upside down.
SELECT Count(tblDonations.keyDonation) AS CountOfkeyDonation,
qryClientFileAs.compFileAs, Sum(tblDonations.curDonationAmount) AS
SumOfcurDonationAmount, (SELECT Count(tblDonations.keyDonation) AS
CountOfCards WHERE tblDonations.keyFund=1)
FROM tblDonations INNER JOIN qryClientFileAs ON tblDonations.keyDonor =
qryClientFileAs.keyClient
GROUP BY qryClientFileAs.compFileAs, tblDonations.ynDonationPaid
HAVING (((tblDonations.ynDonationPaid)=Yes))
ORDER BY Count(tblDonations.keyDonation) DESC;
Many thanks,
Tal
