Need help with another query syntax
In query designer... I wan to have a field that reads something like this but I can't get the syntax right.
TotHours: Nz([NumberOfHours],0) where PublicActivityCode = "KZU" +Nz([NumberOfHours2],0) where PublicActivityCode2 = "KZU" +Nz([NumberOfHours3],0) where PublicActivityCode3 = "KZU"
How do I write that line to make it work?
TotHours: Nz([NumberOfHours],0) where PublicActivityCode = "KZU" +Nz([NumberOfHours2],0) where PublicActivityCode2 = "KZU" +Nz([NumberOfHours3],0) where PublicActivityCode3 = "KZU"
How do I write that line to make it work?
Are these all fields in the same table? Or 3 different tables?
Do you want to sum them ONLY when PublicActivityCode, PublicActivityCode2 and PublicActivityCode3 are "KZU"? What about where one of those value is NULL, for example? Would you still want to sum all 3?
Personally, I'd do that in a separate query and join it back to your other query (and be sure to add in the ID value needed to join the two).
So in a separate query:
SELECT (Nz([NumberOfHours],0) +Nz([NumberOfHours2],0) +Nz([NumberOfHours3],0) AS TotalHours WHERE PublicActivityCode = "KZU" AND PublicActivityCode2 = "KZU" AND PublicActivityCode3 = "KZU"
If you need to sum them regardless of whether one or more is NULL:
SELECT YourIDField, (Nz([NumberOfHours],0) +Nz([NumberOfHours2],0) +Nz([NumberOfHours3],0) AS TotalHours WHERE PublicActivityCode = "KZU" OR PublicActivityCode2 = "KZU" OR PublicActivityCode3 = "KZU"
Save that as a stored query (perhaps named qryTotalHours), then include qryTotalHours in your main query. Join them on YourIDField, and then drag the TotalHours field into the query grid.
Do you want to sum them ONLY when PublicActivityCode, PublicActivityCode2 and PublicActivityCode3 are "KZU"? What about where one of those value is NULL, for example? Would you still want to sum all 3?
Personally, I'd do that in a separate query and join it back to your other query (and be sure to add in the ID value needed to join the two).
So in a separate query:
SELECT (Nz([NumberOfHours],0) +Nz([NumberOfHours2],0) +Nz([NumberOfHours3],0) AS TotalHours WHERE PublicActivityCode = "KZU" AND PublicActivityCode2 = "KZU" AND PublicActivityCode3 = "KZU"
If you need to sum them regardless of whether one or more is NULL:
SELECT YourIDField, (Nz([NumberOfHours],0) +Nz([NumberOfHours2],0) +Nz([NumberOfHours3],0) AS TotalHours WHERE PublicActivityCode = "KZU" OR PublicActivityCode2 = "KZU" OR PublicActivityCode3 = "KZU"
Save that as a stored query (perhaps named qryTotalHours), then include qryTotalHours in your main query. Join them on YourIDField, and then drag the TotalHours field into the query grid.
ASKER
Yes, I need to sum them regardless of whether one or more is NULL. But I'm getting a syntax error with:
SELECT YourIDField, (Nz([NumberOfHours],0) +Nz([NumberOfHours2],0) +Nz([NumberOfHours3],0) AS TotalHours WHERE PublicActivityCode = "KZU" OR PublicActivityCode2 = "KZU" OR PublicActivityCode3 = "KZU"
SELECT YourIDField, (Nz([NumberOfHours],0) +Nz([NumberOfHours2],0) +Nz([NumberOfHours3],0) AS TotalHours WHERE PublicActivityCode = "KZU" OR PublicActivityCode2 = "KZU" OR PublicActivityCode3 = "KZU"
SELECT YourIDField, Nz([NumberOfHours],0) +Nz([NumberOfHours2],0) +Nz([NumberOfHours3],0) AS TotalHours WHERE PublicActivityCode = "KZU" OR PublicActivityCode2 = "KZU" OR PublicActivityCode3 = "KZU"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHERE PublicActivityCode = "KZU" AND PublicActivityCode2 = "KZU" AND PublicActivityCode3 = "KZU"