How to sum in an append query

B

blaw

How do I create a sum column to add all the months. Problem: whenever the
field is zero, the total excludes in the sum. Please see the SQL statement
below:
INSERT INTO [YTD PRODUCTIVITY REPORT] ( Type, [Atty #], [Atty Name], Hours,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec] )
SELECT [YTD - Step 3 Recorded Hours].Type, [YTD - Step 3 Recorded
Hours].[Work Atty], [YTD - Step 3 Recorded Hours].[Atty Name], [YTD - Step 3
Recorded Hours].Recorded, IIf([YTD - Step 3 Recorded Hours]![1] Is Not
Null,[YTD - Step 3 Recorded Hours]![1],0) AS Jan, IIf([YTD - Step 3 Recorded
Hours]![2] Is Not Null,[YTD - Step 3 Recorded Hours]![2],0) AS Feb, IIf([YTD
- Step 3 Recorded Hours]![3] Is Not Null,[YTD - Step 3 Recorded Hours]![3],0)
AS Mar, IIf([YTD - Step 3 Recorded Hours]![4] Is Not Null,[YTD - Step 3
Recorded Hours]![4],0) AS Apr, IIf([YTD - Step 3 Recorded Hours]![5] Is Not
Null,[YTD - Step 3 Recorded Hours]![5],0) AS May, IIf([YTD - Step 3 Recorded
Hours]![6] Is Not Null,[YTD - Step 3 Recorded Hours]![6],0) AS Jun, IIf([YTD
- Step 3 Recorded Hours]![7] Is Not Null,[YTD - Step 3 Recorded Hours]![7],0)
AS Jul, IIf([YTD - Step 3 Recorded Hours]![8] Is Not Null,[YTD - Step 3
Recorded Hours]![8],0) AS Aug, IIf([YTD - Step 3 Recorded Hours]![9] Is Not
Null,[YTD - Step 3 Recorded Hours]![9],0) AS Sep, IIf([YTD - Step 3 Recorded
Hours]![10] Is Not Null,[YTD - Step 3 Recorded Hours]![10],0) AS Oct,
IIf([YTD - Step 3 Recorded Hours]![11] Is Not Null,[YTD - Step 3 Recorded
Hours]![11],0) AS Nov, IIf([YTD - Step 3 Recorded Hours]![12] Is Not
Null,[YTD - Step 3 Recorded Hours]![12],0) AS [Dec]
FROM [Attorney No/Name] INNER JOIN [YTD - Step 3 Recorded Hours] ON
[Attorney No/Name].[Atty #] = [YTD - Step 3 Recorded Hours].[Work Atty];


Thank you
 
J

John W. Vinson

How do I create a sum column to add all the months. Problem: whenever the
field is zero, the total excludes in the sum. Please see the SQL statement
below:
INSERT INTO [YTD PRODUCTIVITY REPORT] ( Type, [Atty #], [Atty Name], Hours,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec] )

OW.

This table's structure IS SIMPLY WRONG. It *should not exist*.

You can use a Crosstab query (which I won't try to write off the top of my
head, use the wizard) to spread a normalized table with one record per month
(or even a record per step, if you group by the month) to get the same result.

That said... if you really, really want to do this sum in this horribly
denormalized structure, use the NZ() function instead of summing the values
themselves. Any math expression involving a NULL returns NULL; NZ([fieldname])
will return a numeric zero if the field [fieldname] is null.


John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top