Returning zero in a query?

  • Thread starter Thread starter Flopbot
  • Start date Start date
F

Flopbot

I know I’m missing something simple, but I haven’t been able to track it down
through the other posts. I have a query that returns the number of hours
worked each month.


I have:

Admissions 400
Education 200

I want:

Admissions 400
Education 200
Office 0
Exhibits 0


How do I use the Nz() function to make the office and exhibits departments
show zero. The SQL is below. Thank you for any help!


SELECT DateSerial(Year([Date Worked]),Month([Date Worked]),1) AS [Month],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].Department
FROM [Volunteer Hours Tracking Table]
GROUP BY DateSerial(Year([Date Worked]),Month([Date Worked]),1), [Volunteer
Hours Tracking Table].Department
ORDER BY DateSerial(Year([Date Worked]),Month([Date Worked]),1) DESC;
 
I know I’m missing something simple, but I haven’t been able to trackit down
through the other posts.  I have a query that returns the number of hours
worked each month.

I have:

Admissions 400
Education   200

I want:

Admissions 400
Education   200
Office          0
Exhibits       0

How do I use the Nz() function to make the office and exhibits departments
show zero.  The SQL is below.  Thank you for any help!

SELECT DateSerial(Year([Date Worked]),Month([Date Worked]),1) AS [Month],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].Department
FROM [Volunteer Hours Tracking Table]
GROUP BY DateSerial(Year([Date Worked]),Month([Date Worked]),1), [Volunteer
Hours Tracking Table].Department
ORDER BY DateSerial(Year([Date Worked]),Month([Date Worked]),1) DESC;
Click to expand...

If you're getting this from a single table, then not possible... how
do you show records that don't exist anywhere? You can't. If you
have a table with Admissions, Education, Office, Exhibits etc in the
same column, then you can left join that table to the Volunteer Hours
Tracking table and then you'll get the zeroes you're looking for.
 
First you need a query to give you all Departments and month combinations ---
[Volunteer Hours Tracking Table_All_Days]
SELECT [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked]
FROM [Volunteer Hours Tracking Table], [Volunteer Hours Tracking Table] AS
[Volunteer Hours Tracking Table_1]
GROUP BY [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked];

SELECT DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1) AS
[Month], [Volunteer Hours Tracking Table_All_Days].Department,
IIf((Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) Is
Null,0,(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])))
AS Total
FROM [Volunteer Hours Tracking Table_All_Days] LEFT JOIN [Volunteer Hours
Tracking Table] ON ([Volunteer Hours Tracking Table_All_Days].Department =
[Volunteer Hours Tracking Table].Department) AND ([Volunteer Hours Tracking
Table_All_Days].[Date Worked] = [Volunteer Hours Tracking Table].[Date
Worked])
GROUP BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1),
[Volunteer Hours Tracking Table_All_Days].Department
ORDER BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1)
DESC;
 
On Wed, 10 Jun 2009 16:37:02 -0700, Flopbot

You seem to have a bad database design, because your table [Volunteer
Hours Tracking Table] has a field Department, whereas it should have
DepartmentID joined to a table listing departments (DepartmentID,
autonumber, PK; DepartmentName text 50, unique index)
Once you have that in place, you can outer-join with the Departments
table, and you will get:
Admissions 400
Education 200
Office null
Exhibits null
Then you can convert nulls to 0 using the Nz function.

-Tom.
Microsoft Access MVP
 
Back
Top