[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

Query Total: Expression, Why??

I am working on a query (In the code segment) created by someone else.  The query pulls information from a table of driver miles driven joined with a table of state tax rates.  This query is used in a report that gives totals by state and a grand total.  The report accomplishes this by doing a 'group by' state.

Right now there is one line in the query for each line in tblDriveMiles.  There is no summarizing.

The user now wants the report to give totals by state for each driver, then a final total for all drivers.  I figured I would add the field 'DriverID' to the query, add a group level to the report and be done.  However, each time I add the driver ID to the query and then try to execute it I get an error message "You tried to execute a query that does not include the specified expression TollMiles as an aggregate function".  What the heck is going on?

I then noticed that in the design view of the query there is a third line (after Field: and Table:) called Total:.  For each field in the query the entry on the Total: line says 'Expression".  I also noticed when I add the DriverID the query automatically assigns a 'Group By' in the Total: Line under the new field.

I removed all of the 'Expression" entries for every field but they come back.  What is going on?

This seems like a simple 'Select' query with a join to another table to pickup some information.  I have created these hundreds of times and never seen the Total: row in the designer.  What am I missing?
SELECT tblDriverMiles.TollMiles, tblDriverMiles.NonTollMiles, tblStates_Linked.NewYorkMtyRate, tblDriverMiles.DateOfMiles, tblDriverMiles.LoadedOrEmptyID, tblStates_Linked.FuelTaxRate, tblStates_Linked.HighwayTaxRate, tblStates_Linked.StateName, IIf(LoadedOrEmptyID=1,TollMiles+NonTollMiles,0) AS LoadedMles, IIf(LoadedOrEmptyID=2,TollMiles+NonTollMiles,0) AS EmptyMles, tblDriverMiles.FuelGallons
FROM tblDriverMiles INNER JOIN tblStates_Linked ON tblDriverMiles.StateOfMiles = tblStates_Linked.ID;

Open in new window

0
mlcktmguy
Asked:
mlcktmguy
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what if the query goes like this:
SELECT tblDriverMiles.DriverID , SUM(tblDriverMiles.TollMiles), SUM(tblDriverMiles.NonTollMiles), MAX(tblStates_Linked.NewYorkMtyRate), tblDriverMiles.DateOfMiles, MAX(tblStates_Linked.FuelTaxRate), MAX(tblStates_Linked.HighwayTaxRate), tblStates_Linked.StateName, SUM(IIf(LoadedOrEmptyID=1,TollMiles+NonTollMiles,0)) AS LoadedMles, SUM(IIf(LoadedOrEmptyID=2,TollMiles+NonTollMiles,0)) AS EmptyMles, SUM(tblDriverMiles.FuelGallons)
FROM tblDriverMiles INNER JOIN tblStates_Linked ON tblDriverMiles.StateOfMiles = tblStates_Linked.ID
GROUP BY tblDriverMiles.DriverID,tblDriverMiles.DateOfMiles, tblStates_Linked.StateName;

Open in new window

0
 
TextReportCommented:
You need to turn off the Total's from your query design as you are letting the report do the summation. This can be done on the toolbar by selecting the sigma button (this is the funny looking E)

You may find that the report is more efficient though if you let the query do the summation for you.

Cheers, Andrew
0
 
mlcktmguyAuthor Commented:
Excellent
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now