Avg or Sum in Query

[AlCamp]

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by the
query.

The form is frmDialog, and the Option Frame on that form is [AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum = 1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this calculation) error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[PC Datasheet]

In your Totals query include the field, DefectCount, twice. Under the first
DefectCount, change GroupBy to Sum and under the second DefectCount, change
GroupBy to Avg. On your report use one textbox and label to show either Sum
or Avg. Put the following code in the Open event of your report:
If Forms!FrmDialog!AverageOrSum = 1 Then
Me!NameOfTextBox.ControlSource = "AvgOfDefectCount"
Me!NameOfTextBoxLabel.Caption = "Average"
Else
Me!NameOfTextBox.ControlSource = "SumOfDefectCount"
Me!NameOfTextBoxLabel.Caption = "Sum"
End If

 

[Dale Fye]

Al,

Since I don't know what the rest of your query looks like, I cannot help you
with that. However, I was able to compute the AverageOrSum of a numeric
column using the following:

AverageOrSum:
IIF([Forms]![yourForm]![og_averageORSum]=1,Avg([DefectCount]),Sum([DefectCou
nt]))
Total: Expression

HTH
Dale

 

[AlCamp]

Dale,
Thanks for responding. I'll try your solution, but... hmm... I'm pretty
sure that's what I tried right at the start.
I remember being surprised that I got an "aggregate" error, because the
results of my IIF were aggregates. Maybe I had a finger check...
Anyhow... thanks for the help. I'll give that another shot.

Al Camp

Al,

Since I don't know what the rest of your query looks like, I cannot help
you
with that. However, I was able to compute the AverageOrSum of a numeric
column using the following:

AverageOrSum:
IIF([Forms]![yourForm]![og_averageORSum]=1,Avg([DefectCount]),Sum([DefectCou
nt]))
Total: Expression

HTH
Dale

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by the
query.

The form is frmDialog, and the Option Frame on that form is
[AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum = 1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this calculation) error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[AlCamp]

That's a good work-around! I would think that will do the trick...

So... perhaps my attempt to select the aggregate via an IIF is unrealistic?

Thanks a lot for the help,
Al Camp

In your Totals query include the field, DefectCount, twice. Under the
first
DefectCount, change GroupBy to Sum and under the second DefectCount,
change
GroupBy to Avg. On your report use one textbox and label to show either
Sum
or Avg. Put the following code in the Open event of your report:
If Forms!FrmDialog!AverageOrSum = 1 Then
Me!NameOfTextBox.ControlSource = "AvgOfDefectCount"
Me!NameOfTextBoxLabel.Caption = "Average"
Else
Me!NameOfTextBox.ControlSource = "SumOfDefectCount"
Me!NameOfTextBoxLabel.Caption = "Sum"
End If

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by the
query.

The form is frmDialog, and the Option Frame on that form is
[AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum = 1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this calculation) error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[Dale Fye]

I only had this one field in my query, but it worked exactly as you
indicated you wanted it to. If this doesn't work, post the entire SQL
string and I will take a look at it.

Dale

Dale,
Thanks for responding. I'll try your solution, but... hmm... I'm pretty
sure that's what I tried right at the start.
I remember being surprised that I got an "aggregate" error, because the
results of my IIF were aggregates. Maybe I had a finger check...
Anyhow... thanks for the help. I'll give that another shot.

Al Camp

Al,

Since I don't know what the rest of your query looks like, I cannot help
you
with that. However, I was able to compute the AverageOrSum of a numeric
column using the following:

AverageOrSum:
IIF([Forms]![yourForm]![og_averageORSum]=1,Avg([DefectCount]),Sum([DefectCou
nt]))
Total: Expression

HTH
Dale

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by the
query.

The form is frmDialog, and the Option Frame on that form is
[AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum = 1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this calculation) error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[AlCamp]

Dale,
Here's the SQL statement... (Select query)

SELECT tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0) AS InRange,

*IIf([DefectID]=1 And
[Forms]![frmInspectionReportsDialog]![AverageOrCount1]=1,Avg([DefectCount]),Sum([DefectCount]))
AS 1*

