sql server new users: I have to following Access2003 Code that needs to run in SQL 2000

SELECT
extend,
Source,
Count(PersonalID) AS RecordCount,
Count(IIf([Literature1Quantity]>0,([Literature1Quantity]),Null)) AS
Item01,
Count(IIf([Literature2Quantity]>0,([Literature2Quantity]),Null)) AS
Item02,
Count(IIf([Literature3Quantity]>0,([Literature3Quantity]),Null)) AS
Item03,
Count(IIf([Literature4Quantity]>0,([Literature4Quantity]),Null)) AS
Item04
FROM dbo_Transaction_History
WHERE
(((dbo_Transaction_History.extend)=538) And
((dbo_Transaction_History.extend_TransType)="ib")
And ((nz([Literature1Quantity])+nz([Literature2Quantity])+nz
([Literature3Quantity])+nz([Literature4Quantity]))>0))
GROUP BY dbo_Transaction_History.extend, dbo_Transaction_History.Source
HAVING (((dbo_Transaction_History.Source) In ("tm","web","brc")));

This will Give me the required result of

extend Source RecordCount Item01 Item02 Item03 Item04
538 BRC 919 0 0 919 0
538 TM 1178 41 115 1081 53
538 Web 4 0 0 4 0

The ItemXX is simply a COUNT if not null ELSE NULL (0) based on the
grouping. Also I have no idea how NZ() is fitting into the WHERE clause.

Now here's where I'm at with the SQL Server code

SELECT
extend,
Source,
Count(PersonalID) AS 'RecordCount',
CASE
COUNT(ISNULL(Literature1Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature1Quantity)
END AS Item01,

CASE
COUNT(ISNULL(Literature2Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature2Quantity)
END AS Item02,

CASE
COUNT(ISNULL(Literature3Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature3Quantity)
END AS Item03,

CASE
COUNT(ISNULL(Literature4Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature4Quantity)
END AS Item04

FROM Transaction_History
WHERE
extend=538
AND extend_TransType='ib'
GROUP BY extend,Source
HAVING Source In ('tm','web','brc')

extend Source RecordCount Item01 Item02 Item03 Item04
538 BRC 1812 0 0 1812 0
538 TM 20103 41 115 1081 53
538 Web 39 39 34 9 39

As you can see these results are nothing like the required.
I'm kind of new and really lost, can someone help?

[quoted text, click to view]

COUNT(x) will count up all the non-null x. So instead of the COUNT(CASE
structure you have, it should just be COUNT(Literature1Quantity).

The NZ(x) function in VBA turns a NULL value into an empty value for the
required data type. So NZ(Literature1Quantity) would be zero when
Literature1Quantity is null. So the addition is just attempting to limit
the search to rows where at least one quantity is greater than zero.
(assuming no negatives...) The equivalent to NZ() in T-SQL is COALESCE:
COALESCE(foo,0) is zero if foo is null, and foo if not.

the HAVING clause is inefficient when all you're checking is the value of
Source; you might as well put that into the WHERE clause.

Also, GROUP BY no longer necessarily implies ORDER BY .. you may get
results out of order if you don't specify.

Also below see the alternate quotation syntax for AS-ing an expression,
somewhat more readable.

So try this:

SELECT
extend, Source,
COUNT(PersonalID) "RecordCount",
COUNT(Literature1Quantity) "Item01",
COUNT(Literature2Quantity) "Item02",
COUNT(Literature3Quantity) "Item03",
COUNT(Literature4Quantity) "Item04"
FROM Transaction_History
WHERE
extend=538 AND extend_TransType='ib'
AND Source In ('tm', 'web', 'brc')
AND (
COALESCE(Literature1Quantity,0) + COALESCE(Literature2Quantity, 0)
+ COALESCE(Literature3Quantity,0) + COALESCE(Literature4Quanitity, 0)
[quoted text, click to view]
GROUP BY extend, Source
ORDER BY extend, Source