Multiple criteria in a single query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dino - 17 Feb 2006 18:18 GMT

I would like to have several different totals calculated from one table, each
based on different criteria in that table. Is it possible to do this in one
query? If so, how would I do that in the design view?

Reply to this Message

John Vinson - 17 Feb 2006 20:58 GMT

>I would like to have several different totals calculated from one table, each
>based on different criteria in that table. Is it possible to do this in one
>query? If so, how would I do that in the design view?

Possibly, depending on the structure of the table and what totals you
want to calculate. What you would need to do is put in some calculated
fields using IIF() to select the field if you want it totaled, and
select a 0 if you don't, and sum this calculated field.

If you'ld care to post some typical fieldnames and criteria, someone
should be able to help with the syntax, but (just as a blue-sky
example) suppose you have a field Size with values Small, Medium and
Large; you can total the Quantity field for these intependently with a
query like

SELECT Sum(IIF([Size] = "Small", [Quantity], 0)) AS SumOfSmall,
Sum(IIF([Size] = "Medium", [Quantity], 0) AS SumOfMedium,
<etc>

John W. Vinson[MVP]

Reply to this Message

Dino - 17 Feb 2006 22:27 GMT

Thanks, I'll try that. One issue I was having with the help files is that the
examples were calculating different fields from the same record to display a
calculated field. I'm interested in calculating the total of an entire field
(like a spreadsheet does at the bottom of a column.)

> >I would like to have several different totals calculated from one table, each
> >based on different criteria in that table. Is it possible to do this in one
[quoted text clipped - 16 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 17 Feb 2006 23:19 GMT

>Thanks, I'll try that. One issue I was having with the help files is that the
>examples were calculating different fields from the same record to display a
>calculated field. I'm interested in calculating the total of an entire field
>(like a spreadsheet does at the bottom of a column.)

If you want to display both the line data *and* the total, you cannot
(easily) do it in a Query.

Instead, just include the lineitems in your query. To get the Totals
base a Form (for onscreen use) or a Report (for printing) on the
query; put textboxes on the form/report Footer with a control source

=Sum([fieldname])

John W. Vinson[MVP]

Reply to this Message