FROM tblInspectionDefects LEFT JOIN tblFabricInventory ON
tblInspectionDefects.RollNo = tblFabricInventory.RollNo
GROUP BY tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0)
HAVING (((tblFabricInventory.StyleID) Like
[Forms]![frmInspectionReportsDialog]![cboStyleID1] & "*") AND
((IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0))=1))
ORDER BY tblFabricInventory.StyleID;

* Problem Code *
Error = You tried to execute a query that doesn't include the specified
expression <name> as part of an aggregate function. (Error 3122)
I used some "alias" control names in my first post for ease/clarity... this
SQL uses the real control names.

Any help would be appreciated... but, I may have to create multiple SQLs,
and apply then to the report according to the dialog form values.

Thanks,
Al Camp

I only had this one field in my query, but it worked exactly as you
indicated you wanted it to. If this doesn't work, post the entire SQL
string and I will take a look at it.

Dale

Dale,
Thanks for responding. I'll try your solution, but... hmm... I'm pretty
sure that's what I tried right at the start.
I remember being surprised that I got an "aggregate" error, because
the
results of my IIF were aggregates. Maybe I had a finger check...
Anyhow... thanks for the help. I'll give that another shot.

Al Camp

Al,

Since I don't know what the rest of your query looks like, I cannot
help
you
with that. However, I was able to compute the AverageOrSum of a numeric
column using the following:

AverageOrSum:
IIF([Forms]![yourForm]![og_averageORSum]=1,Avg([DefectCount]),Sum([DefectCou
nt]))
Total: Expression

HTH
Dale

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by
the
query.

The form is frmDialog, and the Option Frame on that form is
[AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this
as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum =
1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this calculation)
error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[Dale Fye]

Al,

Correct me if I am wrong, but what it appears you are trying to do is get a
cumulative count of the number of defects or the average of the number of
defects, by StyleID, where the fabric was received between two dates. If
this is the case, you don't need your second value in the SELECT portion of
the query, and I think it would be more appropriate to put the filtering
criteria a WHERE clause rather than in the HAVING clause I believe the
following should work.

I've parsed it to make it a little easier to read; just delete all the
carraige returns once you paste it into the SQL view.

SELECT tblFabricInventory.StyleID,IIf([DefectID]=1 And
[Forms]![frmInspectionReportsDialog]![AverageOrCount1]=1,Avg([DefectCount]),
Sum([DefectCount])) as AvgOrCount
FROM tblInspectionDefects
LEFT JOIN tblFabricInventory ON tblInspectionDefects.RollNo =
tblFabricInventory.RollNo
WHERE tblFabricInventory.StyleID Like
[Forms]![frmInspectionReportsDialog]![cboStyleID1] & "*")
AND [RcvdDate] Between [Forms]![frmInspectionReportsDialog]![BeginningDate]
And
[Forms]![frmInspectionReportsDialog]![EndingDate]
GROUP BY tblFabricInventory.StyleID
ORDER BY tblFabricInventory.StyleID;

HTH
Dale

Dale,
Here's the SQL statement... (Select query)

SELECT tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0) AS InRange,

*IIf([DefectID]=1 And
[Forms]![frmInspectionReportsDialog]![AverageOrCount1]=1,Avg([DefectCount]),
Sum([DefectCount]))
AS 1*

FROM tblInspectionDefects LEFT JOIN tblFabricInventory ON
tblInspectionDefects.RollNo = tblFabricInventory.RollNo
GROUP BY tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0)
HAVING (((tblFabricInventory.StyleID) Like
[Forms]![frmInspectionReportsDialog]![cboStyleID1] & "*") AND
((IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0))=1))
ORDER BY tblFabricInventory.StyleID;

* Problem Code *
Error = You tried to execute a query that doesn't include the specified
expression <name> as part of an aggregate function. (Error 3122)
I used some "alias" control names in my first post for ease/clarity... this
SQL uses the real control names.

Any help would be appreciated... but, I may have to create multiple SQLs,
and apply then to the report according to the dialog form values.

Thanks,
Al Camp

I only had this one field in my query, but it worked exactly as you
indicated you wanted it to. If this doesn't work, post the entire SQL
string and I will take a look at it.

Dale

