Hi guys i have a problem I have a table that shows a participants activity for the state. The fields are both drop down boxes One is [state category] and [state services]. under [State Category] = Academic Support, Self Concept and Counselingand under [State Services] = "Computer Class Hours";"GED Class Hours";"College Class Hours";"Cultural Activites Hours";"Leadership Hours";"Individual - Personal Adjustment Hours";"Individual - Academic Progress Hours";"Individual - Vocational Planning Hours";"Group - Personal Adjustment Hours";"Group - Academic Progress Hours";"Group - Vocational Planning Hours"The thing is the users only uses the Self Concept [state Category] and Cultural Activities Hours [State Services]but for a particular report I need all of these categories to show, but in a particular order. Now I got help from all you great db gurus before on another report. I'm just not sure how to get all the Categories to show even thought there are no hours asociated with them. can someone help plse. Also does that make sense???SELECT AdultStateActivity_tbl.[Contact Date], AdultStateActivity_tbl.[Earned hours], Parent.[Parent First Name], Parent.[Parent Last Name], Parent.[Parent ID], AdultStateActivity_tbl.[State Category] AS Expr1, AdultStateActivity_tbl.[State Services] AS Expr2, COUNT(*) AS Expr3FROM AdultStateActivity_tbl INNER JOIN Parent ON AdultStateActivity_tbl.[Parent ID] = Parent.[Parent ID] INNER JOIN StateLookup_tbl ON Parent.[Parent ID] = StateLookup_tbl.[Parent ID]GROUP BY AdultStateActivity_tbl.[Contact Date], AdultStateActivity_tbl.[Earned hours], Parent.[Parent First Name], Parent.[Parent Last Name], Parent.[Parent ID], AdultStateActivity_tbl.[State Category], AdultStateActivity_tbl.[State Services]HAVING (StateLookup_tbl.[State Category] IN (N'Academic Support', N'Self Concept', N'Counseling')) AND (AdultStateActivity_tbl.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate)ORDER BY CASE WHEN AdultStateActivity_tbl.[State Category] = N'Academic Support' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Computer Class Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'GED Class Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'College Class Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Category] = N'Self Concept' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Cultural Activities Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Leadership Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Category] = N'Counseling' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Individual - Personal Adjustment Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Individual - Academic Progress Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Individual - Vocational Planning Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Group - Personal Adjustment Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Group - Academic Progress Hours' THEN 0 ELSE 1 END, CASE WHEN AdultStateActivity_tbl.[State Services] = N'Group - Vocational Planning Hours' THEN 0 ELSE 1 END
Showing all Categories in a Report
Posted - 2009-09-22 : 05:46:56
