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!
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!