J
Jon
I'm trying to execute an aggregate function as part of a select
statement. It requires me to include all columns not in aggregate
function in the 'Group By' clause. That makes sense.
However, one of the columns may have null values because it's based on
a left join.
So I use the NZ() function to return 0 if that's the case. I then
return that value, whatever it is as [Exam Total]. If I don't I get
the 'Invalid use of null in query expression' error.
However, as it stands now, I'm getting an error like "You tried to
execute a query that does not include the specified expression <name>
as part of an aggregate function. (Error 3122)".
So if I correct one error, I get the other. Here's the query... any
ideas? - jon
SELECT [Level Ordered], [Customer to Level].[Customer #],
SUM([Customer to Level].[Quantity]) AS [Level Count],
NZ([Total], 0) as [Exam Total]
FROM [Customer to Level] LEFT JOIN
[Count Exams Per Customer]
ON ([Customer to Level].[Customer #]=[Count Exams Per Customer].
[Customer Number]) AND ([Customer to Level].[Level Ordered]=[Count
Exams Per Customer].[This Level])
WHERE [Customer to Level].[Order Date] < (SELECT [Actual Date] FROM
Dates WHERE [Date Name] = 'Billing Period Start' )
GROUP BY [Customer to Level].[Level Ordered], [Customer #], [Exam
Total];
statement. It requires me to include all columns not in aggregate
function in the 'Group By' clause. That makes sense.
However, one of the columns may have null values because it's based on
a left join.
So I use the NZ() function to return 0 if that's the case. I then
return that value, whatever it is as [Exam Total]. If I don't I get
the 'Invalid use of null in query expression' error.
However, as it stands now, I'm getting an error like "You tried to
execute a query that does not include the specified expression <name>
as part of an aggregate function. (Error 3122)".
So if I correct one error, I get the other. Here's the query... any
ideas? - jon
SELECT [Level Ordered], [Customer to Level].[Customer #],
SUM([Customer to Level].[Quantity]) AS [Level Count],
NZ([Total], 0) as [Exam Total]
FROM [Customer to Level] LEFT JOIN
[Count Exams Per Customer]
ON ([Customer to Level].[Customer #]=[Count Exams Per Customer].
[Customer Number]) AND ([Customer to Level].[Level Ordered]=[Count
Exams Per Customer].[This Level])
WHERE [Customer to Level].[Order Date] < (SELECT [Actual Date] FROM
Dates WHERE [Date Name] = 'Billing Period Start' )
GROUP BY [Customer to Level].[Level Ordered], [Customer #], [Exam
Total];