Help with Expression in a query

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Can anyone help me with an expression in a query? I keep
getting that it's not a part of a aggriage function.

SELECT tblAttribute.AttributeID, Sum(tblPrevAudit.[#Ded])
AS [SumOf#Ded], tblAttribute.[#Checked],
tblAttribute.WeightPoints, IIF([tblPrevAudit].[#Ded] Is
Null,15,(([tblAttribute].[#Checked]-[tblPrevAudit].
[SumOf#Ded])/[tblAttribute].[#Checked]) * [tblAttribute].
[WeightPoints]) AS [Percent]
FROM tblPrevAudit RIGHT JOIN tblAttribute ON
tblPrevAudit.AttributeID = tblAttribute.AttributeID
GROUP BY tblAttribute.AttributeID, tblAttribute.
[#Checked], tblAttribute.WeightPoints;
 
Hi Eric,

The problem that you are encountering is that you are referring to some
field values that you are not grouping by, but you can't do this. Aside from
references to the fields that you are grouping by, your field references need
to be some sort of aggregate (sum, min, max, first, last, avg, etc).

For example, take the iif statement in your query:
IIF([tblPrevAudit].[#Ded] Is
Null,15,(([tblAttribute].[#Checked]-[tblPrevAudit].
[SumOf#Ded])/[tblAttribute].[#Checked]) * [tblAttribute].[WeightPoints]) AS
[Percent]

The iif is checking to see if tblPrevAudit.[#Ded] Is Null, but, since you
are not grouping on that field, some records may be null, some may have
values, so Access doesn't know what to do with the expression. You could use
an iif() function that checked the max() value of the field, the min, etc.
Or, you could add that field to the list of grouped fields, in which Access
could evaluate the condition.

Similarly, you would have to look at all field references in the sql to make
sure that they are either part of an aggregate function, or that they are
included in the group by clause.

HTH, Ted Allen
 
Thanks! I got it to work by using this:

SELECT tblAttribute.AttributeID, Sum(tblPrevAudit.[#Ded])
AS [SumOf#Ded], tblAttribute.[#Checked],
tblAttribute.WeightPoints, IIf([SumOf#Ded] Is Null,
[tblAttribute].[WeightPoints],(([tblAttribute].[#Checked]-
[SumOf#Ded])/[tblAttribute].[#Checked])*[tblAttribute].
[WeightPoints]) AS WeightedScore
FROM tblPrevAudit RIGHT JOIN tblAttribute ON
tblPrevAudit.AttributeID = tblAttribute.AttributeID
GROUP BY tblAttribute.AttributeID, tblAttribute.
[#Checked], tblAttribute.WeightPoints;
 
Back
Top