Total Query Examples

Example 1: Perform Subtotals & Grand Totals With NO "Detail" Lines: My "Tooling Costs" Example

Also see: Total Queries: Showing Those With 0 Count Sales Percentage Example

I had a parent table with molds in it. I had a another table with tooling costs applicable to each mold. The purpose was to design a subform which would show total costs applicable to that mold, and break it down by each "cost code".

The data in the table looked like this:

There were such entries for every mold. What I wanted to do was perform a calculation of Quantity X Unit Price. But rather than show all 5 lines with the "extended price," I wanted it to grand-total all of them based on the Cost Code field.

The resulting data would look like this (for each mold):

In order to do this, the query would need to perform the Quantity X Unit Cost calculation and add the totals together to give a grand total; again, it would need to break it down based on the Cost Code field.

Narrowing it down by the mold number was not a difficulty; that was easily done by CRITERIA arguments in the query which plucked the mold number from the active form, since the active form would, in fact, be the MOLD form. The difficulty was in, as I mentioned before, performing the Unit * Cost calculation for each line item and then ALSO summing them up--again, based on the COST CODE field.

The query which performed this function looked like this:

The main important part here is the syntax of the 3rd column, which simultaneously calculates the (extended price) by multiplying the quantity times the unit price, AND adds each of these up for each COST code. The syntax:

The ext_price portion assigns a "field name" if you will to this column. The [unit_price]*[quantity] portion performs the 1st calculation--the EXTENDED PRICE calculation. Placing the Sum() argument around this, and then selecting Expression in the TOTAL attribute in the query, is what gave the desired result.

Running Totals: The "Checkbook Balance" Example

I wanted a query which would show a "running log" if you will of parts available. As new parts were added, the "remaining balance" should increase, or decrease as parts were deleted. I wanted to do this with a "calculated field" rather than calculating it and storing it in the table itself, which violates common database integrity rules.

The result was supposed to look something like this:

mold_#    qty    total
-------    ----    -----
2053        3        3
2053        4        7
2053        8        15
8015        1        1
8015        3        4
8015        3        7

Further, it was supposed to only show one mold at a time, because this was going to show up as a "subform" for each particular mold, so as a result it would actually look more like this:

mold_#    qty    total
-------    ----    -----
2053        3        3
2053        4        7
2053        8        15

Doing this was MUCH harder than I thought. The suggestion recommended to me was to:

Rename the "source" query in query#2 using "alias" properties

The initial "root" query looked like this:

A very straightforward query; the only unique thing about it is how it plucks the mold number from the "parent form" via the syntax in the CRITERIA row under the mold_# column. Nothing different there.

Next, a 2nd query based on the 1st query was created, and it looks like this:

Notice that the "source" query, which is actually qry_component_1, has now been renamed to "query1." Per my understanding, it is necessary to do this. You would do this by selecting the box, right-clicking, selecting PROPERTIES, and typing in your choice in the "alias" row.

The critical area which computes the running total is the 2nd column, with the following syntax:

runtot: (Select sum([qty]) from qry_component_1 where [ID]<=[query1].[ID])

Breakdown of each portion: the run_tot: portion merely gives this row a "field name" of sorts. The qry_component_1 portion identifes the "root" query as the ultimate datasource. The ending portion, [ID]<=[query1].[ID]), specifies to look in the "alias" query within this one, and as I understand it is what helps it identify the previous record.