How to sum in an append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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] )
Click to expand...

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]
 
Back
Top