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.
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:
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:
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.
Code:
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:
Code:
[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: