Access SQL 1

[sangrg]

In MS Acess 2003, I want to calculate a percentage of certain field only for one specific item in the query.
For the rest of the item that doesn't meet the criteria,
do not perform percentage calculation.

eg.

The current Access query displays following records. 'Invest' is calculated field which is 50% of 'Summary_amt' (another summary field).
If I want to calculate percentage only for 'Cash' item.
What would be the SQL syntax?
Thankyou.

Currently 'Invest' column shows calculated percentage for all items:

year period Summary_amt account_description account_type Invest

2006 1 12000 Cash Assets 6000
2006 1 10000 Home Assets 50000
2006 1 0 PC Assets 0
2006 1 6000 Furniture Assets 3000

It should look like:

year period Summary_amt account_description account_type Invest

2006 1 12000 Cash Assets 6000
2006 1 10000 Home Assets
2006 1 0 PC Assets
2006 1 6000 Furniture Assets

MS Acess SQL:

SELECT balance.year, balance.period, Sum(balance.balance_amt) AS Summary_amt, balance_item.account_description, balance_item.account_type, 0.50*[Summary_amt] AS Invest
FROM (balance_item INNER JOIN balance_account ON balance_item.item_id = balance_account.item_id) INNER JOIN balance ON balance_account.account = balance.acct
GROUP BY balance.year, balance.period, balance_item.account_description, balance_item.account_type
HAVING (((balance.year)=2006) AND ((balance_item.account_type)="Assets"))
ORDER BY balance.year, balance.period

 

[SkipVought]

Hi,

I'd do a UNION.

First where account_description = 'Cash' with the percentage

Second where account_description <> 'Cash' with 0 for percentage


Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

Something like this ?
SELECT B.year, B.period, Sum(B.balance_amt) AS Summary_amt, I.account_description, I.account_type
, Sum(IIf(I.account_description='Cash',B.balance_amt/2,Null)) AS Invest
FROM (balance_item AS I
INNER JOIN balance_account AS A ON I.item_id = A.item_id)
INNER JOIN balance AS B ON A.account = B.acct
WHERE B.year=2006 AND I.account_type='Assets'
GROUP BY B.year, B.period, I.account_description, I.account_type
ORDER BY B.year, B.period

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sangrg]

Thanks, that worked great!!

Would you happen to know the SQL Syntax in SQL Server 2000
for the same query? I know SS2000 don't have IIF statements.

 

[PHV]

Have a look at CASE ... WHEN ... END

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886