Dale,
Thanks for responding. I'll try your solution, but... hmm... I'm pretty
sure that's what I tried right at the start.
I remember being surprised that I got an "aggregate" error, because
the
results of my IIF were aggregates. Maybe I had a finger check...
Anyhow... thanks for the help. I'll give that another shot.

Al Camp

Al,

Since I don't know what the rest of your query looks like, I cannot
help
you
with that. However, I was able to compute the AverageOrSum of a numeric
column using the following:

AverageOrSum:

IIF([Forms]![yourForm]![og_averageORSum]=1,Avg([DefectCount]),Sum([DefectCou

nt]))
Total: Expression

HTH
Dale

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by
the
query.

The form is frmDialog, and the Option Frame on that form is
[AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this
as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum =
1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this calculation)
error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[AlCamp]

Thanks for all your help Dale. I'll try that out ASAP, and try to get back
to you.
Al Camp

Al,

Correct me if I am wrong, but what it appears you are trying to do is get
a
cumulative count of the number of defects or the average of the number of
defects, by StyleID, where the fabric was received between two dates. If
this is the case, you don't need your second value in the SELECT portion
of
the query, and I think it would be more appropriate to put the filtering
criteria a WHERE clause rather than in the HAVING clause I believe the
following should work.

I've parsed it to make it a little easier to read; just delete all the
carraige returns once you paste it into the SQL view.

SELECT tblFabricInventory.StyleID,IIf([DefectID]=1 And
[Forms]![frmInspectionReportsDialog]![AverageOrCount1]=1,Avg([DefectCount]),
Sum([DefectCount])) as AvgOrCount
FROM tblInspectionDefects
LEFT JOIN tblFabricInventory ON tblInspectionDefects.RollNo =
tblFabricInventory.RollNo
WHERE tblFabricInventory.StyleID Like
[Forms]![frmInspectionReportsDialog]![cboStyleID1] & "*")
AND [RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate]
And
[Forms]![frmInspectionReportsDialog]![EndingDate]
GROUP BY tblFabricInventory.StyleID
ORDER BY tblFabricInventory.StyleID;

HTH
Dale

Dale,
Here's the SQL statement... (Select query)

SELECT tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0) AS InRange,

*IIf([DefectID]=1 And
[Forms]![frmInspectionReportsDialog]![AverageOrCount1]=1,Avg([DefectCount]),
Sum([DefectCount]))
AS 1*

FROM tblInspectionDefects LEFT JOIN tblFabricInventory ON
tblInspectionDefects.RollNo = tblFabricInventory.RollNo
GROUP BY tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0)
HAVING (((tblFabricInventory.StyleID) Like
[Forms]![frmInspectionReportsDialog]![cboStyleID1] & "*") AND
((IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0))=1))
ORDER BY tblFabricInventory.StyleID;

* Problem Code *
Error = You tried to execute a query that doesn't include the specified
expression <name> as part of an aggregate function. (Error 3122)
I used some "alias" control names in my first post for ease/clarity... this
SQL uses the real control names.

Any help would be appreciated... but, I may have to create multiple SQLs,
and apply then to the report according to the dialog form values.

Thanks,
Al Camp

I only had this one field in my query, but it worked exactly as you
indicated you wanted it to. If this doesn't work, post the entire SQL
string and I will take a look at it.

Dale

Dale,
Thanks for responding. I'll try your solution, but... hmm... I'm
pretty
sure that's what I tried right at the start.
I remember being surprised that I got an "aggregate" error, because
the
results of my IIF were aggregates. Maybe I had a finger check...
Anyhow... thanks for the help. I'll give that another shot.

Al Camp

Al,

Since I don't know what the rest of your query looks like, I cannot
help
you
with that. However, I was able to compute the AverageOrSum of a
numeric
column using the following:

AverageOrSum:

IIF([Forms]![yourForm]![og_averageORSum]=1,Avg([DefectCount]),Sum([DefectCou
nt]))
Total: Expression

HTH
Dale

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by
the
query.

The form is frmDialog, and the Option Frame on that form is
[AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with
this
as
a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum =
1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this
calculation)
error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[AlCamp]

Dale,
No luck with your Select statement. Created a new query and pasted your
SQL into my grid but still got the...
"You tried to execute a query that doesn't include the specified
expression <refers to AvgOrCount calculation> as part of an aggregate
function. (Error 3122)"
I can't understand why... if the results of an IIF statement yields an
AVG aggregate, or a SUM aggregate... how that can trigger the "no aggregate"
error? Also tried every combination of Unique Values and Unique Records
properties in desperation, with no luck.

I've decided to not "spit into the wind" any more.

I'll use the OnOpen event of my report to determine the status of my
AverageOrCount criteria on the frmDialog, and apply different RowSource SQL
statements accordingly.

Thanks for hanging in there, and all your help...
Al Camp

Al,

Correct me if I am wrong, but what it appears you are trying to do is get
a
cumulative count of the number of defects or the average of the number of
defects, by StyleID, where the fabric was received between two dates. If
this is the case, you don't need your second value in the SELECT portion
of
the query, and I think it would be more appropriate to put the filtering
criteria a WHERE clause rather than in the HAVING clause I believe the
following should work.

I've parsed it to make it a little easier to read; just delete all the
carraige returns once you paste it into the SQL view.

SELECT tblFabricInventory.StyleID,IIf([DefectID]=1 And
[Forms]![frmInspectionReportsDialog]![AverageOrCount1]=1,Avg([DefectCount]),
Sum([DefectCount])) as AvgOrCount
FROM tblInspectionDefects
LEFT JOIN tblFabricInventory ON tblInspectionDefects.RollNo =
tblFabricInventory.RollNo
WHERE tblFabricInventory.StyleID Like
[Forms]![frmInspectionReportsDialog]![cboStyleID1] & "*")
AND [RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate]
And
[Forms]![frmInspectionReportsDialog]![EndingDate]
GROUP BY tblFabricInventory.StyleID
ORDER BY tblFabricInventory.StyleID;

HTH
Dale

Dale,
Here's the SQL statement... (Select query)

SELECT tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0) AS InRange,

*IIf([DefectID]=1 And
[Forms]![frmInspectionReportsDialog]![AverageOrCount1]=1,Avg([DefectCount]),
Sum([DefectCount]))
AS 1*

FROM tblInspectionDefects LEFT JOIN tblFabricInventory ON
tblInspectionDefects.RollNo = tblFabricInventory.RollNo
GROUP BY tblFabricInventory.StyleID, IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0)
HAVING (((tblFabricInventory.StyleID) Like
[Forms]![frmInspectionReportsDialog]![cboStyleID1] & "*") AND
((IIf([RcvdDate] Between
[Forms]![frmInspectionReportsDialog]![BeginningDate] And
[Forms]![frmInspectionReportsDialog]![EndingDate],1,0))=1))
ORDER BY tblFabricInventory.StyleID;

* Problem Code *
Error = You tried to execute a query that doesn't include the specified
expression <name> as part of an aggregate function. (Error 3122)
I used some "alias" control names in my first post for ease/clarity... this
SQL uses the real control names.

Any help would be appreciated... but, I may have to create multiple SQLs,
and apply then to the report according to the dialog form values.

Thanks,
Al Camp

I only had this one field in my query, but it worked exactly as you
indicated you wanted it to. If this doesn't work, post the entire SQL
string and I will take a look at it.

Dale

Dale,
Thanks for responding. I'll try your solution, but... hmm... I'm
pretty
sure that's what I tried right at the start.
I remember being surprised that I got an "aggregate" error, because
the
results of my IIF were aggregates. Maybe I had a finger check...
Anyhow... thanks for the help. I'll give that another shot.

Al Camp

Al,

Since I don't know what the rest of your query looks like, I cannot
help
you
with that. However, I was able to compute the AverageOrSum of a
numeric
column using the following:

AverageOrSum:

IIF([Forms]![yourForm]![og_averageORSum]=1,Avg([DefectCount]),Sum([DefectCou
nt]))
Total: Expression

HTH
Dale

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by
the
query.

The form is frmDialog, and the Option Frame on that form is
[AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with
this
as
a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)

SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum =
1,[DefectCount],Avg([DefectCount])))

I get a "Can't use aggregate function" (the AVG in this
calculation)
error.

Any help will be appreciated.

As always... Thanks,
Al Camp