Hi everybody:
I’m using Access 2003 / XP and am getting inaccurate results using SELECT DISTINCT in a totals query. My select distinct statement produces correct results. However, when I view the totals row and sum up the fields, the duplicate rows are included. I don’t recall that this was a problem in previous versions of Access, so am hoping someone can throw some light on this.
SELECT DISTINCT Sum(BalData.BegBalNet) AS SumOfBegBalNet, Sum(BalData.EndBalNet) AS SumOfEndBalNet, Investments.InvestmentId
FROM Investments INNER JOIN (BalData INNER JOIN (People INNER JOIN InvestContactReportType ON People.PersonId = InvestContactReportType.PersonId) ON BalData.InvestmentId = InvestContactReportType.InvestmentId) ON Investments.InvestmentId = InvestContactReportType.InvestmentId
WHERE (((People.PersonId)=2128640559) AND ((Investments.Investment)=’High Peak International Hldg.’) AND ((BalData.AsOfDate)=#1/31/2004#))
GROUP BY People.PersonId, Investments.Investment, Investments.InvestmentId, BalData.AsOfDate;
Apparently, Access SQL is performing the sums before filtering the records with the WHERE clause. Using a HAVING clause produced the same problem.
I tried using IN statements in the criteria, but Access SQL produced no results. Can someone improve my SQL?
SELECT Sum(BalData.BegBalNet) AS SumOfBegBalNet, Sum(BalData.EndBalNet) AS SumOfEndBalNet
FROM BalData
HAVING (((BalData.BegBalNet)) In (SELECT DISTINCT BalData.BegBalNet
FROM Investments INNER JOIN (BalData INNER JOIN (People INNER JOIN InvestContactReportType ON People.PersonId = InvestContactReportType.PersonId) ON BalData.InvestmentId = InvestContactReportType.InvestmentId) ON Investments.InvestmentId = InvestContactReportType.InvestmentId
WHERE (((People.PersonId)=2128640559) AND ((Investments.Investment)=’High Peak International Hldg.’) AND ((BalData.AsOfDate)=#1/31/2004#))
) AND ((BalData.EndBalNet)) In (SELECT DISTINCT BalData.EndBalNet
FROM Investments INNER JOIN (BalData INNER JOIN (People INNER JOIN InvestContactReportType ON People.PersonId = InvestContactReportType.PersonId) ON BalData.InvestmentId = InvestContactReportType.InvestmentId) ON Investments.InvestmentId = InvestContactReportType.InvestmentId
WHERE (((People.PersonId)=2128640559) AND ((Investments.Investment)=’High Peak International Hldg.’) AND ((BalData.AsOfDate)=#1/31/2004#));
I realize I can work around this by saving the SELECT DISTINCT as a querydef and then summing its results in a second query, but this approach means creating a new querydef each time my code runs. Since I’m passing in PersonID, AsOfDate and Investment variables, it makes it a bit messy . . .
Any ideas?
