Thanks for the info but I'm still having trouble getting past the missing
column problem... haven't found much info on using the IN operator you
mention... here is my query... perhaps you could show me how to modify it?
Note that I did use the NZ funtion to convert my nulls to zeros (not in this
query)... thanks... to be honest I had it there to start with but without
the ,0 at the end - didn't seem to work... don't use Access much...
Anyway here is a slightly modified version of my query:
TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE (((DateDiff("ww",[DateForwarded],Date())>0) AND
((Categories.Category='Category1') Or (Categories.Category='Category2') Or
(Categories.Category='Category3') Or (Categories.Category='Category4') Or
(Categories.Category='Category5') Or (Categories.Category='Category6'))))
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + ' : ' +
Cases.CaseTitle
PIVOT Categories.Category;
I need to be able to always return a column for each of the categories in
the WHERE clause... will the IN clause achieve this and if so, how?
Thanks again
Kevin
John Viescas said:
You can use an IN clause (Query Properties, Column Headings) to explicitly
declare all the Categories that the report expects. When an Office doesn't
have a particular category, the query will return a Null value. To
return
a
zero instead of a Null, use the NZ function. Change your Value column to
NZ([Value Column], 0).
You could also use an outer join with a list of all categories to make sure
you get them all, but that would involve perhaps significant changes to your
query. You didn't post your SQL or table structure, so I'm only guessing.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Kevin said:
My crosstab returns Categories as column headers, Office as row
headers,
and
a number (a sum) as the value. If a certain Category has no numeric
data
the
crosstab does not return that Category in the resultset. When the
query