Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL: SUM with SELEC DISTINCT (Access 2003 / XP)

    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#))[img]/forums/images/smilies/wink.gif[/img]) 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#))[img]/forums/images/smilies/wink.gif[/img];

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL: SUM with SELEC DISTINCT (Access 2003 / XP)

    I think you're stuck with creating two queries, a query to perform the SELECT DISTINCT and a totals query based on the first one to calculate the sums. I would recommend to get the values for the criteria from a form; that way, the user can enter or select the criteria and you will only need the two queries, instead of a new set for each value.
    Say that you create a form frmCriteria with a text box or combo box PersonID, a text box or combo box Investment and a text box or combo box AsOfDate. You can use this as WHERE clause in the first query:

    WHERE People.PersonId=Forms!frmCriteria!PersonID AND Investments.Investment=Forms!frmCriteria!Investmen t AND BalData.AsOfDate=Forms!frmCriteria!AsOfDate

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL: SUM with SELEC DISTINCT (Access 2003 / XP)

    Thanks, Hans, that's what I've done. After scrutinizing the KB, I discovered that Jet SQL does not support SUM(Distinct). Actually, it runs pretty fast.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •