Access 2007 NZ() is working but the group by is not grouping 1

[chromarog]

I'm having trouble with the NZ() functionality. It works the way it should, I'm converting a null result to the number 3, but then my group by's are still seperating the results.

SELECT T_VSMSMP2_BreakDown.WOTYPE, [red]nz([T_VSMSMP2_BreakDown.PRIORITY],3) as PRIORITY[/red], Sum(T_VSMSMP2_BreakDown.REGHRS) AS SumOfREGHRS, Sum(T_VSMSMP2_BreakDown.OTHRS) AS SumOfOTHRS, AcctCalendar.AccMonth
FROM AcctCalendar, T_VSMSMP2_BreakDown
WHERE (((T_VSMSMP2_BreakDown.COMPLETIONDATE) Between [AcctCalendar].[StartDay] And [AcctCalendar].[EndDay]))
GROUP BY T_VSMSMP2_BreakDown.WOTYPE, T_VSMSMP2_BreakDown.PRIORITY, AcctCalendar.AccMonth
ORDER BY AcctCalendar.AccMonth;

results before, without the NZ():

WOTYPE PRIORITY SumOfREGHRS SumOfOTHRS AccMonth
BR 7 January
BR 1 16 January
BR 2 280.05 5.5 January
BR 3 64.25 January
SU 13 January
SU 2 24.75 January
SU 3 15.25 January


results after with the NZ():

WOTYPE PRIORITY SumOfREGHRS SumOfOTHRS AccMonth
BR 3 7 January
BR 1 16 January
BR 2 280.05 5.5 January
BR 3 64.25 January
SU 3 13 January
SU 2 24.75 January
SU 3 15.25 January
(I can't get the results to post right but I hope you get the idea)

The proirity columns are not summing up as I had hoped they would. I've looked into forcing the column to a numeric value but I'm not finding that bit of code. I've went back to the tables and they are all set to number types and the original table it was pulled from on the sql server side was a float data type. If I use an IsNull I get a "wrong number of arguments used with function in query expression" error. What am I doing wrong here? Or at least point me in the right direction or resource. I've been searching for the solution for a couple of hours now.

 

[kjv1611]

Looks like you're having trouble in regards to SQL. Is this something you're running in an Access query (Jet SQL), or is it in SQL Server, or some other? Whatever it is, that's where you need to ask the question:

forum183

forum701

For example.

And also:
forum220

And there are a few others, Oracle for instance.

I'm assuming this is for an Access query, or a SQL string run from within Access VBA. In that case, go to the Access queries and Jet SQL forum.

 

[chromarog]

It's an access 2007 query. I'll repost there or will this be moved accordingly?

 

[chromarog]

Nevermind, I reposted.
Thanks,
Rog...

 

[SkipVought]

SELECT
  T_VSMSMP2_BreakDown.WOTYPE
, nz([T_VSMSMP2_BreakDown.PRIORITY],3) as PRIORITY
, Sum(T_VSMSMP2_BreakDown.REGHRS) AS SumOfREGHRS
, Sum(T_VSMSMP2_BreakDown.OTHRS) AS SumOfOTHRS
, AcctCalendar.AccMonth

FROM
  AcctCalendar
, T_VSMSMP2_BreakDown

WHERE (((T_VSMSMP2_BreakDown.COMPLETIONDATE) Between [AcctCalendar].[StartDay] And [AcctCalendar].[EndDay]))

GROUP BY
  T_VSMSMP2_BreakDown.WOTYPE[b]
, nz([T_VSMSMP2_BreakDown.PRIORITY],3)[/b]
, AcctCalendar.AccMonth

ORDER BY AcctCalendar.AccMonth;

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

New thread is here (for reference):

http://www.tek-tips.com/viewthread.cfm?qid=1598574&page=1

 

[chromarog]

Skip, again I owe you a tall cool frosty mug of a beverage of your choice. Seeing the logistics nightmare there, I'm afraid all I can do is click the Thanks link.

And thanks KJV for moving the thread for me.

 

[SkipVought]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]