Query with sub SELECT statement not allowing sum of a field

[Guest]

Hi

I am wanting to use the following SQL statement in a larger UNION query but
am struggling to get it to work. I need to do a nested SELECT so as to only
include the K5 records that match the query.

The database is in Access 2003 and I am using WinSQL with ODBC connection.

When I run this statement I get the results I want:

SELECT SUM(Base1Acc.Val)
FROM Base1Acc, Base1Key, ATT1
WHERE Base1Acc.BaseID = Base1Key.BaseID
AND Base1Key.K5 = Att1.MMITNO
AND Base1Acc.Year = 2006
AND Base1Acc.Period = 3
AND Base1Key.BudVsn = '01'
AND Base1Acc.Val <> NULL
AND Base1Key.K5 in ('251','258','259','267','268','269')


When I run this statement I get the results I want

SELECT Base1Acc.Val
FROM Base1Acc, Base1Key, ATT1
WHERE Base1Acc.BaseID = Base1Key.BaseID
AND Base1Key.K5 = Att1.MMITNO
AND Base1Acc.Year = 2006
AND Base1Acc.Period = 3
AND Base1Key.BudVsn = '01'
AND Base1Acc.Val <> NULL
AND Base1Key.K5 in (SELECT distinct Key3.K5
FROM Base1Acc Acc3, Base1Key Key3, Base1Calc
Calc3, ScenarioPart Scenario3
WHERE Scenario3.ScenarioPartID =
Calc3.ScenarioPartID
AND Calc3.BaseAccID = Acc3.BaseAccID
AND Acc3.BaseID = Key3.BaseID
AND Scenario3.StsCommit =1
AND Calc3.Val <> 0
AND Acc3.Val <> 0)



But when I run this statement:

SELECT sum(Base1Acc.Val)
FROM Base1Acc, Base1Key, ATT1
WHERE Base1Acc.BaseID = Base1Key.BaseID
AND Base1Key.K5 = Att1.MMITNO
AND Base1Acc.Year = 2006
AND Base1Acc.Period = 3
AND Base1Key.BudVsn = '01'
AND Base1Acc.Val <> NULL
AND Base1Key.K5 in (SELECT distinct Key3.K5
FROM Base1Acc Acc3, Base1Key Key3, Base1Calc
Calc3, ScenarioPart Scenario3
WHERE Scenario3.ScenarioPartID =
Calc3.ScenarioPartID
AND Calc3.BaseAccID = Acc3.BaseAccID
AND Acc3.BaseID = Key3.BaseID
AND Scenario3.StsCommit =1
AND Calc3.Val <> 0
AND Acc3.Val <> 0)

WinSQL hangs on me.

I suspect it could be a bug with Access.

Any help would be appreciated

Cheers

Pouty

 

[Van T. Dinh]

It looks to me that WinSQL syntax is different from JET syntax. In JET, we
use:

Is Not Null

since

<> Null

won't work correctly in JET.

My suggestion is to re-post your question in a newsgroup dedicated to WinSQL
rather than here.