If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple criteria in a single query
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? |
#2
|
|||
|
|||
Multiple criteria in a single query
On Fri, 17 Feb 2006 10:18:29 -0800, "Dino"
wrote: 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] |
#3
|
|||
|
|||
Multiple criteria in a single query
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" wrote: On Fri, 17 Feb 2006 10:18:29 -0800, "Dino" wrote: 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] |
#4
|
|||
|
|||
Multiple criteria in a single query
On Fri, 17 Feb 2006 14:27:06 -0800, "Dino"
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I query multiple rows into a single string in Access? | ComputerHope | Running & Setting Up Queries | 3 | July 1st, 2005 05:06 PM |
Update Query using same criteria for multiple Tables | Manuel Indacochea via AccessMonster.com | Running & Setting Up Queries | 2 | April 27th, 2005 02:42 PM |
How can multiple command buttons run a single query and pass a vec | IAN BURDON | Running & Setting Up Queries | 1 | April 14th, 2005 12:04 PM |
using option group to change multiple field query criteria | bicyclops | Running & Setting Up Queries | 1 | December 22nd, 2004 07:43 PM |
COUNTIF single criteria across multiple sheets?? | Earl Kiosterud | Worksheet Functions | 1 | October 29th, 2003 06:17 PM |