query about data dispay in dbgrid.

cbpanchal · July 5th, 2003, 01:52 AM

Dear All user,
I am writing application which tracks your expenses. In that application the data structure is as follows. I am using credit as positive values & credit as negative values.

Date | Particulars | Money
------------------------------
1-1-03 | Salary | 8000
2-3-03 | Petrol | -500
3-5-03 | shares money | 1000
4-5-03 | Medicines | -200

Now my question is I want to show the data to the user as follows :

Date | Particulars | In | Out
-----------------------------------
1-1-03 | Salary | 8000|
2-3-03 | Petrol | | 500
3-5-03 | shares money | 1000|
4-5-03 | Medicines | | 200

I hope you all will understand my problem.

Pl. help me.

Regards,
Chandrakant

psychadelic · July 8th, 2003, 04:46 AM

Hello

try these queries to get the result you want. They work with SQL Server and Ms Access though, so modify them is you are using another RDBM.

SQL Server:

Code:

SELECT     Date, Particulars,
    (CASE 
        WHEN Money < 0 THEN 0 
        ELSE Money 
    END) AS [In],  
    (CASE      
        WHEN Money >= 0 THEN 0 
        ELSE Money 
    END) AS [Out]
FROM YourTableName

!! IN is a reserve word in SQL Server and must be enclosed in braces.

Ms Access:

Code:

SELECT Date, Particulars, 
    IIf([Money]>=0,[Money],0) AS [In], 
    IIf([Money]<0,[Money],0) AS Out
FROM YourTableName;

cheers,

psychadelic