Criteria in Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My totals query includes a calculated field set up as an expression. So far
all works fine until I try to add a criteria to this calc field. The popup
menu points to a summed field (WeeklyMiles) that is referenced in my
calculation as SumOfWeeklyMiles. Any thoughts?

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year)) ON tblFeeder.FeederNumber =
tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
ORDER BY tblFeederMilesYrly.Vendor;
 
If you are trying to use SumOfWeeklyMiles in the having Statement of the
query, you will have to repeat the calculation, you can't reference its
alias.

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year))
ON tblFeeder.FeederNumber = tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
HAVING Sum(tblVMTW.WeeklyMiles) > 200
ORDER BY tblFeederMilesYrly.Vendor;

or if it is the field "Calc" that is causing the problem, you would need.

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year))
ON tblFeeder.FeederNumber = tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
HAVING Sum(tblVMTW.WeeklyMiles) - PlanMiles > 200
ORDER BY tblFeederMilesYrly.Vendor;
 
John,
I see where you included the field
"Sum([tblVMTW].[WeeklyMiles])-[PlanMiles]" in order to fix the summation of
WeeklyMiles. However, using Expression as my "Total:" choice is still giving
me problems as well as using Where. Perhaps I missed something here - any
thoughts would be appreciated.

John Spencer said:
If you are trying to use SumOfWeeklyMiles in the having Statement of the
query, you will have to repeat the calculation, you can't reference its
alias.

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year))
ON tblFeeder.FeederNumber = tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
HAVING Sum(tblVMTW.WeeklyMiles) > 200
ORDER BY tblFeederMilesYrly.Vendor;

or if it is the field "Calc" that is causing the problem, you would need.

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year))
ON tblFeeder.FeederNumber = tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
HAVING Sum(tblVMTW.WeeklyMiles) - PlanMiles > 200
ORDER BY tblFeederMilesYrly.Vendor;

briank said:
My totals query includes a calculated field set up as an expression. So
far
all works fine until I try to add a criteria to this calc field. The popup
menu points to a summed field (WeeklyMiles) that is referenced in my
calculation as SumOfWeeklyMiles. Any thoughts?

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year)) ON tblFeeder.FeederNumber =
tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
ORDER BY tblFeederMilesYrly.Vendor;
Click to expand...
Click to expand...
 
You can't use the aggregate functions in a WHERE clause. They have to be
used in a HAVING clause, since the value must be calculated for you to do a
compare against it.

If you are using the query grid, you cannot use WHERE under any of the
Aggregated data. You must use Expression or one of the aggregate functions
and then put your criteria in the criteria grid. If you don't want to see
the value that is being calculated, uncheck the visible box.

Beyond that I'm not sure what you may be doing.


briank said:
John,
I see where you included the field
"Sum([tblVMTW].[WeeklyMiles])-[PlanMiles]" in order to fix the summation
of
WeeklyMiles. However, using Expression as my "Total:" choice is still
giving
me problems as well as using Where. Perhaps I missed something here - any
thoughts would be appreciated.

John Spencer said:
If you are trying to use SumOfWeeklyMiles in the having Statement of the
query, you will have to repeat the calculation, you can't reference its
alias.

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS
PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year))
ON tblFeeder.FeederNumber = tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
HAVING Sum(tblVMTW.WeeklyMiles) > 200
ORDER BY tblFeederMilesYrly.Vendor;

or if it is the field "Calc" that is causing the problem, you would need.

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS
PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles, tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year))
ON tblFeeder.FeederNumber = tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
HAVING Sum(tblVMTW.WeeklyMiles) - PlanMiles > 200
ORDER BY tblFeederMilesYrly.Vendor;

briank said:
My totals query includes a calculated field set up as an expression.
So
far
all works fine until I try to add a criteria to this calc field. The
popup
menu points to a summed field (WeeklyMiles) that is referenced in my
calculation as SumOfWeeklyMiles. Any thoughts?

SELECT tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles AS
PlanMiles,
Sum(tblVMTW.WeeklyMiles) AS SumOfWeeklyMiles,
tblFeederMilesYrly.Vendor,
Round([SumOfWeeklyMiles]-[PlanMiles],2) AS Calc
FROM tblFeeder INNER JOIN (tblFeederMilesYrly LEFT JOIN tblVMTW ON
(tblFeederMilesYrly.Feeder = tblVMTW.FeederNumber) AND
(tblFeederMilesYrly.TrimYear = tblVMTW.Year)) ON tblFeeder.FeederNumber
=
tblFeederMilesYrly.Feeder
GROUP BY tblFeederMilesYrly.TrimYear, tblFeederMilesYrly.Miles,
tblFeederMilesYrly.Vendor
ORDER BY tblFeederMilesYrly.Vendor;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top