Multiple criteria in a single query

D

Dino

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?
 
J

John Vinson

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?
Click to expand...

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]
 
D

Dino

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.)


John Vinson said:
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?
Click to expand...

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]
Click to expand...
 
J

John Vinson

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.)
Click to expand...

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]
 
Top