Aggregate function error in query 1

qqp · May 11, 2018

Hi and Happy Friday everyone!

I have a query where I am getting the balances of a member's account. I am getting the error: "Your query does not include the specified expression "MemberID_PK" as part of an aggregate function. I will post the sql of the other queries involved following the offending query.

SELECT Members.MemberID_PK, Members.LASTNAME, IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[FirstName] & " and " & [Spouse] & " " & [LastName],[FirstName] & " " & [LastName]) AS ComboName, IIf([SpouseLastName] Is Null,"",[Spouse] & " " & [SpouseLastName]) AS ComboName2, MemberAccountTotals.LOTNUMBER, Members.FIRSTNAME, Members.BusinessName, Members.ADDRESS, Members.CITY, Members.STATE, Members.ZIP, MemTotAsmts.Bankrupt, MemTotAsmts.UnCollect, MemTotAsmts.Legal1, MemTotAsmts.Legal2, Sum([dbamount]) AS TotalDBAMT, Sum([cramount]) AS TOTALCRAMT, MemTotAsmts.SumOfAmount, [totaldbamt]-[totalcramt] AS Bal, IIf([Bal] Between -0.005 And 0.005,0,[Bal]) AS Bal2, IIf([SumOfAmount]>0,([sumofdbamount]-[sumofcramount])/[SumOfAmount],0) AS BF, CLng([BF]*10000)/10000 AS BF2, IIf([Bankrupt]=Yes,10,IIf([UnCollect]=Yes,9,IIf([Legal2]=Yes,8,IIf([Legal1]=Yes,7,IIf([Bal2]=0,2,IIf([Bal2]<0,1,IIf([BF2]>=5,6,IIf([BF2]>=4,5,IIf([BF2]>=3,4,IIf([BF2]>0,3,0)))))))))) AS BFNum2, IIf([Bal2]=0,2,IIf([Bal2]<0,1,IIf([BF2]>=5,5,IIf([BF2]>=4,4,IIf([BF2]>0,3,0))))) AS BFNum3, IIf([Bankrupt]=Yes,10,IIf([UnCollect]=Yes,9,IIf([Legal2]=Yes,8,IIf([Legal1]=Yes,7,IIf([Bal2]=0,2,IIf([Bal2]<0,1,IIf([Bal2]-[SumOfAmount]>0.01,6,IIf([Bal2]>0,3,0)))))))) AS BFNum, MemberAccountTotals.AsmtType, IIf([asmttype] Like "*" & "roads" & "*",DateDiff("d",Date(),[trandate]),"") AS RoadsAge, MemTotAsmts.BillingCycle, MemberAccountTotals.TranDate, MemberAccountTotals.DBAmount, MemberAccountTotals.CRAmount, Members.SPOUSE, Members.SPOUSELASTNAME, Members.LASTNAME, Members.FIRSTNAME
FROM ((MemberAccountTotals INNER JOIN Members ON MemberAccountTotals.MemberID_PK = Members.MemberID_PK) LEFT JOIN PrimaryLots ON Members.MemberID_PK = PrimaryLots.MemberID_FK) INNER JOIN MemTotAsmts ON Members.MemberID_PK = MemTotAsmts.MemberID_FK;

SQL FOR MEMTOTASMTS:

SELECT DISTINCT MemberAssessments.MemberID_FK, Sum(MemberAssessments.Amount) AS SumOfAmount, AccountStatus.Bankrupt, AccountStatus.UnCollect, AccountStatus.Legal1, AccountStatus.Legal2, MemberAssessments.BillingCycle, MemberAssessments.AssessmentType, IIf([assessmenttype] Like "*" & "roads" & "*",[sumofamount],0) AS RoadsAmount
FROM MemberAssessments INNER JOIN AccountStatus ON MemberAssessments.MemberID_FK = AccountStatus.MemberID
GROUP BY MemberAssessments.MemberID_FK, AccountStatus.Bankrupt, AccountStatus.UnCollect, AccountStatus.Legal1, AccountStatus.Legal2, MemberAssessments.BillingCycle, MemberAssessments.AssessmentType, MemberAssessments.AssessmentType;

SQL FOR MEMBERACCOUNTTOTALS

SELECT MemberAccounts.MemberID_PK, MemberAccounts.MEMBER, Accounts.DBAmount, Accounts.CRAmount, primarylots.primarylots, primarylots.LOTNUMBER, Accounts.TranDate, Accounts.AsmtType
FROM (MemberAccounts INNER JOIN Accounts ON MemberAccounts.MemberID_PK = Accounts.MemberID_FK) INNER JOIN primarylots ON MemberAccounts.MemberID_PK = primarylots.MemberID_FK;

I think that's all of them, anything else is just a table. I thank you in advance for any assistance!

SkipVought · May 11, 2018

Hi,

Do yourself a favor and format your query to make it easier to read and understand.

Code:

SELECT
  MemberAccounts.MemberID_PK
, MemberAccounts.MEMBER
, Accounts.DBAmount
, Accounts.CRAmount
, primarylots.primarylots
, primarylots.LOTNUMBER
, Accounts.TranDate
, Accounts.AsmtType
FROM (MemberAccounts
  INNER JOIN Accounts 
  ON MemberAccounts.MemberID_PK = Accounts.MemberID_FK)
  INNER JOIN primarylots 
  ON MemberAccounts.MemberID_PK = primarylots.MemberID_FK;

Your first query has a SUM() and therefor must have a GROUP BY that includes all other non aggregate expressions in your SELECT clause.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

dhookom · May 11, 2018

Here is a handy tool for formatting your Access (and other) SQL Statements from SQLinForm. There are lots of options for when to insert indents and carriage returns. I pasted in the first query, set some options, and clicked [Format SQL]. You might try first to uncheck most options and then add them back in as needed.

BTW: you have a ton of hard-coded values nested in IIf() functions which I would work at modeling in tables.

Code:

SELECT
          Members.MemberID_PK
        , Members.LASTNAME
        ,
           IIF([SpouseLastName] IS NULL
                    AND NOT ([Spouse] IS NULL),[FirstName] & " and " & [Spouse] & " " & [LastName],[FirstName] & " " & [LastName]) AS ComboName
        ,
           IIF([SpouseLastName] IS NULL,"",[Spouse] & " " & [SpouseLastName]) AS ComboName2
        , MemberAccountTotals.LOTNUMBER
        , Members.FIRSTNAME
        , Members.BusinessName
        , Members.ADDRESS
        , Members.CITY
        , Members.STATE
        , Members.ZIP
        , MemTotAsmts.Bankrupt
        , MemTotAsmts.UnCollect
        , MemTotAsmts.Legal1
        , MemTotAsmts.Legal2
        , SUM([dbamount]) AS TotalDBAMT
        , SUM([cramount]) AS TOTALCRAMT
        , MemTotAsmts.SumOfAmount
        , [totaldbamt]-[totalcramt] AS Bal
        ,
           IIF([Bal] BETWEEN -0.005 AND 0.005,0,[Bal]) AS Bal2
        ,
           IIF([SumOfAmount]>0,([sumofdbamount]-[sumofcramount])/[SumOfAmount],0) AS BF
        , CLng([BF]*10000)/10000                                                  AS BF2
        ,
           IIF([Bankrupt]=Yes,10,
                    IIF([UnCollect]=Yes,9,
                              IIF([Legal2]=Yes,8,
                                        IIF([Legal1]=Yes,7,
                                                  IIF([Bal2]=0,2,
                                                            IIF([Bal2]<0,1,
                                                                      IIF([BF2]>=5,6,
                                                                                IIF([BF2]>=4,5,
                                                                                          IIF([BF2]>=3,4,
                                                                                                    IIF([BF2]>0,3,0)))))))))) AS BFNum2
        ,
           IIF([Bal2]=0,2,
                    IIF([Bal2]<0,1,
                              IIF([BF2]>=5,5,
                                        IIF([BF2]>=4,4,
                                                  IIF([BF2]>0,3,0))))) AS BFNum3
        ,
           IIF([Bankrupt]=Yes,10,
                    IIF([UnCollect]=Yes,9,
                              IIF([Legal2]=Yes,8,
                                        IIF([Legal1]=Yes,7,
                                                  IIF([Bal2]=0,2,
                                                            IIF([Bal2]<0,1,
                                                                      IIF([Bal2]-[SumOfAmount]>0.01,6,
                                                                                IIF([Bal2]>0,3,0)))))))) AS BFNum
        , MemberAccountTotals.AsmtType
        ,
           IIF([asmttype] LIKE "*" & "roads" & "*",DATEDIFF("d",DATE(),[trandate]),"") AS RoadsAge
        , MemTotAsmts.BillingCycle
        , MemberAccountTotals.TranDate
        , MemberAccountTotals.DBAmount
        , MemberAccountTotals.CRAmount
        , Members.SPOUSE
        , Members.SPOUSELASTNAME
        , Members.LASTNAME
        , Members.FIRSTNAME
FROM
          ((MemberAccountTotals
          INNER JOIN
                    Members
          ON
                    MemberAccountTotals.MemberID_PK = Members.MemberID_PK)
          LEFT JOIN
                    PrimaryLots
          ON
                    Members.MemberID_PK = PrimaryLots.MemberID_FK)
          INNER JOIN
                    MemTotAsmts
          ON
                    Members.MemberID_PK = MemTotAsmts.MemberID_FK
;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

SkipVought · May 11, 2018

Thx Dwane. Have been formatting manually for decades!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

dhookom · May 11, 2018

There is another great tool that I use for creating VBA from an SQL statement.

A simple query like:

SQL:

SELECT FieldA, FieldB, FieldC
FROM tblA A JOIN tblB B on A.AID = B.BID

can be output like:

SQL:

SELECT fielda, 
       fieldb, 
       fieldc 
FROM   tbla A 
       JOIN tblb B 
         ON A.aid = B.bid

and

Code:

varname1 = ""
varname1 = varname1 & "SELECT FieldA, FieldB, FieldC " & vbCrLf
varname1 = varname1 & "FROM tblA A JOIN tblB B on A.AID = B.BID"

I use these regularly to cut down on coding time and make my efforts more organized.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

qqp · May 11, 2018

Nevermind guys, I figured it out. Thanks anyway! And thanks for the link to the cool tool Duane!

SkipVought · May 11, 2018

For the benefit of all other members who may browse your thread, please post a description of your solution.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

qqp · May 11, 2018

Using the cool new tool:

Code:

SELECT DISTINCT members.memberid_pk, 
                Iif([spouselastname] IS NULL 
                    AND NOT ( [spouse] IS NULL ), 
                [firstname] & " and " & [spouse] & "" & 
                [lastname], [firstname] & "" & [lastname]) AS 
                ComboName, 
                Iif([spouselastname] IS NULL, "", 
                [spouse] & "" & [spouselastname])          AS 
                ComboName2, 
                memberaccounttotals.lotnumber, 
                members.businessname, 
                members.address, 
                members.city, 
                members.state, 
                members.zip, 
                memtotasmts.bankrupt, 
                memtotasmts.uncollect, 
                memtotasmts.legal1, 
                memtotasmts.legal2, 
                [highlight #FCE94F][highlight english]memberaccounttotals.sumofdbamount 
                AS TDB, *****Previously these fields were "sum([dbamount]) when it should have been sumofdbamount
                memberaccounttotals.sumofcramount 
                AS TCR,[/highlight] *****Previously these fields were "sum([cramount]) when it should have been sumofcramount[/highlight]
                memtotasmts.sumofamount 
                AS TOTALASMTS, 
                [tdb] - [tcr] 
                AS Bal, 
                Iif([bal] BETWEEN -0.005 AND 0.005, 0, [bal]) 
                AS Bal2, 
                Iif([totalasmts] > 0, ( [tdb] - [tcr] ) / [totalasmts], 0) 
                AS BF, 
                Clng([bf] * 10000) / 10000 
                AS BF2, 
                Iif([bankrupt] = yes, 10, Iif([uncollect] = yes, 9, 
                                          Iif([legal2] = yes, 8, Iif( 
                                          [legal1] = yes, 7, Iif( 
                [bal2] 
                = 0, 2, 
                Iif([bal2] < 0, 1 
                , Iif([bf2] >= 5, 6, 
                Iif 
                ([bf2] >= 4, 5, Iif( 
                [bf2] 
                >= 3, 4, Iif( 
                [bf2] 
                > 0, 3, 0)))))))))) 
                AS BFNum2, 
                Iif([bal2] = 0, 2, Iif([bal2] < 0, 1, Iif([bf2] >= 5, 5, Iif( 
                                                      [bf2] >= 4, 4, 
                                                      Iif( 
                                                          [bf2] > 0, 3, 0))))) 
                AS 
                BFNum3, 
                Iif([bankrupt] = yes, 10, Iif([uncollect] = yes, 9, 
                                          Iif([legal2] = yes, 8, Iif( 
                                          [legal1] = yes, 7, Iif( 
                [bal2] 
                = 0, 2, 
                Iif([bal2] < 0, 1 
                , 
                Iif([bal2] - [sumofamount] 
                > 0.01, 6, Iif( 
                [bal2] 
                > 0, 3, 0)))))))) 
                AS BFNum, 
                memberaccounttotals.asmttype, 
                Iif([asmttype] LIKE "*" & "roads" & "*", 
                Datediff("d", Date(), [trandate]), "") 
                                                     AS RoadsAge, 
                memtotasmts.billingcycle, 
                memberaccounttotals.trandate, 
                memberaccounttotals.sumofdbamount, 
                memberaccounttotals.sumofcramount, 
                members.spouse, 
                members.spouselastname, 
                members.lastname, 
                members.firstname 
FROM   ((memberaccounttotals 
         INNER JOIN members 
                 ON memberaccounttotals.memberid_pk = members.memberid_pk) 
        LEFT JOIN primarylots 
               ON members.memberid_pk = primarylots.memberid_fk) 
       INNER JOIN memtotasmts 
               ON memberaccounttotals.memberid_pk = memtotasmts.memberid_fk;

dhookom · May 11, 2018

Good work qqp and thanks for reporting back!

FYI: when you TGML with the Code tag, the language typically refers to something like "VBA", "SQL", "PHP", or other programming language.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

qqp · May 11, 2018

LOL! I figured that after I entered it. [peace]

dhookom · May 11, 2018

You never know with people from MN. We might speak Norwegian or Canadian eh?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

JordanSanders · Jul 17, 2018

Here is one more useful tool for SQL code formatting, which helps me to make my code more readable and beautifies SQL statements instantly and convert code into well formatted script. This is SQL Formatter for SQL Server

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Aggregate function error in query 1

qqp

Programmer

SkipVought

Programmer

dhookom

Programmer

SkipVought

Programmer

dhookom

Programmer

qqp

Programmer

SkipVought

Programmer

qqp

Programmer

dhookom

Programmer

qqp

Programmer

dhookom

Programmer

JordanSanders

Programmer

Similar threads

Part and Inventory Search

Sponsor