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!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Aggregate function error in query 1

Status
Not open for further replies.

qqp

Programmer
Feb 23, 2016
34
US
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!


 
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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
 
Thx Dwane. Have been formatting manually for decades!

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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
 
Nevermind guys, I figured it out. Thanks anyway! And thanks for the link to the cool tool Duane!
 
For the benefit of all other members who may browse your thread, please post a description of your solution.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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;
 
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
 
LOL! I figured that after I entered it. [peace]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top