Data Report Problem

[sumeetk]

I have a transaction table which contains 2 columns, Amount and Cat_id (category)

eg my table is like

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2


So now ive 2 queries
select amount from transaction where category = 1;

select amount from transaction where category = 2;

I want to display the result of these 2 queries in my report.

If i try to add these queries in two seperate commands in the data environment and then add it to the report then it shows an error.


So I created 2 views cat1 and cat 2 with those queries, created a new query which takes the value from those two views and placed it on the report.

But now wen i see the report i get this

Cat_1 | Cat_2
500 | 800
300 | 800
500 | 400
300 | 400


While i want my report like this.

Cat_1 | Cat_2
500 | 800
300 | 400

Please help me guys, have to build a small program.

[cmpgeek]

i am not sure i am reading this correctly, but i am going to take a stab at helping just in case...
have you tried grouping your results by category?

hope this helps...

[malleyo]

What about rewriting your query to:

select amount from transaction where category = 1 OR category = 2
Order By category

That will get your results in the form of:

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2

... unless category 1 and category 2 come from 2 different tables. If they do, you could use a JOIN.