Sum of Field is not null

[shrikant]

Hi all,

i am trying to make a query which will give my the result as

if Sum of filed is not null....

Criteria 1) (Field Name)in Category "Asset ID Diff" And "Cusip Change" should select first then

2) Fund and Amt

if Fund Amt is not zero than data will comes in query


Category Fund Amt
Asset ID Diff ADF0 1000
Asset ID Diff ADF0 -1000
Cusip Change ADGH 2000
Cusip Change ADGH -2000
Asset ID Diff ADF0 5000

in above data you can see ADF0 funds total is 5000 which is not null or 0
so query will give me the result as


Asset ID Diff ADF0 5000

Plase help me on same thanks

 

[plog]

Here's a query that will get you very close to where you need to be:

SELECT YourTableNameHere.Category, YourTableNameHere.Fund, Abs([Amt]) AS AbsoluteAmt, Sum(IIf([amt]<0,1,0)) AS Negatives, Sum(IIf([amt]>0,1,0)) AS Positives, Sum(IIf([amt]>0,1,0))-Sum(IIf([amt]<0,1,0)) AS RecordCount
FROM YourTableNameHere
GROUP BY YourTableNameHere.Category, YourTableNameHere.Fund, Abs([Amt])
HAVING (((Sum(IIf([amt]>0,1,0))-Sum(IIf([amt]<0,1,0)))<>0));

What it does is finds unmatched absolute values of the data. It will work for the sample set you have provided. It will provide you the Category, Fund and the Absolute Amount of the Amt field, the total number of negative records, the total number of positive records and the total unmatched records for records that have a different number of negative and positive Amt fields.

 

[Brianwarnock]

I think thoughts of Null are a red herring, it won't happen it will be 0, therefore all you need is a simple Totals query Groupby on all fields except amount which is Sum , with a criteria of <>0

Brian

 

[shrikant]

HI thanks for your reply!

the below coding giving me error as syntax error query expression All.Category
All.fund

and in category i only need Asset ID Diff and Cusip Change only not whole category field thanks

 

[Brianwarnock]

Don't understand your comment regarding syntax error, perhaps it would help if you posted a copy of the SQL .

For your second problem you need to have criteria selecting the records you require.

Brian