One amount field in Table, multiple sum fields in Query? (1 Viewer)

[Bailey]

This seems simple, but I cannot get it done.

I have data in a table [tbl-Data] with one amount field (currency) and several other fields with various info (short text).

I can run a query to filter the amount field by criteria from another field, but what if I want more than one column in my query filtered by different criteria, but using the same summed amount field? Is this possible?

A simple example is one amount field [amount], and another field with the account number [acct_no] and a query that results in one column with the amount field summed by one range of account numbers and another column with the amount field summed by another range of account numbers.

Sample Table:
[Type]         [Acct_no]        [Amount]
PlanA              1000       $400
PlanB              1200       $300
PlanB              1000       $200
PlanA              1300       $500
PlanA              1000       $400
PlanB              1200       $300

What I want is a query with [Type] in the first column and then multiple columns with a sum of the amount by various [Acct_no] as criteria.

Sample end result of Query:

[Type]          [Amount]                                [Amount] 
                   where Acct_no = 1000           where Acct_no = 1200
Plan A            800                                                   0            
Plan B            200                                                   600

For the Query in design view, I'm putting in the [Acct_no] field, then unchecking the box so it doesn't show. Then I'm putting in a [Amount] field, putting "sum" in the total row and [tbl_Data]![Acct_No]="1000" in the criteria row. The query runs as expected. Then I do the same in another column, but change the criteria to [tbl_Data]![Acct_No]="1200", and when I run the query I get no data in either column for the amounts.


Looking around for solutions I see I can create a crosstab query, which I did and this converts all the data in the [Acct_no] field into fields, which is good, however I want to do more things with the data such as reverse the sign for some columns and combine other columns. I started working on that and find I cannot add more than two columns together for some reason. REgardless, I don't understand why the first method won't work, and if anyone has an easier way to achieve what I'm trying to do it will be appreciated.


This just seems like a simple thing, yet I can't figure it out.:banghead:

 

[June7]

No, cannot have different filter criteria as in your first attempt. How many account columns do you want? It is possible to emulate a CROSSTAB with calculated fields.

AcctGrp1: Sum(IIf(Acct_No Between 1000 And 1999, [Amount], Null))

Do these calcs in an aggregate (GROUP BY) query.

 

[plog]

Looking around for solutions I see I can create a crosstab query, which I did and this converts all the data in the [Acct_no] field into fields, which is good, however I want to do more things with the data such as reverse the sign for some columns and combine other columns

If a cross-tab isn't going to work, then the best bet is to move your data to Excel.

First though, give a cross-tab a shot. However, don't make that step 1. The first step is to build a subquery to do all those other calculations you talk about. Since you need 3 fields in your final query (Account, Amount, Type) this subquery will produce those 3 fields. Account and Type will come straight from the original table, but Amount will turn into a calculated field doing all those other things you said you need done. Once you have it like you need, save that subquery then cross-table it.