asked on

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

Guy Hengel [angelIII / a3]

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

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Excellent