Query Total: Expression, Why??

Posted on 2009-02-22
Last Modified: 2012-05-06
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

Question by:mlcktmguy
    LVL 142

    Expert Comment

    by: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

    LVL 28

    Accepted Solution

    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
    LVL 1

    Author Closing Comment


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now